if value exist in multiple non-adjacent ranges ?

[kds14589]

I'm having troubles finding a value ("Not Assigned") in two non-adjacent ranges. Both ranges are populated from these codes,,,

 .Range("AD5:AD100").Value = "=IF(ISBLANK(RC[-1]),"""",IF(ISERROR(MATCH(RC[-1],R5C27:R" & LDR_EH_4_04 & "C27,0)),""Not Assigned"",""Used"" ))"    'STATUS
    .Range("AI5:AI100").Value = "=IF(ISBLANK(RC[-1]),"""",IF(ISERROR(MATCH(RC[-1],R5C27:R" & LDR_EH_4_04 & "C27,0)),""Not Assigned"",""Used"" ))" ' status

If the value "Not Assigned" is anywhere in those 2 ranges, then it will return a msgbox I made called MsgBoxNoMatch, if not then MsgBoxYesMatch.
The latest code I tried is below, but it is one attempt of many,,,

Dim AddOnRange As Range
    Set AddOnRange = AA04.Range("AD5:AD100")
Dim WorksheetRange As Range
    Set WorksheetRange = AA04.Range("AI5:AI100")
Dim MyRange As Range
    Set MyRange = Union(AddOnRange, WorksheetRange)
Dim str As String
    str = "Not Assigned"
Dim srchRng As Range
    Set srchRng = MyRange.Find(what:=str)
    
If srchRng Is Nothing Then  '''''Not Assigned' not present
    Load MsgBoxNoMatch
    MsgBoxNoMatch.Show
    Else
    Load MsgBoxYesMatch
    MsgBoxYesMatch.Show
End If

No matter what is in either range I get MsgBoxNoMatch, even when I tried a loop it still won't work. Any suggestions.

 

[p45cal]

This worked for me (I was searching plain values, not the results of formulae):

Dim AddOnRange As Range
Set AddOnRange = AA04.Range("AD5:AD100")
Dim WorksheetRange As Range
Set WorksheetRange = AA04.Range("AI5:AI100")
Dim MyRange As Range
Set MyRange = Union(AddOnRange, WorksheetRange)
Dim str As String
str = "Not Assigned"
Dim srchRng As Range
Set srchRng = MyRange.Find(What:=str, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If srchRng Is Nothing Then                       '''''Not Assigned' not present
  '    Load MsgBoxNoMatch
  '    MsgBoxNoMatch.Show
  MsgBox "No match"
Else
  '    Load MsgBoxYesMatch
  '    MsgBoxYesMatch.Show
  MsgBox "Match found"

End If

Many of the range.find arguments are remembered from its last use either in vba or on the sheet (the find dialogue box).
My version above may be wrong for your set up:
If you're on a Mac then SearchFormat:= might need to go.
LookIn:=xlFormulas2 might need to be xlFormulas (or more likely in your case xlValues).
SearchOrder:= and SearchDirection:= you might not need at all.
You may want LookAt:= to be xlWhole
MatchCase:= is remembered too so it's a good idea to specify it.

 

[Marc L]

Any suggestions.

A direct Excel basics demonstration - easy to find out yourself operating manually with the Macro Recorder - so removing the useless :​

Sub Demo1()
    If AA04.[AD5:AD100,AI5:AI100].Find("Not Assigned", , xlValues, 2) Is Nothing Then
        MsgBox "No match", 48
    Else
        MsgBox "Match"
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[kds14589]

A direct Excel basics demonstration

it works, THANKS