CODEWITHSUNDEEP
excelvba
Worst SQL Noob
Worst SQL Noob

Reputation: 189

How to use multiple variables for cell formula in VBA

I am trying to use multiple variables to control the formula I want to enter into a certain cell. My code is:

Sub test()
Dim a As String, b As String, c As String

With ThisWorkbook

a = .Sheets("Share").Cells(2, 21).Address()

b = .Sheets("Share").Cells(8, 21).Address(RowAbsolute:=False)
c = .Sheets("Share").Cells(8, 23).Address(ColumnAbsolute:=False)

MsgBox a  '$U$2
MsgBox b  '$U8
MsgBox c  'W$8

.Sheets("Share").Cells(8, 23).Formula = "=SUMIFS($E8:" & b & ",$E$2:" & a & "," & c& ")"

'=SUMIFS($E8:$U8,$E$2:$U$2,W$4)

End With
End Sub

But it keeps giving me this error:

Compile error: Syntax error.

Does anyone know why? I searched some topics online, but I didn't find a clear solution for that.

Upvotes: 0

Views: 385

Answers (1)

elixenide
elixenide

Reputation: 44823

You need a space between c and the following & in this line:

.Sheets("Share").Cells(8, 23).Formula = "=SUMIFS($E8:" & b & ",$E$2:" & a & "," & c & ")"

When you leave the space out, as in c&, the compiler misinterprets the & as an identifier type character instead of as the concatenation operator.

Upvotes: 2

Related Questions