VBA search Criteria

Question

I am using below code to find a column.

Set FinColumn = .Find(What:="Tax", AFter:=.Cells(1, 1), LookIn:=xlValues, LookAt _
          :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).

if i have columns like below ,it should identify.

Tax     Tax&Fee    Tax&Fee1

How can i change above set statement to find all the above columns.is there any search criteria i can implement.

Thanks,

Chaitu

Answer 1

Tony has already given you one way. Here is another using wild cards. Now using wildcards is important because let's say you have cells where

A1 = Tax

B10 = Tax&Fee

C15 = Tax&Fee1

D20 = 123Tax

G45 = DoggyTax

What if you only wanted to search Tax* i.e Tax, Tax&Fee and Tax&Fee1?

Also when you are doing a search in all cells then you have to specify the range. Here is quick example

Option Explicit

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    Dim SearchString As String, FoundAt As String

    On Error GoTo Err

    '~~> The Sheet where the search has to be performed
    Set ws = Worksheets("Sheet1")
    '~~> In All cells
    Set oRange = ws.Cells

    '~~> Search string
    SearchString = "Tax*"

    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    '~~> If search was found
    If Not aCell Is Nothing Then
        Set bCell = aCell
        FoundAt = aCell.Address
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                FoundAt = FoundAt & ", " & aCell.Address
            Else
                ExitLoop = True
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
    End If

    MsgBox "The Search String has been found these locations: " & FoundAt
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

You can find more about FIND() and FINDNEXT() in the below mentioned link.

TOPIC: .Find and .FindNext In Excel VBA

LINK: http://siddharthrout.wordpress.com/2011/07/14/find-and-findnext-in-excel-vba/

Note: If you want to find all instances of "Tax" then you do not need a wild card. All you have to do is use the below as Tony suggested.

LookAt:=xlPart

HTH

Sid

Answer 2

Although my first answer was correct, it was not complete. The following code loops to find each cell containing "tax" and stops when all have been processed.

The direct answer to your question is replace xlWhole by xlPart. However, there are errors that would prevent your code working; for example, you do not define the range on which the Find is to operate. I have added .Cells in front of .Find.

Hope this helps.

Option Explicit
Sub FindAllTax()

  Dim ColCrnt As Long
  Dim ColLast As Long
  Dim FinColumn As Range
  Dim RowCrnt As Long
  Dim RowLast As Long

  RowLast = 0
  ColLast = 0

  With Sheets("Sheet6")

    Set FinColumn = .Cells.Find(What:="Tax", After:=.Cells(1, 1), _
             LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
             SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    Do While True
      If FinColumn Is Nothing Then
        ' No occurrence of "Tax" found
        Exit Do
      End If
      RowCrnt = FinColumn.Row
      ColCrnt = FinColumn.Column
      If RowCrnt < RowLast Or _
         (RowCrnt = RowLast And ColCrnt < ColLast) Then
        ' Current cell is above last cell so have looped after finding
        ' all values.
        Exit Do
      End If
      Debug.Print "Cells(" & RowCrnt & ", " & ColCrnt & ")=" & _
                                             .Cells(RowCrnt, ColCrnt).Value
      RowLast = RowCrnt
      ColLast = ColCrnt
      Set FinColumn = .Cells.FindNext(FinColumn)
    Loop
  End With

  Debug.Print "All cells containing ""tax"" processed"

End Sub