If statement or Lookup on a range

[RightCoastRipper]

Hi

I am trying to run an if statement on a range of cells to obtain a corresponding set of values in a separate range (i would be fine with overwrite the original range too)

For every cell1 in range(C2:J50)
If cell1 = 1 then cell2 = 50, If cell1 = 2 then cell2=20....If cell1 = 10 then cell2=1, Else 'do nothing, endif
Loop

cell2 is in range(N2:W50)

The if statement can also be phrased as a lookup in excel:

N2=VLOOKUP(C2, $AA$2:$AB$11, 2, 1)
For every cell in range(N2:W50)

Where AA2:AA11 is a column 1,2,..,10 and AB2:AB11 is a column of the corresponding values.

Can you please help me make this work in a macro???

 

[MrKowz]

Welcome to the forums!

Why do you need this to be in a macro? Are you needing the macro to create the VLOOKUP formula, or do you need it to create the value?

 

[RightCoastRipper]

I want to automate it so that other people can use my method

 

[RightCoastRipper]

Thanks for your help in advance!

 

[MrKowz]

This line of code should do it for you:

Range("N2:W50").Formula = "=VLOOKUP(C2,$AA$2:$AB$11,2,1)"

 

[RightCoastRipper]

So simple! Thanks.

I'm sure i'll be coming back for more help later in the development of the process