Sumif with VBA

[Nelson78]

Hello everybody.

I'm running on empty with the following matter.


Writing this in a cell it works fine:

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

 

[mumps]

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

 

[Marcelo Branco]

Maybe something like this

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.

 

[steve the fish]

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

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 & ")"

 

[Nelson78]

Maybe something like this

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?

 

[Marcelo Branco]

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/office/vba/language/reference/user-interface-help/data-type-summary

M.