CODEWITHSUNDEEP
arraysvbaexcelvlookup
AP1
AP1

Reputation: 167

VLOOKUP with Array Subscript out of range

I'm trying to do what I think should be a simple formula but it's proving difficult. I'm attempting to set a cell equal to a formula via VBA. Here is the code: 

Dim pivotws_month As Worksheet
Dim departmentArray() As Variant
Dim i As Long
Dim x As Long
Dim lrow As Long

lrow = pivotws_month.Cells(Rows.Count, 2).End(xlUp).Row
ReDim departmentArray(1 to lrow)
departmentArray = pivotws_month.Range("B4:B" & lrow)

i = 4  
For x = 1 to UBound(departmentArray)
   pivotws_month.Cells(i,4).Value = pivotws_month.Cells(i,3) / Application.WorksheetFunction.VLookup(departmentArray(x), pivotws_month.Range("G4:H" & lrow), 2, False)
i = i + 1
Next x

I've debugged my variables and my lrow is correct (297) and the # of items in my array is correct (294). I keep getting a subscript out of range error inside of my For loop and I'm not sure why. Please help, I've been looking around for answer for awhile now.

Upvotes: 0

Views: 190

Answers (1)

user4039065
user4039065

Reputation:

You have created a 2D array; supply the rank in all references.

...

'this Redim is completely unnecessary
'ReDim departmentArray(1 to lrow)

...

with pivotws_month
    i = 4  
    lrow = .Cells(Rows.Count, 2).End(xlUp).Row
    'create a 2-D array of many 'rows' and one 'column
    departmentArray = .Range("B4:B" & lrow).value
    For x = LBound(departmentArray, 1) to UBound(departmentArray, 1)
       .Cells(i,4).Value = .Cells(i,3) / Application.VLookup(departmentArray(x, 1), .Range("G4:H" & lrow), 2, False)
        i = i + 1
    Next x
end with

Loading a variant array from a worksheets range always results in a 2-D array. It doesn't matter whether you are loading from a single row or a single columns. It also discards any Option Base 0 declaration or a ReDim command to a 1-D array prior to the bulk loading code line.

You can test the dimensions of your array after the bulk loading event with the following.

debug.print lbound(departmentArray, 1) & ":" & ubound(departmentArray, 1)
debug.print lbound(departmentArray, 2) & ":" & ubound(departmentArray, 2)

It may help to think of the first rank (e.g. ubound(departmentArray, 1)) as the 'rows' of the array and the second rank (e.g. ubound(departmentArray, 2)) as the 'columns' of the array.

There is no error control on that VLOOKUP. A simple no-match #N/A will throw your code into conniptions. The result of an Application.Vlookup can be thrown into a variant and that variant can be tested with IsError. A further test against the variant equaling zero can be used to avoid #DIV/0! results on the division operation in your code.

Upvotes: 2

Related Questions