lookup value for a vlookup formula in VBA for an excel macro

[nskrbin]

I have a macro where at one point it populates a vlookup formula in a given column. The lookup value is in column AY. So I need the row to change to the active row and the column to stay AY. How do I go about that in the code? For example, lets say I am pasting the formula in column Z. I need the lookup value in Z1 to be AY1, Z2 to be AY2, Z3 to be AY3 etc. What do I enter in to VBA to get the row to change but the column to stay AY?

[AlphaFrog]

This will put a VLOOKUP formula in Z1:Z100. The lookup value in AY will automatically change for each row.

Range("Z1:Z100").Formula = "=VLOOKUP(AY1,AY:AZ,2,FALSE)"

[nskrbin]

Thank you AlphaFrog for your reply. I believe your suggestion would put AY1 as the vlookup value in Z1:Z100 though wouldn't it? What I need is for Z1 to have the lookup value AY1, Z2 should have AY2 as the lookup, all the way through Z100 in which the lookup value would be AY100. I know if I was manually entering in the formula into Excel I could use the $ to keep something constant and the other would change so $AY1 would change to $AY2 if I dragged the formula down, but the macro doesn't drag the formula down...it enters the formula in each cell. Just curious if there is a workaround to have something like =iferror(vlookup(AY...and then whatever language to reference the active row...then continue with the formula. any suggestions or am I not making any sense?

[AlphaFrog]

I believe your suggestion would put AY1 as the vlookup value in Z1:Z100 though wouldn't it?

No.
It would work the way you want it where Z1 will get AY1. Z2 will get AY2...just try it.

Alternatively, you could use the AutoFill method if you really want.

    Range("Z1").Formula = "=VLOOKUP(AY1,AY:AZ,2,0)"
    Range("Z1").AutoFill Destination:=Range("Z1:Z100")