Excel VBA Find based upon multiple criteria

[spydey]

I have some code, that when run from the current workbook (a template), it opens a different predetermined workbook (Sales Log), performs a find in column "A" in the Sale Log to determine the corresponding row of the item # from the template, and places certain results in the Sales Log in that row for a specific column.

Example: In the template I have item # 24538. When I run the code, it opens the Sales Log, locates the row where item # 24538 (from the template) is located, and adjusts the cell in the 6th column for that corresponding row in the Sales Log.

Here is the code:

Public Sub Pending()

Dim FindRowNumber, Item As Long
Dim wbT, wbL As Workbook
Dim wsT, wsL As Worksheet


Set wbT = ThisWorkbook
Set wsT = wbT.Worksheets(1)
Set wbL = Workbooks("Sales Log.xlsm")
Set wsL = wbL.Worksheets("Assigned")


With wsT
    Item = .Range("Item").Value
End With
Application.ScreenUpdating = False
With wsL
    On Error Resume Next
    .ShowAllData
    Set FindRow = .Range("A:A").Find(What:=Item, LookIn:=xlValues, LookAt:=xlWhole)
End With
FindRowNumber = FindRow.Row
wsL.Cells(FindRowNumber, 1).Offset(0, 7).Value = "PENDING"
wbL.Save
Application.ScreenUpdating = True


End Sub

However, now I need to add an additional layer of criteria to the Find, so that not only does it find the row where the item number is located, but also the corresponding type.

I could have the same item # multiple times in the column "A", but in column "B" I have several different types, such as winter, summer, fall, spring, etc.

Example: I could have item # 12458 twice, one with Summer and one with Fall.

When I run my code, I need it to find the row where with the item # and the Type of Fall, then make the update to the appropriate row.

Any ideas?

-Spydey

 

[spydey]

Re: Excel VBA Find based upon multiple criteria ... ????

So doing a bit more research, I see that .Find does not support multiple criteria.

That leads me to think that maybe I do a find for the first criteria, then a find for the next criteria.

Would that work?

-Spydey

 

[Akuini]

Re: Excel VBA Find based upon multiple criteria ... ????

Untested:

With wsL
    On Error Resume Next
    .ShowAllData
    Set findrow = .Range("A:A").Find(What:=Item, LookIn:=xlValues, LookAt:=xlWhole)
    If Not findrow Is Nothing Then
        [COLOR=#0000ff]If findrow.Offset(, [/COLOR][COLOR=#0000ff][B]2[/B][/COLOR][COLOR=#0000ff]) = "Fall" Then [/COLOR]      
            findrow.Offset(, 7).Value = "PENDING"
        End If
    End If
            
End With
wbL.Save

Note: If you want to find the Item multiple times then you must use Loop.

Edit: actually the code won't work as expected, you need to loop the find to do that.

 

[Akuini]

Re: Excel VBA Find based upon multiple criteria ... ????

Maybe this (untested):

Public Sub Pending()

Dim FindRowNumber, Item As Long
Dim wbT, wbL As Workbook
Dim wsT, wsL As Worksheet


Set wbT = ThisWorkbook
Set wsT = wbT.Worksheets(1)
Set wbL = Workbooks("Sales Log.xlsm")
Set wsL = wbL.Worksheets("Assigned")


With wsT
    Item = .Range("Item").Value
End With
Application.ScreenUpdating = False
With wsL
    On Error Resume Next
    .ShowAllData
    Set findrow = .Range("A:A").Find(What:=Item, LookIn:=xlValues, LookAt:=xlWhole)
    If Not findrow Is Nothing Then
        FirstAddress = findrow.Address
        Do
        If findrow.Offset(, 2) = "Fall" Then
            findrow.Offset(, 7).Value = "PENDING"
            'If you only need the first match
            'Exit Do
        End If

            Set findrow = .Range("A:A").FindNext(findrow)
        Loop While Not findrow Is Nothing And findrow.Address <> FirstAddress
    End If
            
End With
wbL.Save
Application.ScreenUpdating = True


End Sub

 

[spydey]

Re: Excel VBA Find based upon multiple criteria ... ????

@Akuini, thanks for the code and the info.

I tested it out and it looks like it is correctly looping through all iterations if the item #.
However, it doesn't appear to be catching the Findrow.Offset(, 2) = "Fall".

When it gets to the row where the Item # and "Fall" are present, it doesn't update the corresponding column for that row.
It just loops through all iterations of the Item # then quits.

I uncommented the "Exit Do" to see if it exits the Do Loop once it finds the "FAll" criteria. It doesn't stop, it just keeps on going through the iterations.

Any idea as to why?

I am looking at the code and I think

-Spydey

P.S. I was reading something that @AlphaFrog posted a while back that I think will be helpful, but I need to study it a bit more to understand how to implement it into what I am trying to do and with what you have provided me.

https://www.mrexcel.com/forum/excel-questions/880147-range-find-multiple-criteria.html

 

[Akuini]

Re: Excel VBA Find based upon multiple criteria ... ????

Sorry, the offset should be 1 not 2:

If findrow.Offset(, 1) = "Fall" Then

 

[spydey]

Re: Excel VBA Find based upon multiple criteria ... ????

Sorry, the offset should be 1 not 2:

If findrow.Offset(, 1) = "Fall" Then

Hahahah, I should have noticed that too .... hahahaha, here I was racking my brain and it was very obvious.

I know how to use offset but didn't catch that it was looking too far to the right. Sorry.

I will give it a try again.

-Spydey

 

[spydey]

Re: Excel VBA Find based upon multiple criteria ... ????

Sorry, the offset should be 1 not 2:

If findrow.Offset(, 1) = "Fall" Then

Spot on sir!! Thanks again.

-Spydey

 

[Akuini]

Re: Excel VBA Find based upon multiple criteria ... ????

You're welcome, glad to help, & thanks for the feedback.