Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Formula Variable in SUMIF (XP; SR3)

    I'm having an issue passing a variable to the following formula:

    Example:
    ActiveCell.Offset(0,2).Formula = "=SUMIF(B6:B1998,"John",D61998)"


    The row 1998 varies depending on the row of the ActiveCell so in theory I should be able to pass a variable into the line of code. Such as "oRow" = ActriveCell.Row
    ActiveCell.Offset(0,2).Formula = "=SUMIF("B6:B" & oRow &","John","D6" & oRow &")"

    The issue is with the additional quote marks in the formula. The original formula example does not have any quote marks.

    Any suggestions would be appreciated.
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA Formula Variable in SUMIF (XP; SR3)

    How about (you need an extra set of quotes around "John" since you want the quotes as quotes and not delimiters)

    ActiveCell.Offset(0, 2).Formula = _
    "=SUMIF(B6:B" & oRow & ",""John"",D6" & oRow & ")"

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Formula Variable in SUMIF (XP; SR3)

    Steve,

    It was too obvious. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I was looking for something more complex.

    Thanks,
    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA Formula Variable in SUMIF (XP; SR3)

    But it's always obvious when you know the answer <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

Posting Permissions

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