Use union with find

Question

I want to search in columns B,C,G only , I used below code:

Set findName = shWaseet.Range("B:C","G:G").Find(Me.SearchBox.Value, LookIn:=xlValues, Lookat:=xlPart)

and it takes only B & C columns, I try

Set findName = shWaseet.Range("B1:C" & irow, "G1:G" & irow).Find(Me.SearchBox.Value, LookIn:=xlValues, Lookat:=xlPart)

and it didn't work, I know I have to use UNION, but I don't know how to use it with find, I tried:

Dim ran As Range
Set ran = Union(Range("B:C"), Range("G:G"))
Set findName = shWaseet.Range(ran).Find(Me.SearchBox.Value, LookIn:=xlValues, Lookat:=xlPart)

and it throws an error, also I tried:

Set findName = shWaseet.Union(Range("B:C"), Range("G:G")).Find(Me.SearchBox.Value, LookIn:=xlValues, Lookat:=xlPart)

and nothing. how to achieve that?

Answer 1

Using the Find Method With a Discontinued Range

The Objects

• There are 3 major objects in Excel:
  • Workbook
  • Worksheet
  • Range
• Each range has a worksheet and each worksheet has a workbook.
• If you e.g. use Range("A1") then this range is not qualified i.e. it is a range on the active sheet which may or may not be a worksheet and which may or may not be the correct worksheet (talking about Union(Range("B:C"), Range("G:G"))).
• Similarly, if you e.g. use Worksheets("Sheet1") then the worksheet is not qualified i.e. it is a worksheet in the active workbook which may or may not be the correct workbook.

The Variables

• Once you start using variables for each object, you will spare yourself many headaches.

  Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
  Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1") ' note the 'wb' 
  Dim rg As Range: Set rg = ws.Range("B:C,G:G") ' note the 'ws', or...
  Set rg = Union(ws.Range("B:C"), ws.Range("G:G")) ' note the 'ws', or...
  Set rg = Union(ws.Columns("B:C"), ws.Columns("G")) ' note the 'ws'
  

• Applied to you particular case, the simplest would be:

  Dim rg As Range: Set rg = shWaseet.Range("B:C,G:G") ' note the 'shWaseet'
  

  a single string with comma-separated addresses with less than 256 characters.

The Find Method

• The Find method will fail if the worksheet is filtered.
• If you need to find values in hidden rows or columns, you need to set the LookIn parameter to xlFormulas.
• The Find method has 9 arguments, the first 7 being of great importance (plenty to learn).
• What will happen when you use the Find method with your range "B:C,G:G"?
  • Depending on the value of the SearchOrder parameter, e.g. if it is xlByRows, it will start the search with cell C1 (not B1) and go down B2,C2,B3,C3,... until it reaches C1048576 after which it will continue the search with G1 to G1048576 and finally, it will look into cell B1. Basically, by default, it starts with the 2nd cell and ends with the 1st cell.
  • To avoid this peculiar behavior, you would want to set the parameter of the After argument to the last cell of the range.

The Last Cell

• The last cell of your range is cell G1048576. To set it dynamically, you can use:

  Dim lCell As Range
  With shWaseet.Range("B:C,G:G")
      With .Areas(.Areas.Count)
          Set lCell = .Cells(.Cells.Count)
      End With 
  End With
  

• If you don't expect the resulting cell to be the first cell of the range, you can ignore this last cell business as I have in the continuation.

The Solution

Dim sValue As Variant: sValue = Me.SearchBox.Value

Dim rg As Range: Set rg = shWaseet.Range("B:C,G:G")
' Omitting the 'After' argument i.e. not expecting the result in 'B1'
Dim fCell As Range: Set fCell = rg.Find(What:=sValue, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows)
If fCell Is Nothing Then
    MsgBox "Could not find """ & sValue & """.", vbCritical
    Exit sub ' ?
End If

' Continue
MsgBox "Found """ & fCell.Value & """ in """ & fCell.Address(0, 0) & """.", _
    vbInformation