vlookup range

[Randy]

vlookup table array - QOH!R1C1:R844C5, is there a way to specify range as
QOH! xlright,xldown as the number of columns and rows can vary?


ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(C2,QOH!R1C1:R844C5,5,FALSE)),""0"",VLOOKUP(C2,QOH!R1C1:R844C5,5,FALSE))"

 

[Per Jessen]

Hi Randy,

Dim ws As Worksheet
Set ws = Worksheets("QOH")

LastRow = ws.Range("A1").End(xlDown).Row
LastCol = ws.Range("A1").End(xlToRight).Column

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(C2,QOH!R1C1:R" & LastRow & "C" & LastCol & _
",5,FALSE)),""0"",VLOOKUP(C2,QOH!R1C1:R" & LastRow & "C" _
& LastCol & ",5,FALSE))"

Regards,
Per

 

[Jim Thomlinson]

Generally speaking you are better off to come up from the bottom just in case
there is a blank cell in the data range of column A. Since your Vlookup is
hard coded at 5 there is no reason to vary the number of columns. If you
wnated to however you could apply the same logic as here...


dim lngRows as long

lngRows = sheets("QOH").cells(rows.count, "A").end(xlup).row

ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(C2,QOH!R1C1:R" & lngRows &
"C5,5,FALSE)),""0"",VLOOKUP(C2,QOH!R1C1:R844C5,5,FALSE))"

 

[Jim Thomlinson]

ooops missed something...

dim lngRows as long

lngRows = sheets("QOH").cells(rows.count, "A").end(xlup).row

ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(C2,QOH!R1C1:R" & lngRows &
"C5,5,FALSE)),""0"",VLOOKUP(C2,QOH!R1C1:R" & lngRows &
"C5,5,FALSE))"

 

[Dave Peterson]

I'd just cheat and use the entire column.

activecell.formula _
= "=if(isna(vlookup(c2,qoh!a:e,5,false)),0,vlookup(c2,qoh!a:e,5,false))

I also changed your text 0 ("0") to a number 0. (And I used .formula, too.)