Insert Formula with Variable in VBA

Question

I'm trying to use a variable into formula using VBA. I've referenced How to pass an integer variable into a vba formula this question and tried implementing that in my if formula. =IF (startCell >0, startcell+confirmed, endcell+confirmed)

but I'm still unsure and getting an error.

My code is the following:

headerrow = 7
startCell = 8
endCell = 3
confirmed= 5

DDSS_Actual.Cells(headerrow + 3, 3).Formula = "=IF(" & startCell & ">" & endCell & ","& startCell & "+" &confirmed&", " & endCell & "+" &confirmed &")"

Thanks.

startCell is 8 and 8 is not a cell. Do you mean cells(startCell, 3)? — user4039065
@Jeeped Nope. startCell is a integer and its a variable that will change. it isnt pointing to a cell. i shouldnt have named it startcell. — Nihara
Hard-coding numbers into a 'formula' is pretty useless. Why wouldn't you adjudicate the criteria and write the result into the cell? — user4039065

Raunak Thomas Raunak Thomas · Accepted Answer · 2018-05-11T06:58:51

You just need to put spaces before and after the &

which makes it

DDSS_Actual.Cells(headerrow + 3, 3).Formula = "=IF(" & startCell & ">" & endCell & "," & startCell & "+" & confirmed & "," & endCell & "+" & confirmed & ")"

Insert Formula with Variable in VBA

1 Answers