CODEWITHSUNDEEP
vbaexcel
Micha Brugger
Micha Brugger

Reputation: 404

Insert " " into formula with variables VBA

I want to insert a vlookup into a range of cells that is defined by variables. My problem is that the search criteria (I gave the variable the name x) in the vlookup needs to be in " ", else the vlookup doesnt work.

But if I insert those " " into the formula in any way VBA thinks I'm trying to let it take x as a value.

Does anyone know how I can solve this problem?

If there is anything else wrong with the code, please tell me too, I'm new to this.

Sub FindExchange()

n = Worksheets.Count
For k = n To 6 Step -1

    Dim ws As Worksheet
    Set ws = Worksheets(k)

Dim lColumn As Long
    lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column


For i = lColumn To 1 Step -4

Dim lrow As Long
    lrow = ws.Cells(Rows.Count, i).End(xlUp).Row

x = Cells(1, i).Value

ws.Range(Cells(2, i + 2), Cells(lrow, i + 2)).FormulaLocal = "=vlookup(" & x & ";Sheet1!$B$2:$C$832;2;FALSE)"

Next i
Next k
End Sub

Upvotes: 1

Views: 421

Answers (3)

Sixthsense
Sixthsense

Reputation: 1975

"=vlookup(" & """" & x & """" & ";Sheet1!$B$2:$C$832;2;FALSE)"

Upvotes: 1

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

You can try this solution ,

"=vlookup(""" & x & """,Sheet1!$B$2:$C$832,2,FALSE)"

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

to get the double quotes " just add Chr(34).

change your FormulaLocal string to:

"=VLookup(" & chr(34) & x & chr(34) & ";Sheet1!$B$2:$C$832;2;FALSE)"

Upvotes: 0

Related Questions