CODEWITHSUNDEEP
excelvba
user2988436
user2988436

Reputation: 1

Find specific text in a cell

I am new to this VBA for excel, anyway I'm trying to write some code that will iterate through the dataset until it comes to a specific text, in my example I'm looking to find the name "FRANKS" so I wrote some code which does that and it works, now I'm trying to figure out how to stop on that cell, copy it and append it to the cell next to it. Long story short, I have a report that runs every week and when certain employees names get split(text to columns) some names don't come out right. So I have to manually fix them. So here I am trying to automate it. Below is the code I have written so far.

      row_number = 0

 Do
 DoEvents
 row_number = row_number + 1
 namedperson = Range("C" & row_number)
 Loop Until namedperson = "FRANKS"
 Columns("C & row_number").Activate

the last line doesn't work but you get the idea of what I'm trying to do. Anyway after I grab this name "FRANKS" the column to the left of it is where I need to append the "FRANKS" to.....

Thanks in advance, Lenny

Upvotes: 0

Views: 7213

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149277

You can use Range.Find Method?

Sub Sample()
    Dim StringToSearch As String
    Dim aCell As Range

    StringToSearch = "FRANK"

    With Sheets("Sheet1").Columns(3) '<~~ Change as applicable
        Set aCell = .Find(What:=StringToSearch, _
                          LookIn:=xlValues, _
                          LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False)
        If Not aCell Is Nothing Then
            MsgBox "Found at " & aCell.Address
        Else
            MsgBox "Nothing found"
        End If
    End With
End Sub

For a partial search for example Frank in frankenstein, change LookAt:=xlWhole to LookAt:=xlPart

and

For Case Sensitive search change MatchCase:=False to MatchCase:=True

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

No need for loops.

Dim i As Long
With Worksheets("Sheet1") ' change to your sheet
    On Error Resume Next
        i = Application.WorksheetFunction.Match("Franks", .Range("C:C"), 0)
    On Error GoTo 0

    If i <> 0 Then
        MsgBox "Franks found at " & .Cells(i, 3).Address(0, 0)
    Else
        MsgBox "Franks not found in Column"
    End If
End With

Upvotes: 2

tha man
tha man

Reputation: 1

Looping until you find a certain value is not a good idea if the value doesn't exist. I would suggest looping through your range, and if you find the value, append it to the cell to the left. You didn't say whether you wanted to stop at the first occurrence of "FRANKS", so my code keeps going in case there are multiple. There are different ways to refer to a cell, but this should give you the idea.

row_count = 7  ' the number of rows in your range
For r = 1 To row_count
    If Cells(r, 3) = "FRANKS" Then
        Cells(r, 2) = Cells(r, 2) & "FRANKS"
    End If
Next r

Upvotes: 0

Related Questions