Subscript is out of range

Question

I'm having troubles with my code. I'm trying to get data posted from one worksheet to another. I defined two worksheets in my code, and put my sheet range in an array. Then I wanted to create a loop that would post the data from column E from the business sheet in my engagment sheet, if column D has a number of 3 or higher. However, when I run it it says

Subscript is out if range

My code is as followed


Sub Simple_if2()
 Dim shB As Worksheet, shE As Worksheet, lastRB As Long, lastRE As Long
 Dim score As Integer, i As Long, k As Long, arrB  As Variant

'Worksheet definitions
 Set shB = Worksheets("Business")
 Set shE = Worksheets("Engagement Plan (High Priority)")
 
 lastRB = shB.Range("D" & shB.Rows.Count).End(xlUp).Row
 lastRE = shB.Range("E" & shB.Rows.Count).End(xlUp).Row
 arrB = shB.Range("D6:E6" & lastRB & lastRE).Value  'put the shB sheet range in an array
 ReDim arrE(UBound(arrB))      'redim arrE at maximum possible dimension

 
 'Loop for Business worksheet
 For i = 1 To UBound(arrB)
    If arrB(2, i) >= 3 Then
        arrE(k) = arrB(1, i) 'fill arrE only with elements >=3
        k = k + 1
    End If
 Next i
 
 
 ReDim Preserve arrE(k + 1)  'redim the array to keep only the filled elements

 'drop the array content at once:
 shE.Range("B3").Resize(UBound(arrE) + 1, 1).Value = WorksheetFunction.Transpose(arrE)
End Sub

Note: shB.Range("D6:E6" & lastRB & lastRE) is probably the wrong range... Debug.Print "D6:E6" & lastRB & lastRE and see what the result is. — BigBen
I think you have your refs backwards should be arrB(i, 2) and arrB(i, 1) — Scott Craner
@BigBen - It gives me an error saying type mismatch. How would a define my ´arrB´ with the ´Debug.Print´? — Anders Vigen
No, just Debug.Print "D6:E6" & lastRB & lastRE. The result is going to be something like D6:E65555 in the Immediate Window. — BigBen
@BigBen it worked! arrB=shB.Range("D6:E" & lastRB).value worked. Thanks man — Anders Vigen

Anders Vigen Anders Vigen · Accepted Answer · 2021-01-28T14:55:45

Swap the 2 and i and 3 and i: arrB(i, 1) and arrB(i, 2)
Correct the reference for the range before reading its .Value into an array: shB.Range("D6:E" & lastRE).Value

 Dim shB As Worksheet, shE As Worksheet, lastRB As Long, lastRE As Long
 Dim score As Integer, i As Long, k As Long, arrB  As Variant

'Worksheet definitions
 Set shB = Worksheets("Business")
 Set shE = Worksheets("Engagement Plan (High Priority)")

 lastRB = shB.Range("D" & shB.Rows.Count).End(xlUp).Row
 lastRE = shB.Range("E" & shB.Rows.Count).End(xlUp).Row
 arrB = shB.Range("D6:E" & lastRE).Value 'put the shB sheet range in an array
 ReDim arrE(UBound(arrB))      'redim arrE at maximum possible dimension


 'Loop for Business worksheet
 For i = 1 To UBound(arrB)
    If arrB(i, 1) >= 3 Then
        arrE(k) = arrB(i, 2) 'fill arrE only with elements >=3
        k = k + 1
    End If
 Next i


 ReDim Preserve arrE(k + 1)  'redim the array to keep only the filled elements

 'drop the array content at once:
 shE.Range("B3").Resize(UBound(arrE) + 1, 1).Value = WorksheetFunction.Transpose(arrE)
End Sub

Subscript is out of range

1 Answers