vlookup range

  • Thread starter Thread starter Randy
  • Start date Start date
R

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))"
 
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
 
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))"
 
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))"
 
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.)
 
Back
Top