Find with 2 criteria to be met

**retos110** · 01-27-2012, 10:05 PM

Hi

Is it possible to have 2 criteria in the what part of find? I have tried the & and it does not work

At the moment I have a find in an if statement based on the other find ie:
find criteria1
if so find criteria2

Thanks

Set c = Worksheets("MFS").Range("A1:a1000").Find(what:="enbl", lookat:=xlPart)

**JapanDave** · 01-27-2012, 10:40 PM

I am not sure if this is what you are after, but

Dim cel as String, cel1 as String
  
  cel = Range("A1").Value
  cel1 = Range("B2").Value
  
     enbl = cel & cel1
  Set c = Worksheets("MFS").Range("A1:a1000").Find(what:="enbl", lookat:=xlPart)

Edit: Of coarse replace the cells with the criteria you are after.

**retos110** · 01-30-2012, 06:54 PM

Hi

Thanks for the response. I tried that and its not quite what Im looking for. What I would want it to do is search for 2 seperate criteria simultanously. Something like this which doesnt work:

.Find(what:="enbl" & what:=prodcode, lookat:=xlPart)

Thanks again
Mike

**rylo** · 01-30-2012, 07:32 PM

Mike

Can you attach an example workbook to better explain what you are trying to achieve.

rylo

**retos110** · 01-30-2012, 07:58 PM

Hi Rylo

Not sure how to attach a file but here is the code. Essentially, in a cell Im trying to find a 5 digit number, for example 12621 and then looking for the word enbl

34:1 1262102 Enbl

I have it right now to look for the 5 digit number and if its found look for enbl. What I would like to do is have look for both the 5 digit number and the word enbl simultaneously in one find statement. Is this possible?

Thanks
Mike

For x = 1 To (lastrowsheet2 - 1)
        v = prodcodelist(x)
        
       
        Set c = Worksheets("MFS").Range("c1:c1000").Find(what:=v, lookat:=xlPart)
        
    If c Is Nothing Then
        'Cells(x + 1, 7) = v & " not found" 'prodcode not found
    Else
       ' Cells(x + 1, 6) = v & " found in cell " & c.Address
           
        
        Set d = c.Find(what:="enbl", lookat:=xlPart)
        If d Is Nothing Then
           ' Cells(x + 1, 8) = v 'dsbl
        Else 'enbl code below
            For t = 1 To j
                If finallist(t) <> v Then
                    chk = chk + 1
                End If
            Next
            If chk = j Then
                j = j + 1
                finallist(j) = v
                Worksheets("LIST").Cells(j, 1) = v 'enbl
                chk = 0
            End If
            chk = 0 'needs to be reset before reentering loop
        End If
    End If
Next

**JapanDave** · 01-30-2012, 07:52 PM

I agree with rylo, that will help makes things a little clearer.

**JapanDave** · 01-30-2012, 08:08 PM

Are both values in separate ranges? This sounds like advanced filter would be a better path to go.

BTW, you can post an attachment by going to advanced post> Manage attachments> upload file from PC.

Dave

**rylo** · 01-30-2012, 08:27 PM

Hi

OK your what is going to be something like

what:="*12621*Enbl*"

From your code, I think the 12621 is actually the variable v so it would be

what:="*" & v & "*Enbl*"

HTH

rylo

**retos110** · 01-30-2012, 08:56 PM

Hi again

The asteriks worked perfectly- just tested it. There was a very small chance the product code would slip though using my code

Thank you and thanks for all the help this forum has provide

Mike