Efficienct Array Input Error?!?

[DNA]

Hi All,

I could use some advice on this issue. Been combing this over for a couple hours now and I can't seem to find out why it's giving me this error. Perhaps I'm stubborn, as I can probably make this work another way, but I can't help but try this as a more efficient method. I'm trying to input two columns of data from another workbook into this function, compare one array(JobSearchArray) to a known variable(Job) and have it output the data from the corresponding row in the second array(PlexSearchArray).

Some help would be very welcome and much appreciated. Thanks in advance!

Private Function FindMarkerValue(path, file, sheet, Job)
    Dim wb As Workbook
    Dim ArraySize As Integer, irow As Integer
    Dim JobSearchArray As Variant, PlexSearchArray As Variant
        
    Set wb = Workbooks.Open(path & file, True, True)
    ' open the source workbook, read only
    
    With wb.Worksheets(sheet)
        ' read data from the source workbook
        JobSearchArray = .Range("I4", .Range("I65536").End(xlUp))
        PlexSearchArray = .Range("L4", .Range("L65536").End(xlUp))
    End With
    
    ArraySize = UBound(JobSearchArray) - LBound(JobSearchArray) + 1
    
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing  'free memory
        
'I get a "Run-time error '9': Subscript out of range" on the next line.

    For irow = LBound(JobSearchArray) To ArraySize
        If JobSearchArray(irow) = Job Then
            FindMarkerValue = PlexSearchArray(irow)
        End If
    Next irow
End Function

UBound(JobSearchArray)) shows a value of 122. I have tried ReDim on both arrays, but then the error goes to that.

In efforts to debug, I had it output to a text file and got this:
 

z
  768_2  768_2  768-2 ...etc.

Opening this with excel 2003 shows all the data in A1....

I think my problem is with the way I am inputting my data into variants, but can anyone else make this work ?

 

[snowblizz]

Why are you using "to Arraysize" and not "to Ubound(JobSearchArray)"? It seems the problem is an array index that doesn't exist in the array. Too high,or too low.

 

[DNA]

Arraysize also has a value of 122. I tested Ubound(JobSearchArray) per your suggestion and still have the same result.

I'm sure the problem is with the size of the array or the index of elements in it, but I'm not sure how to fix this. Can anyone help?