VBA Vlookup Help

[sararose27]

I could use some help setting up a vlookup that will be used in multiple cells in the same worksheet. The data is in a different tab in same worksheet.
The lookup value will be cell B5 in the sheet with the vlookup.
Table Array: AE 2019 eCAT Test Ppts ***do I need to remove spaces from tab name?***
Column Index: 13 ***this will change based on the data I need to pull***
Range Lookup: False.

I am hoping to come up with one row of code that I can copy/paste and just change the Column Index.

I tried
Dim EEID As String EEID = Range("B5")
ActiveCell.FormulaR1C1 = _
"=VLOOKUP("EID"'AE 2019 eCAT Test Ppts'!C[5]:C[52],13,0)"

It isn't working.
TIA

 

[James006]

Hello,

Aren't you just missing a comma ... after your value "EID" ... ?

HTH

 

[Gregm66]

Hi,

Are you trying to use VBA to do this or
Just using "VLOOKUP" in a range of cells on a worksheet?

If its on a worksheet then your current "VLOOKUP" formula does not like right at all.

 

[sararose27]

Added the comma. Still not working. Darn, I was really hoping it was that easy.

Hello,

Aren't you just missing a comma ... after your value "EID" ... ?

HTH

 

[sararose27]

Trying to use VBA. I have about 100 rows of data that I need to turn into separate tabs with formatting.

Hi,

Are you trying to use VBA to do this or
Just using "VLOOKUP" in a range of cells on a worksheet?

If its on a worksheet then your current "VLOOKUP" formula does not like right at all.

 

[James006]

Re,

If you were to type in the formula directly in cell B5 ....in order to check it is working properly ...

Would you mind posting your formula afterwards ...

By the way ... have you tested :

[COLOR=#333333][FONT=Verdana]ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]"=VLOOKUP(B5,'AE 2019 eCAT Test Ppts'!C[5]:C[52],13,0)"[/FONT][/COLOR]

 

[BarryL]

Just to also note that "EID" will require double quotes in VBA so it should be ""EID"'".

 

[sararose27]

I ended up with this and it seems to do the job
ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C2,'AE 2019 eCAT Test Ppts'!C7:C54,29,0)"

 

[James006]

I ended up with this and it seems to do the job
ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C2,'AE 2019 eCAT Test Ppts'!C7:C54,29,0)"

Glad you could solve your problem ...:wink: