Use union with find

0 votes

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?

Mar 19, 2023 in Others by narikkadan
• 63,420 points

1 answer to this question.

0 votes

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) & """.", _
answered Mar 19, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to use next-seo for setting nextjs meta tag with multiple OGP images? use this git repo that contains ...READ MORE

answered Feb 24, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel use SUMIF but with a multiplier before summation: x2, x3, x4, etc

The product of the sum and the ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

Exclude worksheets from loop with a list

You can achieve this by making a ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP