Populate array dynamimically from a different Excel workbook

Hello all,

I am attempting to populate an array with a column from a different worksheet. The following is what I cobbled together from a couple other forum postings that I found. The array is not populating correctly. The second workbook seems to be successfully accessed. I expect array Tester_IDs to be populated with the seven entries from the workbook.

As a real newbie, I'm not sure if I'm close or way off.

Thanks
Bruce


Dim LR As Long
Dim Tester_IDs() As Variant
Dim found_it

Dim app As New Excel.Application
Dim book As Excel.Workbook

'Dim user_name as string

user_name = "ANDERSD4"

Set book = app.Workbooks.Open("\\spnas2\spdept\dept\SAP_TECH\S:\Upload Master Templates\JEMASTER\Bruce_test\Tester_IDs.xlsx")

LR = Range("A" & Rows.Count).End(xlUp).Row
Tester_IDs = Range("A1:A" & LR)


At this point, Tester_IDs is not populated.


After populating the array, I want to find out if the content of the variable user_name is in the array. But I can't even execute the second part of the macro until I successfully populate the array.



If IsNumeric(Application.Match(user_name, Tester_IDs, 0)) Then

found_it = "Yes, it is there"
Else
found_it = "No, not found"
End If
'''''''
'''''''

For Each item In Tester_IDs
If IsEmpty(item) Then
Debug.Print "Item " & counter & " in the Tester_IDs_2 is empty"
Exit For
End If
counter = counter + 1
Next

If counter = 0 Then
MsgBox "Tester_IDs is empty"
Else
MsgBox "Tester_IDs count is greater than 0"
End If