I am trying to create a formula using R1C1 format that will change based on a string containing a variable. I have tried creating the string and inputting it in the formula, as well as creating the string in the formula and none seems to work. Below is my code:
Dim NewXX As Integer
Dim NewXX1 As Integer
Dim CE As Integer
Dim PrevCE As Integer
Dim CEText As String
CE = Cells(NewXX - 1, 1).Value + 1
CEText = "=" & CE
ActiveCell.FormulaR1C1 = _
"=SUMIFS(R[-209]C8:R[1]C8,R[-209]C1:R[1]C1,CEText,R[-209]C2:R[1]C2,""<>Summary"")*(1+R1C16)"
CEText is the variable that will change every time the macro is run. A few things I have tried: CEText ""CEText"", '"&CEText&"', "CEText", ""="""&CE&", ""=""CE,
All of these trials either give me an 'Expected: end of statement' error, or the formula displayed in the cells matches the text (not value) found in the code.
Any help would be greatly appreciated! I am fairly new to VBA and am always up for learning a better way to do things! Thanks!
CEText is not need when you are equating and since CE is a number, we only need to worry about removing from formula string and concatenating:
ActiveCell.FormulaR1C1 = _
"=SUMIFS(R[-209]C8:R[1]C8,R[-209]C1:R[1]C1," & CE & ",R[-209]C2:R[1]C2,""<>Summary"")*(1+R1C16)"
If you want CEText then we do the extra quotes in the formula:
ActiveCell.FormulaR1C1 = _
"=SUMIFS(R[-209]C8:R[1]C8,R[-209]C1:R[1]C1,""" & CEText & """,R[-209]C2:R[1]C2,""<>Summary"")*(1+R1C16)"
The main issue is that any vba variable must be outside the quotes and concatenated with &