I had a friend have a go at the code and here is his version. However I still get a runtime error 9 subscript out of range message.
I have stepped through the code and it all runs smoothly, checking through the main array etc. UNTIL it reaches a match. As soon as VBA goes from this line:
ReDim Preserve varNewArray(2, UBound(varNewArray, 2) + 1)
to this line
varNewArray(0, UBound(varNewArray)) = varMainArray(i, 1)
it gets the error message. Why? 
Option Explicit
Private wbSource As Workbook
Private sWbSheet As String
Private varMainArray As Variant
Private Sub UserForm_Initialize()
Call EmployeeList_Populate
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub OKButton_Click()
If ListBox1.ListIndex = -1 Then
MsgBox "No employee selected!"
Unload Me
Exit Sub
End If
MsgBox ListBox1.Value 'for test purposes
Unload Me
End Sub
'
'-----form macros above, private macros below--------------------------------
'
Private Sub testme()
Me.Show
End Sub
Private Sub EmployeeList_Populate()
int_employer_id = 3 'for testing
sWbSheet = "employee10"
' turn screen updating off (prevent the user from seeing the source workbook being opened)
' open the source workbook as ReadOnly
Application.ScreenUpdating = False
Set wbSource = Workbooks.Open(sServerFolderProgram & sServerFileDatabaseExcel, False, True)
' create array from worksheet range
Dim intMaxRow As Integer
Dim intMaxCol As Integer
With wbSource
With .Worksheets(sWbSheet)
.Activate
intMaxRow = .Range("A1").End(xlDown).Row 'there are no blanks in row 1 data
intMaxCol = .Range("A1").End(xlToRight).Column 'there are no blanks in column A data
varMainArray = .Range(Worksheets(sWbSheet).Cells(2, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A2 (to avoid header)
End With
.Close ' close the source workbook without saving changes
Set wbSource = Nothing
End With
' build new array
Dim varNewArray() As Variant
ReDim varNewArray(2, 0)
Dim i As Integer
For i = LBound(varMainArray) To UBound(varMainArray)
If varMainArray(i, 2) = int_employer_id Then
'Help NEEDED!
'array is expected to be 5 columns wide by variable rows deep
'I need to check if column 2 (B) value is = int_employer id.
'If yes then add values from column 1 (A), column 3 & 4 (C & D) to new array
ReDim Preserve varNewArray(2, UBound(varNewArray, 2) + 1)
varNewArray(0, UBound(varNewArray)) = varMainArray(i, 1)
varNewArray(1, UBound(varNewArray)) = varMainArray(i, 3)
varNewArray(2, UBound(varNewArray)) = varMainArray(i, 4)
End If
Next i
' populate listbox
Me.ListBox1.List = varNewArray
Application.ScreenUpdating = True
End Sub
Bookmarks