Apart from changing xlFormulas2, I recommend that you write the code in such a way that it does NOT reference the active sheet by default (because there is no qualifier for Range, Cells, etc.). It happens that the code will not work then.
Nor can it be optimistically assumed that the searched phrase will be found always. The day will come when she will not be found. Such a case should be programmed. I only gave a message and an exit from the procedure, but this is not always the correct approach to the problem, especially when the previous part of the macro has already made some changes to the workbook. I will not mention
the lack of declarations of the variables used.
Corrected code below
Sub Check_PO()
'
' Check_PO Macro
Dim rngFound As Range
With Worksheets("PO_Check_Report")
.Range("B3:B4").ClearContents
.Rows("8:4000").ClearContents
End With
MasterListLastRow = Worksheets("Master_List").Range("B3").End(xlDown).Row
With Worksheets("PO_Check_Report")
NotFoundNextRow = .Range("A6").End(xlDown).Row + 1
FoundDifNextRow = .Range("D6").End(xlDown).Row + 1
FoundOKNextRow = .Range("G6").End(xlDown).Row + 1
End With
With Worksheets("PO_text")
LastRow = .Range("A24000").End(xlUp).Row
' Find PO Number in text
Set rngFound = .Cells.Find(What:="PO Number", After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rngFound Is Nothing Then
With rngFound.Offset(1, 0)
Purch_No = InStr(1, .Value, "/", vbTextCompare)
PO_Number = Left(.Value, Purch_No - 2)
End With
Else
MsgBox "Phrase 'PO Number' not found!", vbExclamation
Stop
Exit Sub
End If
End With
With Sheets("PO_Check_Report")
.Range("B3").Value = PO_Number
.Range("B4").Value = Now()
End With
...
Artik
Bookmarks