Find cell value vith loop function

[Marlowwe]

Hey Guys,

I looking for solution when I want to search text in Cell D6 in column D. But the word could be in several cells and I need to add The Next function to search (Search and find next cell with text from cell D6).

Sub Hledat1()


    Dim rfoundCell As Range

    'Searching text from cell D6
    X = Range("D6").Value
    
    ' Search range is column D
    Set rfoundCell = Range("D7:D10000").Find(what:=X, LookIn:=xlValues, lookat:=xlPart)
    
    If Not rfoundCell Is Nothing Then
    
    ' After found text in cell, select the cell and show message
    rfoundCell.Select
    MsgBox "Searched word: " & X & vbCrLf & "was found in cell: " & rfoundCell.Address(0, 0)
    Else
    
        MsgBox "Searched word: " & X & vbCrLf & " was not found"
    
    End If


End Sub

Can you please help me with this ?

THX

 

[Special-K99]

Perhaps

Sub srchword()
Dim i As Integer
Dim j As String
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
j = ""
For i = 7 To lastrow
If Cells(i, 4) = Cells(6, 4) Then
    j = j & Cells(i, 4).Address & " "
End If
Next i
MsgBox "Word " & D6 & " found in cells " & j
End Sub

 

[dmt32]

Hi,

Try

Sub Hledat1()
    
    Dim rfoundCell As Range
    Dim firstaddress As String
    
'Searching text from cell D6
    X = Range("D6").Value
    
' Search range is column D
    Set rfoundCell = Range("D7:D10000").Find(what:=X, LookIn:=xlValues, lookat:=xlPart)
    
    If Not rfoundCell Is Nothing Then
        firstaddress = rfoundCell.Address
' After found text in cell, show message
        Do
            MsgBox "Searched word: " & X & vbCrLf & "was found in cell: " & rfoundCell.Address(0, 0)
            Set rfoundCell = Range("D7:D10000").FindNext(rfoundCell)
        Loop While rfoundCell.Address <> firstaddress
    Else
        
        MsgBox "Searched word: " & X & vbCrLf & " was not found"
        
    End If
    
    
End Sub

you can see working example of Range.FindNext Method in the VBA helpfile.

Dave

 

[Marlowwe]

Perhaps

Sub srchword()
Dim i As Integer
Dim j As String
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
j = ""
For i = 7 To lastrow
If Cells(i, 4) = Cells(6, 4) Then
    j = j & Cells(i, 4).Address & " "
End If
Next i
MsgBox "Word " & D6 & " found in cells " & j
End Sub

Hey thx, But VBA editor gives me a error and Highlight

lastrow = Worksheets("Sheet1").Cells(Rows[COLOR=#ff0000][FONT=arial black].Count[/FONT][/COLOR], "D").End(xlUp).Row

 

[Marlowwe]

Hi,

Try

Sub Hledat1()
    
    Dim rfoundCell As Range
    Dim firstaddress As String

..
    
End Sub

you can see working example of Range.FindNext Method in the VBA helpfile.

Dave

It works Good, but I need to work like this:

1. write a search word
2. Push the button to find results
3. 1st results was found, but it is not what I want -> click next
4. 2nd results was found, but it is not what I want -> click next
5. 3rd results was found, it is what I want -> click OK and the macro end and the cell with right text is selected


Your macro find 1st result, then click ok.. ant this procedure have to be done until last result.

Could you please help me with that ?

 

[Special-K99]

Hey thx, But VBA editor gives me a error and Highlight

lastrow = Worksheets("Sheet1").Cells(Rows[COLOR=#ff0000][FONT=arial black].Count[/FONT][/COLOR], "D").End(xlUp).Row

You must have entered something wrong, this is my standard command to find the last row occupied.
I've used this many times for other routines.
What you/I have pasted in the forum I've copied to the VBA editor and it parses it fine.