Public Sub Test()
Const FILE_PATH = "C:\Users\***\Desktop\vba\wb2.xlsm"
Const FILE_PATH_STATUS = "C:\Users\***\Desktop\vba\wb1.xlsm"
Dim objWorkbook As Workbook
Dim objCell As Range
Dim SrcRange As Range
Dim find_array() As String
Dim loop_st As Variant
Workbooks("wb1.xlsm").Activate
find_array = GetArray(Worksheets("Status_Sheet").Range("A2:A2000"))
Set objWorkbook = Workbooks.Open(Filename:=FILE_PATH, _
UpdateLinks:=0, ReadOnly:=True)
Workbooks("wb2.xlsm").Activate
For Each loop_st In find_array
Set objCell = ActiveWorkbook.Worksheets("WB2_Sheet"). _
Range("A2:A2000").Find(What:=loop_st)
If IIf(objCell Is Nothing, "", objCell) = "" Then
Else
MsgBox objCell.Value
MsgBox objCell.Address
End If
Next loop_st
Set objCell = Nothing
Set objWorkbook = Nothing
End Sub
---------------------------------------------------------------------
Public Function GetArray(xlRange As Range) As String()
Dim strArray() As String
Dim iCounter As Integer
Dim intCount As Integer
Dim xlCell As Range
iCounter = 0
intCount = xlRange.Cells.Count
ReDim strArray(0 To intCount - 1)
For Each xlCell In xlRange
strArray(iCounter) = xlCell.Value
iCounter = iCounter + 1
Next
GetArray = strArray
End Function