Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    many thanx-it drove me crazy!!! for hours
    Smbs

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

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

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanx Hans
    just dont quite understand the logic
    Smbs

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    "=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. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •