Excel VBA find method with 2 search values?

Question

Is there a way to use the FIND method to search for 1 of 2 alternative values? Set d = .Find(What:=[a OR b])

EDIT: Thanks for the UNION suggestions. I had no idea that was possible or I would've provided more detail originally to make the question more clear. So here's a 2nd try:

I have 3 columns. A is filled with dates. B is a code. And D is where I need to insert a function. The function will evaluate the code in colB and if the code is a "2", it will run the FIND method to find the nearest previous code 1 OR 2 in colB, then it will subtract the dates from colA on the two rows. For example:

A       |B  ...|D
[date1] |5     |[function does nothing]
[date2] |1     |[function does nothing]
[date3] |3     |[function does nothing]
[date4] |2     |[code 2 found, function inserts result of [date4]-[date2]

(last row would be same if 2nd row had a code 2 instead of a 1)

Here's the code I have now:

Private Function FindPrevActionCode(ActionCodeCell As Range, SearchCode As Byte)

'ActionCodeCell is the cell to check, search the value of that cell
'SearchCode is the code to look for

Dim d As Range 'Holds result of Find method

If ActionCodeCell.Value = 2 Then
  With Worksheets(AppTab).Columns(cnAppActionCode)
       Set d = .Find(What:=SearchCode, After:=ActionCodeCell, _ 
       LookIn:=xlValues, LookAt:= _
       xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ 
       MatchCase:=False, SearchFormat:=False)
  End With

FindPrevActionCode = Cells(ActionCodeCell.row, cnAppTransEffDate).Value  
 - Cells(d.row, cnAppTransEffDate).Value


End If
End Function

Could you run the find method for each value you want and then union them together? — Generic Guy
– Generic Guy, Commented Aug 3, 2016 at 2:36

Robin Mackenzie · Accepted Answer · 2016-08-03 02:36:31Z

1

You can do two separate searches and then use the Union function to combine the two Ranges. E.g.:

Option Explicit

Sub TestUnionFind()

    Dim rngSearch1 As Range
    Dim rngSearch2 As Range
    Dim rngUnionSearch As Range

    Set rngSearch1 = Sheet1.Cells.Find(What:="A")
    Set rngSearch2 = Sheet1.Cells.Find(What:="B")
    Set rngUnionSearch = Union(rngSearch1, rngSearch2)

    Debug.Print rngUnionSearch.Address

End Sub

answered Aug 3, 2016 at 2:36

Robin Mackenzie

19.4k7 gold badges42 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user6432984 · Accepted Answer · 2016-08-03 02:41:30Z

0

You will have to do each search separately.

Set d = .Find(What:=a, After:=ActionCodeCell, LookIn:=xlValues, _ 
LookAt:= xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ 
MatchCase:=False, SearchFormat:=False)

If d is Nothing Then

    Set d = .Find(What:=b, After:=ActionCodeCell, LookIn:=xlValues, _ 
    LookAt:= xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ 
    MatchCase:=False, SearchFormat:=False)

End If

answered Aug 3, 2016 at 2:41

user6432984

Collectives™ on Stack Overflow

Excel VBA find method with 2 search values?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related