Populate array dynamimically from a different Excel workbook

[Bruce Tjosvold]

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

[gmk]

Bruce Tjosvold

Change these lines ...

Please Login or Register  to view this content.

In your testing

Please Login or Register  to view this content.

hth

gmk

[Bruce Tjosvold]

gmk,

This is the value of Tester_IDs after making the suggested change, see attachment.

Thanks
Bruce

Array_value.jpg

[gmk]

Bruce Tjosvold

Use this construction. Change the sheet numbers where I have commented. I have tested the code using my own file paths.

Please Login or Register  to view this content.

[Bruce Tjosvold]

gmk,

Thanks for the help. I'm an experienced SAP ABAP developer. But this VBA is brand new to me, as is any Windows programming.

I have been tasked with modifying a very complex VBA application to replace numerous HARD CODED values with parameters that will be in several different EXCEL file.

You have started my on my way. I appreciate the help.

Bruce

[gmk]

Bruce

Pleased to be of assistance.

Please mark the the thread as solved if it is the answer you require and if you feel it is warrented then add to my reputation by clicking on the star at the bottom of my reply.

gmk