Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Fuzzy Search of a String in Cell

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



    EMSS

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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