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

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

Very Good! Thank you.

Phil

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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