Fuzzy Search of a String in Cell

Ed S.

Board Regular
Joined
Mar 26, 2002
Messages
90
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
It's lunchtime, so I took the liberty of creating some code that I'd like to suggest.

<pre>
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</pre>

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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top