Formula syntax and punctuation

[Leo_Polla_Psemata]

Works

.Range("E7").Formula = "=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & ":D" & i - 1 & " ," & """OFT""" & ",F10:F2500," & """USD""" & ",J10:J2500," & """P"")"

Doesn't work

.Range("E8").Formula ="=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & ":D" & i - 1 & " ," & """OFT""" & ",F10" & ":F" & i - 1 & " & """USD""" & ", J10" & ":J" & i - 1 &, " & """P"")"

Hi
In my code, i need to use the sumiffs function

At first i used as absolute cell values such as D10 to D2500 and function works.
Then i replaced the D2500 with the "" & i - 1 & " and function does works.
However, when i try to replace the sum range after firts criteria, the F2500 with the ":F" & i - 1 & ", the lines turns to red and shows that there is a problem on criteris """OFT""

Is there any tool we could use and build our formulas and criteria without this punctuation headache?


Sorry for smilies , behind each smilie is the : D without space

 

[arnelgp]

how did you Define "i", is it Long?

 

[Leo_Polla_Psemata]

how did you Define "i", is it Long?

Yes, i is a number that counts records

i = 10
then
i = i + 1

But, since i works on the first part of the formula, why doesn't work on the next parts ?

 

[Leo_Polla_Psemata]

Okay, i found it,

It was a typing error,
Anyway, is there any smart way , tool or something, that we could use in order to compose flawlessly such lines ?

 

[Minty]

If you use a text editor such as Notepad ++ or Textpad, you can get it to highlight missing pairs of quotes and parentheses.
That can help a lot.

Also make sure your VBA editor is set to highlight errors, and that you set up a colour scheme that makes it more obvious where keywords and strings are in your code. Something like

Here you can clearly see the string values and the keywords.
Also when dealing with really long concatenations break them down onto seperate lines so you can clearly see each construct.

 

[Gasman]

Works
.Range("E7").Formula = "=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & "" & i - 1 & " ," & """OFT""" & ",F10:F2500," & """USD""" & ",J10:J2500," & """P"")"


Is there any tool we could use and build our formulas and criteria without this punctuation headache?

In the immediate window

? "=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & ":D" & i - 1 & " ," & """OFT""" & ",F10:F2500," & """USD""" & ",J10:J2500," & """P"")"
=SUMIFS(I10:I-1 ,D10:D-1 ,"OFT",F10:F2500,"USD",J10:J2500,"P")

 

[cheekybuddha]

You can use a constant for the double quote character:

Const DQ As String = """"    ' will output a single double quote

  .Range("E8").Formula = "=SUMIFS(I10:I" & i - 1 & ", D10:D" & i - 1 & ", " & DQ & "OFT" & DQ & ", F10:F2500, " & DQ & "USD" & DQ & ", J10:J2500, " & DQ & "P" & DQ & ")"

 

[Leo_Polla_Psemata]

In the immediate window

? "=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & ":D" & i - 1 & " ," & """OFT""" & ",F10:F2500," & """USD""" & ",J10:J2500," & """P"")"
=SUMIFS(I10:I-1 ,D10:D-1 ,"OFT",F10:F2500,"USD",J10:J2500,"P")

Hi, yes okay , i edited first message to include the code into </>