Vary Vlookup column ref in vba
 

Hi everyone. I'm automating a report, and am writing a vlookup formula.
But based on the month on the report, I need the column ref # in the lookup
formula to vary. The report is setup in a trend fashion, with Dec 04 in
column E going all the way out to Dec 06 in column AC. So, vba looks in
cell C2 to determine how many columns to the right to begin the formula,
then it resizes 13 columns for 13 months to be formulated. In row 3 of each
month, I have the column ref # that I want in the vlookup. How can I vary
the formula code in such a way that vba knows what cell ref to put in the
column ref # field? in other words, in the formula below, if I the report
is for October 06, I need O$4. But if I choose Nov 06, then my offset in
cell C2 is 1 larger, which begins the formula in column P. Its like I need
an "activecell.column, row 3" syntax, except I'm not actually selecting the
cells. Thanks for any help!


os = Worksheets("Book I").Range("C2").Value 'the offset value to begin
the formula
For Each cl In Range("D1:D19") 'flag to tell vba whether or not to
formulate that row
If Not IsEmpty(cl) Then
cl.Offset(0, os).Resize(1, 13).Formula = _
"=VLOOKUP($A" & cl.Row & ",Data!$A$7:$P$750,O$4,0)" 'O needs to vary
based on offset
End If
Next cl