CODEWITHSUNDEEP
excelvbaformula
kmr47
kmr47

Reputation: 15

how to use a variable as part of inserting a formula

this code works 

ActiveCell.FormulaR1C1 = "='[2019 Comm Tables.xlsx]table-ZZ'!R5C8"

I want to replace the hardcoded ZZ (part of sheet name) with a value from a variable

I have 

Dim avar as string
avar ="AA"
ActiveCell.FormulaR1C1 = "='""[2019 Comm Tables.xlsx]table-"" & avar &  "" '!R5C8"""

this generates

'object defined error'

Any help on sorting out my coding errors would be gratefully received

Upvotes: 1

Views: 58

Answers (2)

teylyn
teylyn

Reputation: 35905

You don't need to double up the double quotes, unless you have double quotes at the beginning of the string you're quoting. 

   Dim avar As String
    avar = "AA"
    ActiveCell.FormulaR1C1 = "='[2019 Comm Tables.xlsx]table-" & avar & "'!R5C8"

Also, you had one space too many before the closing single quote.

Details matter.

Upvotes: 1

braX
braX

Reputation: 11755

Try this:

ActiveCell.FormulaR1C1 = "='[2019 Comm Tables.xlsx]table-" & avar & "'!R5C8"

Upvotes: 0

Related Questions