I need some help printing an array from a set of values on a worksheet.
Hi,
I have this code that I need to complete, and I am having some trouble understanding how arrays work. I need my two arrays to be populated with the values from two columns on a worksheet, but I can't figure out to properly populate the arrays. Thanks in advance for any help.
Code:
Sub HighSpenderList()
'Declare variables
Dim i As Integer
Dim arrNames() As Long
Dim arrSpent() As Currency
Dim NumberofRows As Integer
'Clear out existing records in columns D and E.
'Start in D4 and E4.
Range("E4", Columns("E").SpecialCells(xlCellTypeLastCell)).Delete
Range("D4", Columns("E").SpecialCells(xlCellTypeLastCell)).Delete
'POPULATE THE ARRAYS
NumberofRows = Cells(65536, 1).End(xlUp).Row
'Set up a For loop ranging from i = 1 to the number of customers.
For i = 1 To NumberofRows
If arrNames(i) = Range("A3:AA").Offset(i, 1).Value > 499 Then
ReDim Preserve arrNames(1 To i)
i = i + 1
End If
Next i
'With cell A3 as your point of reference, check whether
'the value of .Offset(i, 1) is at least 500. If it is,
'then ReDim Preserve your two arrays to add another element to each,
'and also write the ith customer name to the end of the
'names array and the ith amount spent to the end of the
'amount spent array.
'NOTE: the ith customer name won't necessarily be the
'ith element in your array. For re-dimensioning and keeping
'track of what array element you are on, it
'will be helpful to set some integer variable that you manually
'increment every time you find a high spender.
End Sub
