CODEWITHSUNDEEP
vbaexcelwildcard
Ee Laine
Ee Laine

Reputation: 11

Using wildcard in SUMIF function in VBA

I have two worksheets Sheet1 and Sheet2. On Sheet1 I have a list of ItemNumber that I need to populate with inventory level (column East). I have to pull the information from column East from Sheet2. If the ItemNumber column on sheet2 contains the value of ItemNumber string from sheet1, then to sum all the quantity for East on Sheet1. I tried using SumIf function in my VBA code with the wildcard but all my values are returning as 0 on Sheet1. Can someone advise on how to correct this? Very much appreciated

On Sheet1:

Item Number East
WidgetA 
WidgetB 
WidgetC 
WidgetD

Sheet2 data:

Item Number East
WidgetA-1         1
WidgetA-2         2
WidgetA-3         3
WidgetB           2
WidgetB-1         4
WidgetC           1
WidgetD-1     1 
WidgetD-2         1

My code:

Dim i as Integer
Dim ItemNumber, East As Double

Do While Sheet1.Cells(i, 1).Value <> ""

ItemNumber = Sheet1.Cells(i, 1).Value
East = Application.WorksheetFunction.SumIf(Sheet2.Range("B:B"), """*" & ItemNumber & "*""", Sheet2.Range("Q:Q"))
Sheet1.Cells(i, 4).Value = East


i = i + 1


Loop

Upvotes: 0

Views: 1931

Answers (1)

bp_
bp_

Reputation: 432

Not sure why you are searching in the ranges you are, as those ranges do not match your example provided. I changed the ranges, declared ItemNumber as a String instead of a Double, and gave "i" a starting value of 2 to account for the column header. You also seemed to have extra quotations preceding the wildcard character. Hope this helps.

Dim i As Integer
Dim ItemNumber As String
Dim East As Double
    i = 2
    Do While Sheet1.Cells(i, 1).Value <> ""
        ItemNumber = Sheet1.Cells(i, 1).Value
        East = Application.WorksheetFunction.SumIf(Sheet2.Range("A:A"), "*" & _
             ItemNumber & "*", Sheet2.Range("B:B"))
        Sheet1.Cells(i, 2).Value = East
        i = i + 1
    Loop

Upvotes: 1

Related Questions