Excel VBA Find based upon multiple criteria

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
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:

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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
Re: Excel VBA Find based upon multiple criteria ... ????

Untested:

Code:
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.
 
Last edited:
Upvote 0
Re: Excel VBA Find based upon multiple criteria ... ????

Maybe this (untested):

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)
    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
 
Upvote 0
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
 
Last edited:
Upvote 0
Re: Excel VBA Find based upon multiple criteria ... ????

Sorry, the offset should be 1 not 2:

Code:
If findrow.Offset(, 1) = "Fall" Then
 
Upvote 0
Re: Excel VBA Find based upon multiple criteria ... ????

Sorry, the offset should be 1 not 2:

Code:
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
 
Upvote 0
Re: Excel VBA Find based upon multiple criteria ... ????

Sorry, the offset should be 1 not 2:

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


Spot on sir!! Thanks again.

-Spydey
 
Upvote 0
Re: Excel VBA Find based upon multiple criteria ... ????

You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top