Sumif with VBA
Results 1 to 6 of 6

Thread: Sumif with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    375
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Sumif with VBA

    Hello everybody.

    I'm running on empty with the following matter.


    Writing this in a cell it works fine:

    Code:
    =SUMIF(H19:H1891,"black",G19:G1891)

    Now, in my VBA code:

    lastcol + 1 = column in which you can find the values to sum
    lastcol + 2 = column in which you can find the criteria
    criteria = "black"
    row = from 19 (fix) to lr2

    How can I build the calculation?


    Thank's.
    Last edited by Nelson78; Jul 12th, 2019 at 11:04 AM.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,087
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sumif with VBA

    Can you post a screenshot of what you data looks like? What does "lastcol" represent?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Sumif with VBA

    Maybe something like this

    Code:
    Dim myVar as Double
    
    myVar = Application.SumIf(Range(Cells(19, lastcol + 2), Cells(lr2, lastcol + 2)), "Black",Range(Cells(19, lastcol + 1), Cells(lr2, lastcol + 1)))
    M.
    Last edited by Marcelo Branco; Jul 12th, 2019 at 11:26 AM.

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sumif with VBA

    If you want to place the formula in a cell maybe:

    Code:
    lastcol = 6
    lr2 = 1891
    criteria = "black"
    Set rng = Range(Cells(19, lastcol + 2), Cells(lr2, lastcol + 2))
    Set sum_rng = Range(Cells(19, lastcol + 1), Cells(lr2, lastcol + 1))
    
    
    Range("A1").Formula = "=SUMIF(" & rng.Address & ",""" & criteria & """," & sum_rng.Address & ")"

  5. #5
    Board Regular
    Join Date
    Sep 2017
    Posts
    375
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumif with VBA

    Quote Originally Posted by Marcelo Branco View Post
    Maybe something like this

    Code:
    Dim myVar as Double
    
    myVar = Application.SumIf(Range(Cells(19, lastcol + 2), Cells(lr2, lastcol + 2)), "Black",Range(Cells(19, lastcol + 1), Cells(lr2, lastcol + 1)))
    M.
    Yes, perfect.

    Just one clarification: why Double?

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Sumif with VBA

    If you want to add numbers with decimal places, 100.17, 50.45 etc, you need a Double variable to store the result.

    See
    https://docs.microsoft.com/en-us/off...a-type-summary

    M.

Some videos you may like

User Tag List

Tags for this Thread

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
  •