I'm trying to write a macro that on the surface seems pretty straight forward but the solution eludes me. I'm trying to turn a report into a table. The sheet will contain blocks of data with some information in the header which I'm trying to add to the individual rows so we can work with the data as a table.
The report would look something like this (but messier, with heaps of blank rows in between, and repeated blocks of this data):
Cost Centre: 77777
Product Number - Blank column - Description
123456 - Blank column - 123455 something
0987766 - blank column - 0987766 another thing
What I'm trying is do is to place the office and cost centre information in between Product Number and Description. I'm utilising the Blank column and adding another one. (that bit works ...). I also have variables for the office and cost centre but I can't manage to write the code that finds the rows where to insert the data.
Ultimately, I need two loops because it's blocks of data but before I run, I just want to be able to walk to get the code to manage the first block of data. This means I need a loop that inserts Office and Cost Centre in columns B and C in each row that starts with a cell that contains a number only. I'm trying to use ISNUMERIC but nothing I've tried to for has inserted any data anywhere.
- I need the code to find the point where to start, i.e. the first row with only numbers in column A.
- I also need the code to stop whent the numbers end. The following row would either be a blank row or start the next block.
This is the code so far:
Public Sub AgedStock() 'Insert Column Range("B1").EntireColumn.Insert Range("A1").Select 'Add Headers for Plant, Profit Centre and Storage Location 'For Each 'Find 423S Dim rngBlock As Range ActiveSheet.Range("A:A").Find("423S", LookIn:=xlValues).Select Set rngBlock = ActiveCell 'Memorise Plant, Profit Centre and Storage Location Dim strPlant As String Dim strPC As String Dim strSL As String strPlant = ActiveCell.Offset(4, 5).Value strPC = ActiveCell.Offset(5, 5).Value strSL = ActiveCell.Offset(6, 5).Value 'Find 1st row with Article Number Dim LastRow As Integer LastRow = rngBlock.Offset(13, 0).End(xlDown).Count For Row = rngBlock.Offset(13, 0) To LastRow If IsNumeric(Cells(Row, 1)) Then Row = Row + 1 Rows(Row).Select ActiveCell.Offset(0, 1).Value = strPlant 'Column B - Enter Plant ActiveCell.Offset(0, 2).Value = strPC 'Column C - Enter Profit Centre ActiveCell.Offset(0, 3).Value = strSL 'Column D - Enter Storage Location rngBlock.Select End If Next Row End Sub
I haven't found a lot of information on ISNUMERIC and at this point, it's likely that both the code for the loop and the IF function has issues.
Any help will be much appreciated.