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

 

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

this works great !
a little note though, this is part of a bigger macro, there are lr1, 2, 3, 4, 5, 6, 7, 8, 9 being used to serve other purposes.
lr6 is used for adding the last new row right before this evergrip new row, thats why I set lr10 for this subroutine, and set it to lr6 + 1

 

[Micron]

OK; you'll need to modify what I wrote just a bit I guess because for me, some variable values were zero so it was editing the first row. If you've got a solution, maybe mark this one as solved?

 

[zack8576]

OK; you'll need to modify what I wrote just a bit I guess because for me, some variable values were zero so it was editing the first row. If you've got a solution, maybe mark this one as solved?

I already marked your code above as the solution . thanks a bunch !

 

[Micron]

You're welcome. Good thing I'm not getting paid by the hour - I would have lost money on that one!

 

[zack8576]

You're welcome. Good thing I'm not getting paid by the hour - I would have lost money on that one!

Lol, thanks for all the help on this one !