Page 1 of 2 12 LastLast
Results 1 to 10 of 12

SUMIF in VBA

This is a discussion on SUMIF in VBA within the Excel Questions forums, part of the Question Forums category; Hi, tried searching to see if i could avoid the post but, sorry could not find, so posting. i want ...

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Location
    Phoenix, USA
    Posts
    76

    Default SUMIF in VBA

    Hi,

    tried searching to see if i could avoid the post but, sorry could not find, so posting.

    i want a variable to have the value of a sumif condition.

    That is, say

    SUMACT = SUMIF(E28:I28,"",E27:I27)

    Thanks,
    Bhanu
    _______________________
    Bhanu
    Learning Excel @ MrExcel

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,946

    Default Re: SUMIF in VBA

    Try with

    Sumact = Application.Sumif(Range("E28:I28"), "", Range("E27:I27"))
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Dec 2003
    Location
    Phoenix, USA
    Posts
    76

    Default Re: SUMIF in VBA

    Hi Juan,

    Thanks,

    the same formula works in the sheet but not in VBA and always returns a '0'.

    Any help.

    Thanks,
    Bhanu
    _______________________
    Bhanu
    Learning Excel @ MrExcel

  4. #4
    Board Regular
    Join Date
    Dec 2003
    Location
    Phoenix, USA
    Posts
    76

    Default Re: SUMIF in VBA

    Any help !
    _______________________
    Bhanu
    Learning Excel @ MrExcel

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: SUMIF in VBA

    Juan’s macro snippet works for me:
    Code:
    Sub Macro1()
    
    Dim Sumact As Range
    Set Sumact = Sheets(1).Range("A1")
    
    Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))
    
    End Sub
    Alternative:

    Code:
    Sub Macro2()
    Dim Sumact As Range
    Set Sumact = Sheets(1).Range("A1")
    
    With Sumact
      .Formula = "=SUMIF(E28:I28,"""",E27:I27)"
      .Value = .Value
    End With
    
    End Sub
    Note:
    See the second macro. When you enter a worksheet function directly into VBA, you need a double "" to denote a blank.

    Regards,

    Mike

  6. #6
    Board Regular
    Join Date
    Dec 2003
    Location
    Phoenix, USA
    Posts
    76

    Default Re: SUMIF in VBA

    Hi,

    Can any one please let me know if i am doing something wrong here.

    i donot want a cell to have the value as i would be closing without changes rather i want a code variable to directly "get" the value afte doing a SUMIF.

    or is your way to do it, the only possible one.

    Thanks,
    Bhanu
    _______________________
    Bhanu
    Learning Excel @ MrExcel

  7. #7
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: SUMIF in VBA

    Simply re-define the variable Sumact --

    Dim Sumact as Double
    Sumact = ...
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: SUMIF in VBA

    Not sure what you mean when you say that you want a code variable to directly "get" the value after doing a SUMIF.

    A message box perhaps?
    Code:
    Sub Macro3()
    Dim Sumact As Long
    
    Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))
    
    MsgBox Sumact
    ' or do something else with the Sumact value?
    
    End Sub
    HTH

    Mike

  9. #9
    Board Regular
    Join Date
    Dec 2003
    Location
    Phoenix, USA
    Posts
    76

    Default Re: SUMIF in VBA

    Sorry for the confusion, i could not get my HTML maker to ork earlier, now here is what i want to do, i know it is simple but i am in a tangle some where, Thank you for your patience.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Forecast Sheet V1.9 - 2-23-04.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    E
    F
    G
    H
    I
    J
    25
    *****0
    26
    *****0
    27
    002019367280
    28
    657680**221
    29
    *****0
    30
    ******
    31
    ******
    Forecast - Hours *

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    i need,

    Sumact = 260 because it is the sum of cells E27:I27 where E28:I28 has no value.

    Thanks,
    Bhanu
    _______________________
    Bhanu
    Learning Excel @ MrExcel

  10. #10
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: SUMIF in VBA

    The below does not work?

    Dim Sumact as Double
    ' do stuff
    Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com