# Thread: need correc syntax

1. Hi All
Stuck with this in vba editor

Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a5&"?",c6:c65000)"
get compiler error -unexpected end of statement with the"?" marked

this works but is not what I want ---it tranfers formula but without & "?"
Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a5,c6:c65000)"
thanx
smbs

2. Try changing this

Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a5&"?",c6:c65000)"

to

Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a5&" & Chr(34) & "?" & Chr(34) & ",c6:c65000)"

The quotes in your formula need to be coded into VBA statement.

3. many thanx-it drove me crazy!!! for hours
Smbs

4. still have a problem
this works fine as by earlier help
Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a5&" & Chr(34) & "?" & Chr(34) & ",c6:c65000)"
but I now need to have the following
Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a &(r-1) &" & Chr(34) & "?" & Chr(34) & ",c6:c65000)"
the row number must a variable (r-1) instead of the constant value of 5 in original formula
hope I made things clear
thanx
Smbs

5. Try

Cells(r - 1, c + 2).Formula = "=sumif(\$a6:\$a65000,a" & (r - 1) & "&" & Chr(34) & "?" & Chr(34) & ",c6:c65000)"

6. thanx Hans
just dont quite understand the logic
Smbs

7. "=sumif(\$a6:\$a65000,a" & (r - 1) & "&" & Chr(34) & "?" & Chr(34) & ",c6:c65000)"

Part 1: "=sumif(\$a6:\$a65000,a" - the beginning of the formula

Part 2: (r - 1) - this is outside the quotes so it is evaluated, not included literally in the string

Part 3: "&" - this is the ampersand that will end up in the formula itself

Part 4: Chr(34) - this is the ascii code for the double quote ". We cannot use " inside a quoted string, so we must either double it or concatenate with Chr(34)

Part 5: "?" - this is the question mark

Part 6: Chr(34) - another quote "

Part 7: ",c6:c65000)" - the end of the formula

These 7 parts are concatenated by &.

8. Many many thanx Hans I really admire your patience!!!
I will try and understand it
Once again many thanx
Smbs

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•