CODEWITHSUNDEEP
excelvariablesformulavba
BHeerschop
BHeerschop

Reputation: 9

Use declared variable in a VBA formula

Dim LastRowC As Long
Dim LastRowE As Long
Dim Deelnemernr As Long
    LastRowC = Range("C1048576").End(xlUp).Row + 1
    Deelnemernr = Cells(LastRowC, 3).Offset(0, 4).Select

I can manage to select the variable cell which I want to use in my formula below. So the code above works.

LastRowE = Range("E1048576").End(xlUp).Row + 1
Cells(LastRowE, 5).Formula = "=Index(E:E,Match(Deelnemernr,G:G,0))"

What I cannot seem to do is use the variable declared above as Deelnemernr in my Match formula. When I use a fixed cell (for example G12) in stead of Deelnemernr the formula does work. Can anyone tell my how the refer to a declared variable in a formula?

Upvotes: 1

Views: 276

Answers (1)

Shai Rado
Shai Rado

Reputation: 33662

You need to get the Deelnemernr variable outside the double quotes ".

Also, there's no need to use Select here :

Deelnemernr = Cells(LastRowC, 3).Offset(0, 4).Select

just get the value of that cell by:

Deelnemernr = Cells(LastRowC, 3).Offset(0, 4).Value

Try the code below:

LastRowC = Range("C1048576").End(xlUp).Row + 1
Deelnemernr = Cells(LastRowC, 3).Offset(0, 4).Value

LastRowE = Range("E1048576").End(xlUp).Row + 1
Cells(LastRowE, 5).Formula = "=Index(E:E,Match(" & Deelnemernr & ",G:G,0))"

Upvotes: 2

Related Questions