Multiple Column Searches in Excel using VBA Macro

[stoliatic]

I have a sheet of data ("raw") containing multiple pairs of columns of dates and corresponding information under one overall heading, and another sheet ("Date Aligned") with the full list of dates in the first column and the headings from from the previous tab along the top of the sheet. I need to take all the "raw" data and align all the data so that the corresponding information is represented for each date, where a date is missing, I wish to use the previous date's entry.

I therefore wish to find a heading in the "Date Aligned" sheet and then find the corresponding heading and columns in the "Raw" sheet. Then I need to search the column for the appropriate date and record the corresponding piece of information. I am using the MATCH function to perform all my searches. The problem is that I need to move the array that I need to search every time I change heading, and don't think I am doing it correctly. Does anyone know why it is not working, or know of a better solution? I would be very grateful for any help.

Here is my code:


Sub MacroDateAlign()

Worksheets("Date Aligned").Activate
Worksheets("Raw").Activate

For ColNum = 2 To 19


DateRow = Application.WorksheetFunction.Match("Date", Worksheets("Date Aligned").Range("A1", "A7000"), False) ' Obtain row number of "date"
RowNum = DateRow + 1 ' First date row
Heading = Worksheets("Date Aligned").Cells(2, ColNum) ' Header to search for
HeadCol = Application.WorksheetFunction.Match(Heading, Worksheets("Raw").Range("A1", "BB1"), False) ' Header column
CharNum = 64 + HeadCol
ArrayChar = Chr(CharNum) ' Header Column Letter
ArrayStrt = ArrayChar & 1
ArrayEnd = ArrayChar & 7000
PriceCol = HeadCol + 1


Do While Not IsEmpty(Worksheets("Date Aligned").Cells(RowNum, 1))
Date = Worksheets("Date Aligned").Cells(RowNum, 1)
InfoRow = Application.WorksheetFunction.Match(Date, Worksheets("Raw").Range(ArrayStrt, ArrayEnd), True)
Worksheets("Date Aligned").Cells(RowNum, ColNum) = InfoRow
Info = Worksheets("Raw").Cells(InfoRow, HeadCol + 1)
Worksheets("Date Aligned").Cells(RowNum, ColNum) = Info
RowNum = RowNum + 1
Loop

Next ColNum

End Sub

It all works until the line: InfoRow = Application.WorksheetFunction.Match(Date, Worksheets("Raw").Range(ArrayStrt, ArrayEnd), True)

("Raw" is in a series of date-info-blank-date-info-blank-etc. columns with an overall heading above the date column.)

I am a new user of VBA, so I apologise for the quality of my code!!!

 

[SkipVought]

Hi,

Please post VBA questions in Forum707.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}