Adding up the qty of an item and put this value in a new row

[zack8576]

I need to calculate the total inches of item "Evergrip" in some excel files, then apply a simple equation to convert this number into rolls.

In my code, I would remove the inch mark and the text "/JOINT" from column M, count the total inches, then take this value divide by 12 then divide by 14.5 to get the number of rolls.
Then a new row is added after the last row that contains any value, the number of rolls along with few other values will be added to this new row.
If the number of rolls is 0, then the value in column D on this row gets replaced by a comma.

code below, test file attached, the result from the calculation looks wrong, so there is a mistake in the code. any help is greatly appreciated

Public sr As Long, lr11 As Long, lr10 As Long, rng2 As Range, n As Long
Sub Sealant()
    sr = 2 'Starting Row
    Set rng2 = Range("A1").CurrentRegion
    lr11 = rng2.Cells(Rows.Count, "M").End(3).Row
        If rng2.Cells(lr4, "M").Value Like "/JOINT" Then
           With Range("M1", Cells(Rows.Count, "M").End(3))
            .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart 'REMOVE THE INCH MARK
            .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart ' REMOVE /JOINT
           End With
        End If
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*Evergrip*") 'COUNT THE TOTAL NUMBER IN M, IF COLUMN K CONTAINS THE KEYWORD JOINT SEALANT.
    lr10 = lr6 + 1
    Cells(lr10, "A") = Cells(lr, "A")
    Cells(lr10, "B") = "."
    Cells(lr10, "C") = n / 12 / 14.5
    Cells(lr10, "D") = "F09510A"
    Cells(lr10, "I") = "Purchased"
    Cells(lr10, "K") = "Evergrip"

    'IF COLUMN C IN THE NEW ROW IS 0, VALUE IN D GETS REPLACED BY ,
    If Cells(lr10, "C").Value Like "*0*" Then
        Cells(lr10, 4) = ","
    End If
End Sub

2022080011 SANITARY.csv

Shared with Dropbox

www.dropbox.com

 

[Automatrix]

Power Query solution: Filter column K for "Evergrip". Extract before delimiter " in column M and convert type to number. Group By all columns except M, group type Sum with regards to column M. Do the divisions via Transform.

 

[zack8576]

Power Query solution: Filter column K for "Evergrip". Extract before delimiter " in column M and convert type to number. Group By all columns except M, group type Sum with regards to column M. Do the divisions via Transform.

while power query is very useful, I do want to know how to solve this issue using VBA. thanks for the reply !

 

[ValuationMan]

Not sure why you need this is VBA as oppossed to Power Query or a formula, but this is how I would do it. You can adjust some elements to make it more adaptable to different ranges, but hopefully this gets you there.

Dim rng As Range
Dim inches As Double
For Each rng In Range("K2:K191")
    If rng = "Evergrip Sealant + 9"" Wrapid-Seal" Then
            inches = inches + CLng(Left(rng.Offset(0, 2), 3)) / 12 / 14.5
    End If
Next rng