Fuzzy Search of a String in Cell

[Ed S.]

First, I wish to extend my gratitude to all of you who particpate in this forum. Thanks!

I am strugling on developing a macro to read through a worksheet's column (cell by cell), find the literal "Total", and then select the cell.

Specifically, I am unable to develop the code that performs the fuzzy search - the literal is not exlusive, but has other data in the liteal.

Here is my feeble attempt:

Sub FindString()

' Position to first cell in Spread sheet.
Range("A3").Select
Dim wks As Worksheet
Set wks = ActiveSheet

' Last cell is always a total, so color it.
Selection.End(xlDown).Select
Selection.Interior.ColorIndex = 35

' Start reading back to top work sheet.
r = ActiveCell.Row
For r = r To 3 Step -1
x = ActiveCell.Row
y = ActiveCell.Column

*** this is where I need help.
If Cells = "Total" Then
***
wks.Cells(x, y).Select
End If
Next r

End Sub

[Mark O'Brien]

It's lunchtime, so I took the liberty of creating some code that I'd like to suggest.

Public Sub FindString()





Dim wks As Worksheet

Dim rngFull As Range

Dim rngLast As Range

Dim rngTotal As Range



Set wks = ActiveSheet



'Get last Cell

Set rngLast = wks.Range("A3").End(xlDown)

'Get used range

Set rngFull = wks.Range(Range("A3"), rngLast)



' Last cell is always a total, so color it.

rngLast.Interior.ColorIndex = 35

 

' Find "Total" anywhere in Column A range

Set rngTotal = rngFull.Find(What:="Total", Lookat:=xlPart)



' If found select the cell with Total in it.

If Not rngTotal Is Nothing Then

  rngTotal.Select

Else

  MsgBox "Could not find Total."

End If



End Sub

This finds the whole range that we're using in Column A.
It sets the cell colour to that green colour you want.
Then it searches the range in column A for a cell that contains "Total" in it, either by itself or as part of a value like "MyTotal".
It then selects that cell.

I hope this is what you were trying to achieve.

If not, just repost.

HTH

[Cosmos75]

Ed S.,

Why are you trying to select the data next to a column with the text total in it? How about the last cell that's always a total, do you want that selected as well?

Do you want to copy the cells with the text "total" an column AND the values in column B next to cells with the text "total" in them and paste it somewhere else?

It would help to know what exactly you want to do with it, it could change the approadh one could take with the macro.