Formula syntax and punctuation (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Yesterday, 16:24
Joined
Mar 24, 2014
Messages
308
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 ":D" & 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!
Local time
Today, 07:24
Joined
May 7, 2009
Messages
16,393
how did you Define "i", is it Long?
 

Leo_Polla_Psemata

Registered User.
Local time
Yesterday, 16:24
Joined
Mar 24, 2014
Messages
308
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.
Local time
Yesterday, 16:24
Joined
Mar 24, 2014
Messages
308
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
Local time
Today, 00:24
Joined
Jul 26, 2013
Messages
9,286
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
1636020268314.png


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
Local time
Today, 00:24
Joined
Sep 21, 2011
Messages
10,531
Works
.Range("E7").Formula = "=SUMIFS(I10" & ":I" & i - 1 & " ,D10" & ":D" & 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
Local time
Today, 00:24
Joined
Jul 21, 2014
Messages
1,010
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.
Local time
Yesterday, 16:24
Joined
Mar 24, 2014
Messages
308
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 </>
 

Users who are viewing this thread

Top Bottom