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

 

[rpaulson]

does this help?

Sub Add_Rainguards()

sr = 279 'Starting Row
lr = 293 ' Last Row
n = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*Rainguards*")
MsgBox n

End Sub

-Ross

 

[zack8576]

does this help?

Sub Add_Rainguards()

sr = 279 'Starting Row
lr = 293 ' Last Row
n = WorksheetFunction.SumIfs(Range("C" & sr & ":C" & lr), Range("K" & sr & ":K" & lr), "*Rainguards*")
MsgBox n

End Sub

-Ross

Ross, the cell range 279-293 was just the range shown in this screenshots, in reality this range needs to start from 2 to the very last row with data (could be any #)

Also, in the new row, value in A needs to match value in the column A one row above, column C shows qty, I shows Purchased, K shows Rainguard

value in D varies:
F89998 if value in K contains 170 or 580
F89998U if value in K contains 170 or 580, AND column N contains Hernando
F89999 if K contains 225 or 440
F90003 if K contains 667

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

 

[rpaulson]

Zack,

does this return what you want except for Column D

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

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"
End Sub

 

[zack8576]

Zack,

does this return what you want except for Column D

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

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"
End Sub

Ross

thanks for the reply, but this is strange, VBA is telling me sr is not defined? any idea?
I see it is defined as 2

 

[rpaulson]

try
dim sr as long, lr as long

 

[zack8576]

try
dim sr as long, lr as long

I got it fixed, I defined these 3 at the beginning as Public variables

Public sr As Long, n As Long, wr As Long

Any idea how to assign the correct value to column D? thanks !

 

[rpaulson]

Since your Column D is derived From column K, how do we know which row in Column K we need to use.
Example:
K10 might be "225 USF Ring with Rainguards"
K227 might be "580 USF Ring with Rainguards"

how do we know what one to evaluate?

 

[zack8576]

Since your Column D is derived From column K, how do we know which row in Column K we need to use.
Example:
K10 might be "225 USF Ring with Rainguards"
K227 might be "580 USF Ring with Rainguards"

how do we know what one to evaluate?

Ross

Each individual excel file will only contain one of those values:
if the first rainguard is a 170 in K, then all the rest of them will be 170;
if the first rainguard is a 225 in K, then all the rest of the rainguards will be 225 as well....etc

 

[rpaulson]

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