Macro to write Vlookup formula with variable column index

[Ashali]

Hi Guys,

I have defined the following dependent on a dynamic range:

a = ActiveCell.Column
I need the column index in the below vlookup formula to refer to the value of a, but it literally enters

=+IFERROR(VLOOKUP(D13,'[IBC Daily Reported Date 08112012.xlsx]IBC'!$A:$G,a,0),"") as the formula in the cell. How do I need to change this reference???

Please Login or Register  to view this content.

[p24leclerc]

Try the following:

Please Login or Register  to view this content.

In your code, you placed the "a" with the double quotes of the formula so this is what you get. Butyou really want to put the value of your variable "a" into the formula. That is what the concatenation will do.