CODEWITHSUNDEEP
excelformulavba
philipph
philipph

Reputation: 21

Add formula with variable in specific cell with VBA

I'm trying to add a formula to a specific cell using VBA. The formula needs to contain a variable which I calculated before within the VBA script.

The formula is looking like this: 

ActiveCell.FormulaR1C1 =
"=IFERROR(VLOOKUP(RC[-4],tbl_LTCSREP,rngCell_NoUsed.Column,FALSE),""NO LTCS 
VALUE AVAILABLE"")"

The rngCell.Column variable is the one I calculated prior to that. When I do it this way, VBA just pastes the plain text in the cell and Excel itself is not able to use the value behind the variable and the VLOOKUP gives back an error.

Thank you in advance for your support.

Upvotes: 1

Views: 103

Answers (2)

philipph
philipph

Reputation: 21

That works perfectly fine. I already tried to do that in one step like: 

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],tbl_LTCSREP," & _
rngCell_NoUsed.Column & ",FALSE),""NO LTCS VALUE AVAILABLE"")"

This did not work either. But I got it. Thanks again.

Upvotes: 0

Vegard
Vegard

Reputation: 4917

It pastes your variable as text because you've written it as text, not a variable.

When you write "Hello someVariable", the compiler interprets someVariable as a piece of text instead of a variable.

Solution:

"Hello " & someVariable

Or for your case:

Dim str As String
str = "=IFERROR(VLOOKUP(RC[-4],tbl_LTCSREP, " & rngCell_NoUsed.Column & ",FALSE),""NO LTCS VALUE AVAILABLE"")"
ActiveCell.FormulaR1C1 = str

You'll see that we've closed off the first part of the string by adding a ". Then, we tell the compiler to concatenate (&) with the contents of the variable. Then we add another concatenation operator, and we've also added another " to signify that we are once again inputting pure text.

The result can be thought of more simply in this way:
"text" & content_from_a_variable & "more text"

Upvotes: 0

Related Questions