Add up the qty then add a row to the end to show the qty

[zack8576]

I need to count the qty of "Rainguards" in a bunch of excel files, and add a row with these values as shown below (qty is in Column C):

Value in column D in the new row changes based on values in column K, and another value in N, see below:

see code below, any help is appreciated

Public c As Range
Public lr6 As Long
Public lr3 As Long
Public i As Long
Sub Rainguards()
    lr6 = lr3 + 1                    ' set lr6 to one row below lr2, which is the total number of eyebolts and chains
    Set c = Range("C" & lr6)    ' use this cell as our starting point
    With c
        .Offset(, -2).Value = .Offset(-1, -2).Value
        .FormulaR1C1 = "=COUNTIF(R2C11:R" & lr2 & "C11,""Rainguards"")"
        .Value = .Value
        .Offset(, -1).Value = "!"
        .Offset(, 6).Value = "Purchased"
        .Offset(, 8).Value = "Rainguard"
    If Range("K" & (Range("i", 7).Value)).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89997"
    ElseIf Range("K" & i7).Value Like "*170**580*" Then
        .Offset(, 1).Value = "F89998"
    ElseIf Range("K" & i7).Value Like "*170**580*" And _
       Range("N" & i10).Value Like "*Hernando*" Then
        .Offset(, 1).Value = "F89998U"
    ElseIf Range("K" & i7).Value Like "*225**440*" Then
        .Offset(, 1).Value = "F89999"
    ElseIf Range("K" & i7).Value Like "*667*" Then
        .Offset(, 1).Value = "F90003"
    End If
        .EntireRow.Font.Bold = True
    End With
    If ws1.Range("C" & i7).Value Like "*0*" Then
            ws1.Rows(lr6).Delete
    End If
End Sub

 

[zack8576]

i think you may need to break these up.

was
If x Like "*170**580*" Then Data = "F89998"
If x Like "*170**580*" And y Like "*Hernando*" Then Data = "F89998U"
If x Like "*225**440*" Then Data = "F89999"
If x Like "*667*" Then Data = "F90003"

try
If x Like "*170*" Then Data = "F89998"
If x Like "*580*" Then Data = "F89998"
If x Like "*170*" And y Like "*Hernando*" Then Data = "F89998U"
If x Like "*580*" And y Like "*Hernando*" Then Data = "F89998U"
If x Like "*225*" Then Data = "F89999"
If x Like "*440*" Then Data = "F89999"
If x Like "*667*" Then Data = "F90003"

Thank you so much !

 

[zack8576]

Working code posted here, in case if anyone else run into similar issues

Sub Rainguards()

sr = 2 'Starting Row
n = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr3), Range("K" & sr & ":K" & lr3), "*Rainguards*")
'MsgBox n
lr6 = lr3 + 1 'this is the new row at the bottom that we are going to write the data to.

Cells(lr6, "A") = Cells(lr6, "A")
Cells(lr6, "C") = n 'number of Rainguards
Cells(lr6, "I") = "Purchased"
Cells(lr6, "K") = "Rainguards"


''''part 2
Set TargetCell = Range("K" & sr & ":K" & lr).Find(What:="Rainguards", _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
rr = TargetCell.Row

Data = ""
v = Cells(rr, "K")
s = Cells(rr, "N")

If v Like "*170*" Then Data = "F89998"
If v Like "*580*" Then Data = "F89998"
If v Like "*170*" And s Like "*Hernando*" Then Data = "F89998U"
If v Like "*580*" And s Like "*Hernando*" Then Data = "F89998U"
If v Like "*225*" Then Data = "F89999"
If v Like "*440*" Then Data = "F89999"
If v Like "*667*" Then Data = "F90003"

Cells(lr6, "D") = Data

End Sub

 

[zack8576]

Sub Add_Rainguards()

sr = 2 'Starting Row
lr = Cells(Rows.Count, "A").End(xlUp).Row ' Last Row
n = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*Rainguards*")
MsgBox n
wr = lr + 1 'this is the new row at the bottom that we are going to write the data to.

Cells(wr, "A") = Cells(lr, "A")
Cells(wr, "C") = n 'number of Rainguards
Cells(wr, "I") = "Purchased"
Cells(wr, "K") = "Rainguards"


''''part 2
Set TargetCell = Range("K" & sr & ":K" & lr).Find(What:="Rainguards", _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
rr = TargetCell.Row

Data = ""
x = Cells(rr, "K")
y = Cells(rr, "N")

If x Like "*170**580*" Then Data = "F89998"
If x Like "*170**580*" And y Like "*Hernando*" Then Data = "F89998U"
If x Like "*225**440*" Then Data = "F89999"
If x Like "*667*" Then Data = "F90003"

Cells(wr, "D") = Data

End Sub

[/CODE]

WHen I run this on a file that does not contain rainguards, which there are some files like that
this message pops up "Object variable or with block variable not set", is there any way to stop this error from happening?
below is an example test file

2022080071 SANITARY.csv

Shared with Dropbox

www.dropbox.com