Thanks:  0
Likes:  0

# Thread: Fuzzy Search of a String in Cell

1. 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

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

2. 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

3. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•