Vlookup Macro

[bhieatza]

I have a Vlookup function that works great but I can not figure out how to get it working in a macro. The function is
=IF(ISNA(VLOOKUP(AU2,Sheet1!F:F,1,0)),"",VLOOKUP(AU2,Sheet1!F:G,2,FALSE))
an it is posted in column AP and copied all the way down. Any suggestions?

Thanks for your help.

 

[Oeldere]

Why VBA

why not making a table of your data.

if the data is a table and you add a formula it will automatic fill down to the last row of the list.

=> insert => table

 

[bhieatza]

I have 5 conditional drop downs. Based on the result of the 5th drop down(which is text) I need a number that corresponds to that string. I can not add any columns to the existing worksheet.

 

[bhieatza]

any ideas?

 

[bhieatza]

last bump

 

[bhieatza]

bump

 

[bhieatza]

bump

 

[L. Howard]

Try this, where you will adjust AP1 to the row you want the formulas to begin.

Howard

Option Explicit

Sub Formula_Down()

Dim lRow As Long

lRow = Cells(Rows.Count, "F").End(xlUp).Row
Range("[B]AP1[/B]:AP" & lRow).Formula = "=IF(ISNA(VLOOKUP(AU2,Sheet1!F:F,1,0)),"""",VLOOKUP(AU2,Sheet1!F:G,2,FALSE))"

End Sub

 

[bhieatza]

no dice. just blank cells :/

 

[L. Howard]

Try changing to the single cell that you have you original formula in that you say works great.

Range("AP1:AP" & lRow).

Like this, or whatever cell it is, and run the macro.

Range("AP1").Formula = "=IF(ISNA(VLOOKUP(AU2,Sheet1!F:F,1,0)),"""",VLOOKUP(AU2,Sheet1!F:G,2,FALSE))"

Does the formula work there?

Howard