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.
CodePudding user response:
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