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))"
--
Randy

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

"Randy" skrev i meddelelsen
...
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))"
--
Randy

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,F ALSE))"

--
HTH...

Jim Thomlinson


"Randy" wrote:

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))"
--
Randy

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))"


--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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,F ALSE))"

--
HTH...

Jim Thomlinson


"Randy" wrote:

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))"
--
Randy

I'd just cheat and use the entire column.

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

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


On 05/14/2010 08:33, Randy wrote:
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))"