Prev: VLOOKUP
Next: How do I set up a botton on Excel 2003 to go to the next sheet?
From: jatman on 5 May 2010 13:46
good day,

i have the following so far in my macro:

....
supcode = Range("B4").Value
....
If supcode = "CLI" Then
ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order
Generator.xlsm]CLI'!C2:C4,2,FALSE)"
....

right now i have this code repeated several times for each supcode that may
exist and i am usingn an if statement for each possible "supcode". i would
like to replace all the if statements, and change the "CLI" to use the
"supcode" value.

how do i get the "supcode" value into the vlookup code?

thank you,

jat
From: Luke M on 5 May 2010 13:48
'All on one line
ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order Generator.xlsm]" &
supcode & "'!C2:C4,2,FALSE)"

--
Best Regards,

Luke M
"jatman" <jatman(a)discussions.microsoft.com> wrote in message
news:4162A244-900C-40FB-9032-042A66872305(a)microsoft.com...
> good day,
>
> i have the following so far in my macro:
>
> ...
> supcode = Range("B4").Value
> ...
> If supcode = "CLI" Then
> ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order
> Generator.xlsm]CLI'!C2:C4,2,FALSE)"
> ...
>
> right now i have this code repeated several times for each supcode that
> may
> exist and i am usingn an if statement for each possible "supcode". i
> would
> like to replace all the if statements, and change the "CLI" to use the
> "supcode" value.
>
> how do i get the "supcode" value into the vlookup code?
>
> thank you,
>
> jat


 | 
Pages: 1
Prev: VLOOKUP
Next: How do I set up a botton on Excel 2003 to go to the next sheet?