Countif and For loop not adding properly

[cadlum]

So this code below does actually work for me but it is not working how I want it to. My novice with VBA is probably to blame. I am trying to count how many computer types i have in 2 different spreadsheets, each with 5+ sheets. I open them, check the G column, then close them and export the results. I have debugged a little and it seems each of my variables stay the same number throughout the for loops. I have tried a couple different ways to get this to work but not sure what i am missing. Thanks for any help.

Sub compTypes()
Dim wb2 As Workbook, wb3 As Workbook, sh As Worksheet, NewSh As Worksheet, i As Long
Dim E4310 As Integer, E6320 As Integer, E6400 As Integer, E6410 As Integer, E6420 As Integer, E6430 As Integer, O780 As Integer, O790 As Integer, O7010 As Integer


Workbooks.Open Filename:="U:\Assets\Boler Inventory List.xlsx" 'Hard coded location of workbook
Workbooks.Open Filename:="U:\Assets\Hend Itasca Inventory List.xlsx" 'Hard coded location of workbook


Set wb2 = Workbooks("Boler Inventory List.xlsx") 'Full workbook name
Set wb3 = Workbooks("Hend Itasca Inventory List.xlsx") 'Full workbook name
Set NewSh = ThisWorkbook.Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count))


NewSh.Name = "Computer Types"


With NewSh
    .Range("A1") = "Computer Type"
    .Range("B1") = "Amount"
    .Range("A2") = "Dell Latitude E4310"
    .Range("A3") = "Dell Latitude E6320"
    .Range("A4") = "Dell Latitude E6400"
    .Range("A5") = "Dell Latitude E6410"
    .Range("A6") = "Dell Latitude E6420"
    .Range("A7") = "Dell Latitude E6430"
    .Range("A8") = "Dell Optiplex 780"
    .Range("A9") = "Dell Optiplex 790"
    .Range("A10") = "Dell Optiplex 7010"
End With


wb = Array(wb2, wb3)


For i = LBound(wb) To UBound(wb)
    For Each sh In wb(i).Sheets
        If Application.CountA(sh.Range("G:G")) > 0 Then
            E4310 = Application.WorksheetFunction.CountIf(Range("G:G"), "4310")
            E6320 = Application.WorksheetFunction.CountIf(Range("G:G"), "6320")
            E6400 = Application.WorksheetFunction.CountIf(Range("G:G"), "6400")
            E6410 = Application.WorksheetFunction.CountIf(Range("G:G"), "6410")
            E6420 = Application.WorksheetFunction.CountIf(Range("G:G"), "6420")
            E6430 = Application.WorksheetFunction.CountIf(Range("G:G"), "6430")
            O780 = Application.WorksheetFunction.CountIf(Range("G:G"), "780")
            O790 = Application.WorksheetFunction.CountIf(Range("G:G"), "790")
            O7010 = Application.WorksheetFunction.CountIf(Range("G:G"), "7010")
            With NewSh
                .Range("B2") = E4310 + NewSh.Range("B2")
                .Range("B3").Value = E6320
                .Range("B4").Value = E6400
                .Range("B5").Value = E6410
                .Range("B6").Value = E6420
                .Range("B7").Value = E6430
                .Range("B8").Value = O780
                .Range("B9").Value = O790
                .Range("B10").Value = O7010
            End With
        End If
    Next
Next


ThisWorkbook.Sheets("Computer Types").Copy
ActiveSheet.Columns.AutoFit


ActiveWorkbook.SaveAs "U:\Assets\Reports\Computer Types " & Format(Date, "mmm-yyyy") & ".xlsx"
Application.DisplayAlerts = False


ThisWorkbook.Sheets("Computer Types").Delete
Application.DisplayAlerts = True


Workbooks("Boler Inventory List.xlsx").Close SaveChanges:=False
Workbooks("Hend Itasca Inventory List.xlsx").Close SaveChanges:=False


End Sub

 

[Jonmo1]

YOu have to reference the sheet within the countif functions as well.
E4310 = Application.WorksheetFunction.CountIf(Range("G:G"), "4310")
should be
E4310 = Application.WorksheetFunction.CountIf(sh.Range("G:G"), "4310")

 

[hiker95]

cadlum,

You may want to try changing this:

            E4310 = Application.WorksheetFunction.CountIf(Range("G:G"), "4310")
            E6320 = Application.WorksheetFunction.CountIf(Range("G:G"), "6320")
            E6400 = Application.WorksheetFunction.CountIf(Range("G:G"), "6400")
            E6410 = Application.WorksheetFunction.CountIf(Range("G:G"), "6410")
            E6420 = Application.WorksheetFunction.CountIf(Range("G:G"), "6420")
            E6430 = Application.WorksheetFunction.CountIf(Range("G:G"), "6430")
            O780 = Application.WorksheetFunction.CountIf(Range("G:G"), "780")
            O790 = Application.WorksheetFunction.CountIf(Range("G:G"), "790")
            O7010 = Application.WorksheetFunction.CountIf(Range("G:G"), "7010")

To this:

            E4310 = E4310 + Application.CountIf(sh.Range("G:G"), "4310")
            E6320 = E6320 + Application.CountIf(sh.Range("G:G"), "6320")
            E6400 = E6400 + Application.CountIf(sh.Range("G:G"), "6400")
            E6410 = E6410 + Application.CountIf(sh.Range("G:G"), "6410")
            E6420 = E6420 + Application.CountIf(sh.Range("G:G"), "6420")
            E6430 = E6430 + Application.CountIf(sh.Range("G:G"), "6430")
            O780 = O780 + Application.CountIf(sh.Range("G:G"), "780")

 

[hiker95]

cadlum,

The following may be what you are looking for:

            E4310 = Application.CountIf(sh.Range("G:G"), "4310")
            E6320 = Application.CountIf(sh.Range("G:G"), "6320")
            E6400 = Application.CountIf(sh.Range("G:G"), "6400")
            E6410 = Application.CountIf(sh.Range("G:G"), "6410")
            E6420 = Application.CountIf(sh.Range("G:G"), "6420")
            E6430 = Application.CountIf(sh.Range("G:G"), "6430")
            O780 = Application.CountIf(sh.Range("G:G"), "780")

If not, can we see your workbook?

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[cadlum]

Thanks to you both! the 'sh.range' and the 'variable = variable + ....' worked great! Thanks for the quick help

 

[hiker95]

cadlum,

You are very welcome. Glad we could help.

Thanks for the feedback.

And, come back anytime.