# Formula syntax and punctuation (1 Viewer)

#### Leo_Polla_Psemata

##### Registered User.
Works

Code:
``````.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

Last edited:

#### arnelgp

##### ..forever waiting... waiting for jellybean!
how did you Define "i", is it Long?

#### Leo_Polla_Psemata

##### Registered User.
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

##### Registered User.
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

##### AWF VIP
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

##### Enthusiastic Amateur
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
Code:
``````? "=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

##### AWF VIP
You can use a constant for the double quote character:
Code:
``````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

##### Registered User.
In the immediate window
Code:
``````? "=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 </>

Replies
17
Views
1,405
Replies
13
Views
1,095
Replies
2
Views
1,592
Replies
10
Views
2,438
Replies
7
Views
4,561