Thread: Sumif with VBA Thanks: 0 Likes: 0

1. 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.

2. Re: Sumif with VBA

Can you post a screenshot of what you data looks like? What does "lastcol" represent?

3. 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.

4. 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. Re: Sumif with VBA

Originally Posted by Marcelo Branco
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. 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.

User Tag List

Posting Permissions

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