Object variable or With block variable not set error (Find And Replace)

Question

Here Is what i need to do : First , I have Two sheets ("AM Production","PM Production") need to Find String "Pcs" In the each sheet and count the results then Excute macro multiple times depending on that count in both sheets (Every sheet with its own count) So i did the following : - I have Two Macros one counts pcs word in the sheet and the other excute the Second macro with that number.

Sub FindPcs()    
Range("N1").Select
    'Find
           Cells.Find(What:="Pcs", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
            'Found Nothing
            
            
      'Replace
            ActiveCell.Replace What:="Pcs", Replacement:="Done", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        'Copy To Above Cell
        ActiveCell.Range("A1:B1").Select
        Selection.Copy
        ActiveCell.Offset(-1, 0).Range("A1").Select
        ActiveSheet.Paste
     
        ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
End Sub

The Action Macro :

Sub FindMultipleTimes()
Dim x As Integer
x = "=COUNTIF(C[10],""Pcs"")"

For i = 0 To x
Application.Run "PERSONAL.XLSB!FindPcs"
Next i

End Sub

I need to merge the two macros As The main idea is to find pcs in the "AM Production" sheet then execute Sub FindMultipleTimes() in the end when it find nothing it goes to "PM Production" and Repeat the Counting and Executing part .

Note :I tried the Range and If Nothing Method with find but it throws another error object required.

Thanks in Advance.

You need to check the results of your `.Find` before you `.Activate` it. Right now you are trying to do it all on one line. — braX, Dec 05 '21 at 11:19
Does this answer your question? [find if \`find\` method returns \`nothing\` in excel vba](https://stackoverflow.com/questions/29066633/find-if-find-method-returns-nothing-in-excel-vba) — braX, Dec 05 '21 at 11:37

score 1 · Accepted Answer · answered Dec 05 '21 at 14:10

No need to call the macro multiple times, use a Do .. Loop Until loop.

Option Explicit

Sub FindMultipleTimes()
    Dim sht
    For Each sht In Array("AM Production", "PM Production")
        FindPcs Sheets(sht)
    Next        
End Sub

Sub FindPcs(ws As Worksheet)
    
    Dim fnd As Range, n As Long
    Application.ScreenUpdating = False
    With ws
        Set fnd = .Cells.Find(What:="Pcs", After:=.Range("N1"), LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
          
        If Not fnd Is Nothing Then
            Do
                fnd.Replace What:="Pcs", Replacement:="Done", LookAt:=xlPart, _
                MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            
                'Copy To Above Cell
                fnd.Resize(1, 2).Copy fnd.Offset(-1)
                fnd.EntireRow.Delete
                n = n + 1
                Set fnd = .Cells.FindNext
            Loop Until fnd Is Nothing
        End If
    End With
    Application.ScreenUpdating = True
    MsgBox n & " found on " & ws.Name    
End Sub

Object variable or With block variable not set error (Find And Replace)

1 Answers1