VBA Arrays and Loops

Question

I am having trouble with looping through my already defined array to find values that are over 500. It is telling me that my subscript is out of range.

This is what I have so far. Can someone help me to fix the second part, so it will properly loop through the already established arrays.

Sub hishSale()

    Dim customers() As String
    Dim sale() As String
    Dim hiCustomer() As String
    Dim hiSale() As String
    Dim nCustomers As Integer
    Dim i As Integer
    Dim isOver As Integer


    'capture the number of customers and sales and put them in two seperate arrays
    With wsData.Range("A3")
        nCustomers = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
        ReDim customers(nCustomers)
        ReDim sale(nCustomers)
        For i = 1 To nCustomers
            customers(i) = .Offset(i, 0).Value
            sale(i) = .Offset(i, 1).Value
        Next
    End With

'Loop through arrays to find sales over $500

    With Range("A3")
    isOver = 0
        For i = 1 To nCustomers
            If .Offset(i, 1).Value > 500 Then
                isOver = isOver + 1
                hiCustomer(isOver) = .Offset(i, 0).Value
                hiSale(isOver) = .Offset(isOver, 1).Value
            End If
        Next
    End With

    'return results in columns D and E
    With Range("D3")
        For i = 1 To nCustomers
        .Offset(i, 0) = hiCustomer(isOver)
        .Offset(i, 1) = hiSale(isOver)
        Next
    End With

End Sub

To loop array do for lbound(customers()) to ubound(customers()) — findwindow
– findwindow, Commented May 26, 2016 at 19:51
In your final loop maybe you meant to use i to index into hiCustomer and hiSale ? You're using isOver, the value of which is fixed at this point — Tim Williams
– Tim Williams, Commented May 26, 2016 at 20:35
Seems like you didn't like any of these answers. What was wrong with them? This time are you going to give some feedback to pepole who are willing to dedicate time to problems you throw in here? — user3598756
– user3598756, Commented May 26, 2016 at 20:57
The line that has a problem is: With Range("D3") For i = 1 To nCustomers .Offset(i, 0) = hiCustomer(isOver) .Offset(i, 1) = hiSale(isOver) Next End With .Offset(i, 0) = hiCustomer(isOver) --- is giving me an error — th65
– th65, Commented May 26, 2016 at 21:03

Gary Evans · Accepted Answer · 2016-05-26 20:49:06Z

1

I started to write a big description an walk through about your arrays being bigger then needed and how they have a base of zero, while doing so I found the problem was you never dimension hiCustomer OR hiSale.

Change this: -

'Loop through arrays to find sales over $500
With Range("A3")
isOver = 0
    For i = 1 To nCustomers
        If .Offset(i, 1).Value > 500 Then
            isOver = isOver + 1
            hiCustomer(isOver) = .Offset(i, 0).Value
            hiSale(isOver) = .Offset(isOver, 1).Value
        End If
    Next
End With

To this: -

ReDim hiCustomer(0)
ReDim hiSale(0)
With Range("A3")
    For i = 1 To nCustomers
        If .Offset(i, 1).Value > 500 Then
            ReDim Preserve hiCustomer(UBound(hiCustomer, 1) + 1)
            ReDim Preserve hiSale(UBound(hiSale, 1) + 1)
            hiCustomer(UBound(hiCustomer, 1)) = .Offset(i, 0).Value
            hiSale(UBound(hiCustomer, 1)) = .Offset(i, 1).Value
        End If
    Next
End With

And if I am reading it correct the next loop will also need changing to: -

'return results in columns D and E
With Range("D3")
    For i = 1 To UBound(hiCustomer)
        .Offset(i, 0) = hiCustomer(i)
        .Offset(i, 1) = hiSale(i)
    Next
End With

Previously it was looking the customer list and not just the >500 list.

answered May 26, 2016 at 20:49

Gary Evans

1,8904 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

th65 Over a year ago

Thank you! This works. Is there a way you can write the code without using Ubound?

Gary Evans Over a year ago

@th65 yes but it would be longer. Add it is a new question and I'm sure someone will help. I'm curious as to the why.

Collectives™ on Stack Overflow

VBA Arrays and Loops

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related