Insert " " into formula with variables VBA

Question

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

Gowtham Shiva · Accepted Answer · 2017-08-19 06:31:55Z

1

You can try this solution ,

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

answered Aug 19, 2017 at 6:31

Gowtham Shiva

3,8852 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sixthsense · Accepted Answer · 2017-08-19 06:32:20Z

1

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

answered Aug 19, 2017 at 6:32

Sixthsense

1,9612 gold badges22 silver badges40 bronze badges

Comments

Shai Rado · Accepted Answer · 2017-08-19 06:27:04Z

0

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)"

answered Aug 19, 2017 at 6:27

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

Insert " " into formula with variables VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related