Passing Sheet name as Variable in VBA VLOOKUP

Question

Hi Expert,

I have recorded MACRO, and now I want to change the Sheet Name in VLOOKUP formula to variable.

I have copied previous sheet name in variable "NAME"

now I want to pass the variable NAME in VLOOKUP

Formula as follows,

ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-4],'PREVDAY'!C[-4]:C[-3],2,0)"

PREDAY is sheet name, want to replace it with NAME variable.

How to achieve this

I tried following code.. but no luck. Still getting error,

"=+VLOOKUP(RC[-4], "'" & NAME "'" & !C[-4]:C[-3],2,0)"

"=+VLOOKUP(RC[-4],&" '"NAME"'"&!C[-4]:C[-3],2,0)"

"=+VLOOKUP(RC[-4]," '"&NAME&"'"!C[-4]:C[-3],2,0)"

"=+VLOOKUP(RC[-4], "'" NAME "'" & !C[-4]:C[-3],2,0)"

"=+VLOOKUP(RC[-4], & NAME & !C[-4]:C[-3],2,0)"

Answer 1

Hi Hiyaan

Try the following code

Sub vlookup()

Dim shName As String

With ActiveSheet

    shName = .Name

    Selection.FormulaR1C1 = "=+VLOOKUP(RC[-4],'" & shName & "'!C[-4]:C[-3],2,0)"

End With

End Sub

IMPORTANT NOTE

The above code will work as per your requirement

Nevertheless.

It is the VLOOKUP formula itself that worries me

As shown in the picture below

1- If the active cell is in any column < 5 i.e. within columns A:D

The lookup resulting formula will be set at the end of the worksheet ranges columns as indicated with bold RED color in the picture.

So be aware of it, and set in the code, a message, or a command to avoid that.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

Hi Hiyaan,

May I know if you still need help? If so, you can post back and we will try our best to help you.

Best Regards,

Sukie