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]

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

thank you
I got a message "Type mismatch" from

Data = ""

in the beginning of the macro I defined Data as a public value

Data As Long

You know why that message poped up? I think we are very close

 

[rpaulson]

Data As String

 

[zack8576]

Data As String

got it, also x As Long, y As Long ?

 

[rpaulson]

x and y as string

 

[zack8576]

x and y as string

Okay, I ran this a few times, seems like everything is working fine.
except column D is blank..no error message is poping up

 

[rpaulson]

can you upload a sample of you file somewhere, so we can take a look at it?

 

[zack8576]

can you upload a sample of you file somewhere, so we can take a look at it?

MIDDLEMAN V1.6.2 XYPEX INVERT FIXED.xlsb

Shared with Dropbox

www.dropbox.com

2022080081 RAINGUARD.csv

Shared with Dropbox

www.dropbox.com

First link is where the macro resides, click on the large Middleman button, browse to the second file, and the macro will automatically run

 

[rpaulson]

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"

 

[zack8576]

can you upload a sample of you file somewhere, so we can take a look at it?

When you look at the actual codes, it is the "Rainguard" portion of the code, pretty close to the bottom of the macro

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"

I will test it a few times. May I ask why do these have to be broken up?

 

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

Tested it on 3 different files, it works like a charm now !!!