add up certain items then multiply by a constant value and display the result on another row

[zack8576]

I need my macro to calculate the quantity of item "evergrip", the calculation goes like the following:
It calculates the total number of "Base", "Riser", "Cone", "Top slab" with the keyword "Sanitary" in column O.
the qty of these items are always in column C, the description of these items are in column K.

the macro then run this equation: (total # of these items - 1) * 14 and display the qty on a new row that is added after the last row with any data

see my code below, it looks like this is not counting the items correctly, my result always returns as -14

test file link below

2022080011 SANITARY.csv

Shared with Dropbox

www.dropbox.com

Public sr As Long, n As Long, lr10 As Long, lr As Long, lr6 As Long
Sub SealantEvergrip()
    lr = ws1.Cells(Rows.Count, "K").End(xlUp).Row
    sr = 2 'Starting Row
    n = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*Base**Riser**Cone**Top Slab*", Range("O" & sr & ":O" & lr), "*Sanitary*")
    lr10 = lr6 + 1 'this is the new row at the bottom that we are going to write the data to.

    Cells(lr10, "A") = Cells(lr, "A")
    Cells(lr10, "B") = "."
    Cells(lr10, "C") = (n - 1) * 14
    Cells(lr10, "D") = "F09510A"
    Cells(lr10, "I") = "Purchased"
    Cells(lr10, "K") = "Evergrip"

    If Cells(lr10, "C").Value Like "*0*" Then
     Cells(lr10, 4) = ","
    End If
End Sub

 

[Micron]

Then n = zero.

 

[zack8576]

Then n = zero.

thanks, n shouldn't be zero though. In the test file I posted, there are 58 items that meet the criterias, therefore the value of n should be 58.
There might be a mistake in my code, where it is not counting these items correctly

 

[Micron]

I just checked. The formula returns zero even if it's in a sheet cell:
=SUMIFS(C2:C191, K2:K191, "*Base**Riser**Cone**Top Slab*", O2:O191, "*Sanitary*")

Formulas are not my strong point and I'm not familiar with SumIfs. Maybe I can learn what that one does and figure it out but I'm not going to bet on it.

 

[Micron]

try
=SUM(SUMIFS(C2:C191,K2:K191,{"*Base*","*Riser*","*Cone*","*Top Slab*"},O2:O191,"Sanitary"))

I get 59, not 58

 

[zack8576]

try
=SUM(SUMIFS(C2:C191,K2:K191,{"*Base*","*Riser*","*Cone*","*Top Slab*"},O2:O191,"Sanitary"))

I get 59, not 58

thank you, got an error message, a list separator is missing
also, some of the files will contain closer to a thousand lines, so the last row will not always be 191.

 

[Micron]

I used 191 and 2 to test because that is the row your code returned. So that's a test for a sheet version.
Just edit your formula in your code using the array and Sum as I did. The main thing is that I didn't get zero as a result.
Your use of ws1 had to be commented out and replaced with a cells reference.

EDIT - looks like there is a trick to adding { to the formula when using vba.

 

[Micron]

This prints 59 and writes 812 in the target cell

Sub SealantEvergrip()

'lr = ws1.Cells(Rows.Count, "K").End(xlUp).Row
lr = Cells(Rows.Count, "K").End(xlUp).Row '<< ws1 is not declared or set
sr = 2 'Starting Row
n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*Sanitary*"))

Debug.Print n

''lr10 = lr6 + 1 'this is the new row at the bottom that we are going to write the data to.
lr10 = lr + 1 '<< lr10, lr6 is zero, so you're writing in the top row, not the last. Don't see a need for lr10 or lr6
Cells(lr10, "A") = Cells(lr, "A")
Cells(lr10, "B") = "."
Cells(lr10, "C") = (n - 1) * 14
Cells(lr10, "D") = "F09510A"
Cells(lr10, "I") = "Purchased"
Cells(lr10, "K") = "Evergrip"

If Cells(lr10, "C").Value Like "*0*" Then
  Cells(lr10, 4) = ","
End If

That only took about 4 hours of playing around and researching.

 

[zack8576]

I used 191 and 2 to test because that is the row your code returned. So that's a test for a sheet version.
Just edit your formula in your code using the array and Sum as I did. The main thing is that I didn't get zero as a result.
Your use of ws1 had to be commented out and replaced with a cells reference.

EDIT - looks like there is a trick to adding { to the formula when using vba.

thats awesome you found that little trick on the {, I never knew that.

 

[zack8576]

This prints 59 and writes 812 in the target cell

Sub SealantEvergrip()

'lr = ws1.Cells(Rows.Count, "K").End(xlUp).Row
lr = Cells(Rows.Count, "K").End(xlUp).Row '<< ws1 is not declared or set
sr = 2 'Starting Row
n = Application.Sum(Application.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), _
Array("*Base*", "*Riser*", "*Cone*", "*Top Slab*"), Range("O" & sr & ":O" & lr), "*Sanitary*"))

Debug.Print n

''lr10 = lr6 + 1 'this is the new row at the bottom that we are going to write the data to.
lr10 = lr + 1 '<< lr10, lr6 is zero, so you're writing in the top row, not the last. Don't see a need for lr10 or lr6
Cells(lr10, "A") = Cells(lr, "A")
Cells(lr10, "B") = "."
Cells(lr10, "C") = (n - 1) * 14
Cells(lr10, "D") = "F09510A"
Cells(lr10, "I") = "Purchased"
Cells(lr10, "K") = "Evergrip"

If Cells(lr10, "C").Value Like "*0*" Then
  Cells(lr10, 4) = ","
End If

That only took about 4 hours of playing around and researching.

AWESOME ! please allow me sometime to test this, I have 4-5 files I need to run this macro on
will let you know how the test went