0

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.

Community
  • 1
  • 1
Skyblue
  • 35
  • 1
  • 1
  • 10
  • *startCell* is 8 and 8 is not a cell. Do you mean cells(startCell, 3)? –  May 11 '18 at 06:44
  • @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. – Skyblue May 11 '18 at 06:49
  • Hard-coding numbers into a 'formula' is pretty useless. Why wouldn't you adjudicate the criteria and write the result into the cell? –  May 11 '18 at 07:08

1 Answers1

0

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 & ")"
Raunak Thomas
  • 1,393
  • 1
  • 12
  • 28
  • If i change it a little to make my formula >0 and i tried this, changing >end row to >0 but i get errors : DDSS_Actual.Cells(headerrow + 3, DDSS_InvQTY).Formula = "=IF(" & Delta_val & " " > " & 0 & ", " & Delta_val & "+" & confirmed & "," & Delta_val2 & "+" & confirmed & ")" – Skyblue May 11 '18 at 07:16
  • What is your question? – Raunak Thomas May 11 '18 at 07:17
  • how do i write the formula to change the ">" & endCell & to ">" 0 ? I tried and i got errors – Skyblue May 11 '18 at 07:19
  • Make it ">0". You should have tried harder to figure this out yourself. – Raunak Thomas May 11 '18 at 07:21
  • yes i should have tried harder. i was going to just declare endcell = 0. And thank you – Skyblue May 11 '18 at 07:23