VBA Excel Find / Find Next

[dprayner]

Good morning good people.

I find that what I need MS find is not sufficient for my needs. I want to find a number of text in a Excel cell and make the cell bold.

I can do this with the first one, but I am having trouble do this for each one in the spreadsheet. With the following code I can use it to find and replace, but I want the user to have control and search for the first occurrance (and bold it) and then either click the find button again or another command button (find next) to go to the second occurrance (3rd, 4th, 5th, etc.).

Any suggestions? DAVE

Private Sub cmdFind_Click()
Dim Counter As Integer
Private Sub cmdFind_Click()
With Worksheets(1).Range("A12000")
'Find
Set c = .Find(txtFind.Text, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address

'Replace
Do
c.Rows.Select
Selection.Font.Bold = True
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Else
MsgBox "Sorry " & txtFind.Text & " was not found."
End If
End With
End Sub

 

[SkipVought]

How about an InputBox for a user response?



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[firefytr]

Or a message box..

Option Explicit

Private Sub CommandButton1_Click()
    Dim c As Range, firstAddress As String, lngCount As Long, Msg As VbMsgBoxResult
    With Worksheets(1).Range("A1:D2000")
       'Find
        Set c = .Find(txtFind.Text, LookIn:=xlValues)
         
        If Not c Is Nothing Then
            firstAddress = c.Address
            'Replace
            Do
                lngCount = lngCount + 1
                Msg = MsgBox("Bold row " & c.Row & "?", vbYesNo, "#" & lngCount)
                If Msg = vbNo Then Exit Do
                c.Rows.Font.Bold = True
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        Else
            MsgBox "Sorry " & txtFind.Text & " was not found."
        End If
    End With
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[dprayner]

Thank you.