Use declared variable in a VBA formula

Question

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?

Shai Rado · Accepted Answer · 2017-07-18 12:13:39Z

2

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

answered Jul 18, 2017 at 12:13

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

Use declared variable in a VBA formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related