Multiple Column Searches in Excel 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,

Hit the Debug button and examine the values of ArrayStrt, ArrayEnd using View > Watch Window and post back with your findings.

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

 

[stoliatic]

These are the watch results:

Watch : : ArrayEnd : "D7000" : Variant/String : Module2.MacroDateAlign
Watch : : ArrayStrt : "D1" : Variant/String : Module2.MacroDateAlign

D1 and D7000 are the correct values.

Any ideas?

Thanks for the help.

 

[SkipVought]

BTW, Date is a reserve word, a function that returns the current date.

Please post some representative values from column D (copy 'n' paste, please)


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

 

[stoliatic]

I have changed "Date" to "InfoDate", and here is an example of the "Raw" data, though the blank heading does not exist, it is just to illustrate the blank column as it is not very clear otherwise. This is 5 columns worth.

Observation D Observation A
Date Size Blank Date Size
03/01/1989 133.46 03/01/1989 921.22
04/01/1989 134.4 04/01/1989 926.49
05/01/1989 135.22 05/01/1989 929.97
06/01/1989 135.88 06/01/1989 935.77
09/01/1989 136.59 09/01/1989 945.52
10/01/1989 137.4 10/01/1989 947.66
11/01/1989 138.05 11/01/1989 947.38
12/01/1989 139.54 12/01/1989 955.57
13/01/1989 138.79 13/01/1989 961.8
16/01/1989 139.12 16/01/1989 966.78
17/01/1989 139.45 17/01/1989 965.01
18/01/1989 132.21 18/01/1989 976.01
19/01/1989 131 19/01/1989 985.45
20/01/1989 132.09 20/01/1989 988.99
23/01/1989 132.2 23/01/1989 993.14
24/01/1989 131.47 24/01/1989 1001.08
25/01/1989 131.01 25/01/1989 1000.63
26/01/1989 131.38 26/01/1989 1010.7
27/01/1989 132.22 27/01/1989 1034.11
30/01/1989 132.29 30/01/1989 1051.1
31/01/1989 132.66 31/01/1989 1054.97
01/02/1989 133.58 01/02/1989 1050.2
02/02/1989 135.05 02/02/1989 1053.04
03/02/1989 137.13 03/02/1989 1067.88
06/02/1989 137.04 06/02/1989 1056.93
07/02/1989 136.91 07/02/1989 1070.79
08/02/1989 137.03 08/02/1989 1082.84
09/02/1989 137.09 09/02/1989 1075.88
10/02/1989 138.3 10/02/1989 1065.13
13/02/1989 137.21 13/02/1989 1053.37
14/02/1989 137.15 14/02/1989 1060.89
15/02/1989 138.24 15/02/1989 1060.25
16/02/1989 138.76 16/02/1989 1055.2
17/02/1989 139.23 17/02/1989 1058.89
20/02/1989 139.3 20/02/1989 1069.39
21/02/1989 140.53 21/02/1989 1068.07
22/02/1989 142.16 22/02/1989 1055.94
23/02/1989 142.45 23/02/1989 1048.43
24/02/1989 143.3 24/02/1989 1050.47
27/02/1989 142.92 27/02/1989 1039.4
28/02/1989 142.52 28/02/1989 1042.6
01/03/1989 142.08 01/03/1989 1051.42
02/03/1989 142.87 02/03/1989 1059.75
03/03/1989 145.32 03/03/1989 1069.25
06/03/1989 144.91 06/03/1989 1075.47
07/03/1989 145.12 07/03/1989 1080.89
08/03/1989 144.46 08/03/1989 1082.19
09/03/1989 146.16 09/03/1989 1078.48

Thank you very much for the help!

 

[SkipVought]

I'd recommend using Option Explicit in your module, and declaring ALL your variables.

Declare InfoDate as Long.



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

 

[stoliatic]

Thanks for the help!!! I think it all works now. I just run out of column identifiers once it gets past Col Z so going to fix that. It does all the columns up to that though!!!