Update Vlookup lookup_value
 

This code is started in E3 and places the formula in the cells as needed. It
references B3 "RC[-3]" for the lookup_value. Is there a way to write this
formula so that it will always have the lookup_value point to the B column
even if the code were started in F3 "RC[-4]"or G3 "RC[-5]"...etc?

Sub Formulas()
For j = 1 To 5
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]="""","""",IF(ISERROR(VLOOKUP(RC[-3],WorkSheet!R1C2:R43C3,2,FALSE
)),0,VLOOKUP(RC[-2],WorkSheet!R1C2:R43C3,2,FALSE)))"
ActiveCell.Offset(1, 0).Select
Next j
End Sub

Thanks,
Phil

Update Vlookup lookup_value
 

RC2

would represent the same row, column 2 (B).



Phil Floyd wrote:

This code is started in E3 and places the formula in the cells as needed. It
references B3 "RC[-3]" for the lookup_value. Is there a way to write this
formula so that it will always have the lookup_value point to the B column
even if the code were started in F3 "RC[-4]"or G3 "RC[-5]"...etc?

Sub Formulas()
For j = 1 To 5
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]="""","""",IF(ISERROR(VLOOKUP(RC[-3],WorkSheet!R1C2:R43C3,2,FALSE
)),0,VLOOKUP(RC[-2],WorkSheet!R1C2:R43C3,2,FALSE)))"
ActiveCell.Offset(1, 0).Select
Next j
End Sub

Thanks,
Phil

--

Dave Peterson

Update Vlookup lookup_value
 

Very Good! Thank you.

Phil

"Dave Peterson" wrote in message
...
RC2

would represent the same row, column 2 (B).



Phil Floyd wrote:

This code is started in E3 and places the formula in the cells as
needed. It
references B3 "RC[-3]" for the lookup_value. Is there a way to write
this
formula so that it will always have the lookup_value point to the B
column
even if the code were started in F3 "RC[-4]"or G3 "RC[-5]"...etc?

Sub Formulas()
For j = 1 To 5
ActiveCell.FormulaR1C1 = _


"=IF(RC[-3]="""","""",IF(ISERROR(VLOOKUP(RC[-3],WorkSheet!R1C2:R43C3,2,FALSE
)),0,VLOOKUP(RC[-2],WorkSheet!R1C2:R43C3,2,FALSE)))"
ActiveCell.Offset(1, 0).Select
Next j
End Sub

Thanks,
Phil

--

Dave Peterson