Unable to get FindNext property of range class

user_12673297 0
Viewed 127

I have this code, it just takes 3 words from user, looks for words in b,c and d and changes value to XXXXXXXXXXXXX.

The problem is that I get

unable to get findnext property of range class

I need this code to do multiple searches with different words and replace values without closing/resetting excel. And if it is possible, when it does not fiind value in b, should go and execute the next piece of code.

The code looks for words in paragraphs.

Could someone please point me in the right direction?

Private Sub CommandButton1_Click()

Dim x As String
Dim y As String
Dim z As String

x = InputBox("enter word")
y = InputBox("enter word")
z = InputBox("enter word")





With Worksheets(1).Range("b2:b1000")
    Set b = Cells.Find(x)
    If Not b Is Nothing Then
        firstAddress = b.Address
        Do

             b.Value = "XXXXXXXXXXXXX"
             Set b = .FindNext(b)
        Loop While Not b Is Nothing
    End If
End With


With Worksheets(1).Range("c2:c1000")
    Set c = Cells.Find(y)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = "XXXXXXXXXXXXX"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With


With Worksheets(1).Range("d2:d1000")
    Set d = Cells.Find(z)
    If Not d Is Nothing Then
        firstAddress = d.Address
        Do
            d.Value = "XXXXXXXXXXXXX"
            Set d = .FindNext(d)
        Loop While Not d Is Nothing
    End If
End With

End Sub
vba excel
2 Answers

Tested this code and if I have a cell with : "Nombre del producto: wok antiadherente verde // Material: Metal // tama̱o: vista general del dibujo // Capacidad: 2500 / 4200ml " and I am looking for the word "verde", it only replaces the word and the rest of the text is still there. РAndrew Cm 9 mins ago 

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim firstWord As String
    Dim secondWord As String
    Dim thirdWord As String

    On Error GoTo Whoa

    '~~> Change this to the relevant worksheet
    Set ws = Sheet1

    firstWord = InputBox("enter word")
    secondWord = InputBox("enter word")
    thirdWord = InputBox("enter word")

    With ws
        If firstWord <> "" Then ReplaceText ws.Range("B2:B1000"), firstWord
        If secondWord <> "" Then ReplaceText ws.Range("C2:C1000"), secondWord
        If thirdWord <> "" Then ReplaceText ws.Range("D2:D1000"), thirdWord
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

'~~> Identify the cell where the value is found and
'~~> Store it in a range. at the end, directly
'~~> replace all values
Private Sub ReplaceText(rng As Range, txt As String)
    Dim aCell As Range, bCell As Range
    Dim rngFound As Range

    Set aCell = rng.Find(What:=txt, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        Set bCell = aCell
        Set rngFound = aCell

        Do
            Set aCell = rng.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                Set rngFound = Union(rngFound, aCell)
            Else
                Exit Do
            End If
        Loop
    End If

    If Not rngFound Is Nothing Then
        rngFound.Value = "XXXXXXXXXXXXX"
    End If
End Sub
user_11405790

Your case would be easier to solve if VBA/VB6 supported Try/Catch. However, it does not. So we revert to the closest thing possible: On Error Resume Next and On Error Goto 0.

On Error Resume Next will allow errors to happen, and will populate the global object Err with the number of the error in VB library. Just set it to 0 before testing for new errors because On Error Resume Next does not clear its value.

...

Do
  Err = 0   'reset it /ignore any old value
  b.Value = "XXXXXXXXXXXXX"
  On Error Resume Next
  Set b = .FindNext(b)
  On Error Goto 0
Loop While Not b Is Nothing And Err = 0   ' stay in the loop if there are no errors 
...
user_7361720
Related