When assigning a variable to an array, why am I getting an array subscript out of range error?

Question

I am trying to loop through a ledger of transactions and tag costs that correlate to certain account codes. For example, account code 123 would be tagged as a 'Hardware/Software' cost. I first produced code that combed through the ledger via reading each cell (a very lazy solution). This process took ~12-17 minutes to tag all costs in the ledger. I'm now trying to implement an array solution to tag all costs by reading the account codes through one array and then tag the costs in another array if it meets the requirements of an If/Then Statement.

The code below is looping through account codes and tagging 'misc' costs, 'hardware' costs, and 'not expense' costs.

How can I make the code work so that I can go through a series of If/Then statements with the account code to specify the tagging based on what the account code is? I keep getting an error ("array subscript out of range") when I try to assign the 'Not Expense' tag in the second If/Then statement in the code below:

Sub arrayTest()
Dim arTesting() As Variant
Dim arTag1(1 To 1550) As Variant 'this is just a test range
Dim arTag2(1 To 1550) As Variant 'this is just a test range
Dim rng, cell As Range
Dim HWSWTag, miscTag, notExpenseTag As String
Dim x As Integer
Set rng = Range("G2:G1551")

miscTag = "Misc"
HWSWTag = "HW/SW"
notExpenseTag = "Not Expense"

x = 1
'Read in the range of account codes
For Each cell In rng
    ReDim Preserve arTesting(x)
    arTesting(x) = cell.Value
    x = x + 1
    Next cell

'Now tag the costs to arTag1 and arTag2    
Dim i As Long
i = 1
For i = LBound(arTesting) To UBound(arTesting)
    If arTesting(i) = 716 Then
                arTag1(i) = miscTag
                arTag2(i) = HWSWTag
    End If

    If arTesting(i) = 182 Or 160 Or 250 Or 258 Or 180 Then
        arTag1(i) = notExpenseTag 'This is where I get the error 
    End If

'Debug.Print arTesting(i)

Next i

'Now paste the tags into the worksheet

Range("AL2:AL1551").Value = WorksheetFunction.Transpose(arTag1)
Range("AM2:AM1551").Value = WorksheetFunction.Transpose(arTag2)

End Sub

I expect the output to tag all costs with account code '716' as 'misc' and 'HW/SW', and tag costs with account code '182', '160', '250', '258', '180' as 'Not Expense'

I hope this code helps as it is a small part of the overall code that combs through a bunch of other account codes.

There is no reason to read in the elements cell by cell, just use arTesting = rng.Value. This will yield a 2-dimensional array (even for a single column, in which case the second index is always 1) but this is much faster than 1500 + ReDim Preserves (especially because the ReDim Preserve isn't required in this case since you could just Dim the array once before you start). — John Coleman
– John Coleman, Commented Apr 3, 2019 at 14:40
Also, If arTesting(i) = 182 Or 160 Or 250 Or 258 Or 180 doesn't mean what you think it means. That isn't shorthand for If arTesting(i) = 182 Or arTesting(i) =160 Or arTesting(i) =250 Or arTesting(i) =258 Or arTesting(i) =180 (which is what I suspect you meant). — John Coleman
– John Coleman, Commented Apr 3, 2019 at 14:47
@JohnColeman That feeling when the mistake was as elementary as forgetting that If arTesting(i) = 182 Or 160 Or 250 Or 258 Or 180 isn't the same as If arTesting(i) = 182 Or arTesting(i) =160 etc. Your solution worked great - thanks so much for your help! — Mithrandir
– Mithrandir, Commented Apr 3, 2019 at 19:50

John Coleman · Accepted Answer · 2019-04-03 15:08:06Z

The following should do what you seem to be trying to do. It makes several changes:

It properly declares 3 string variables rather than 2 variants and 1 string
It reads in the values in 1 line of code
It uses Select Case rather than an If statement with a condition that doesn't mean what you probably thing. x = 1 Or 2 Or 3 means (x = 1) Or 2 Or 3 (which is almost never what you want) rather than the intended x = 1 Or x = 2 Or x = 3

Here is the code:

Sub arrayTest()
    Dim arTesting() As Variant
    Dim arTag1(1 To 1550, 1 To 1) As Variant 'this is just a test range
    Dim arTag2(1 To 1550, 1 To 1) As Variant 'this is just a test range
    Dim rng As Range, cell As Range
    Dim HWSWTag As String, miscTag As String, notExpenseTag As String
    Dim i As Long

    Set rng = Range("G2:G1551")

    miscTag = "Misc"
    HWSWTag = "HW/SW"
    notExpenseTag = "Not Expense"

    arTesting = rng.Value

    For i = LBound(arTesting,1) To UBound(arTesting,1)
        Select Case arTesting(i,1)
            Case 716:
                arTag1(i, 1) = miscTag
                arTag2(i, 1) = HWSWTag
            Case 182, 160, 250, 258, 180:
                arTag1(i, 1) = notExpenseTag
        End Select
    Next i

    Range("AL2:AL1551").Value = arTag1
    Range("AM2:AM1551").Value = arTag2
End Sub

Collectives™ on Stack Overflow

When assigning a variable to an array, why am I getting an array subscript out of range error?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related