PDA

View Full Version : I need some help printing an array from a set of values on a worksheet.



updown
12-11-2015, 05:46 PM
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.


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

p45cal
12-12-2015, 08:26 AM
You may already have an answer at http://www.mrexcel.com/forum/excel-questions/908348-i-need-some-help-printing-array-set-values-worksheet.html

Aussiebear
12-13-2015, 03:07 PM
Please do not cross post across multiple forums. The VBA community doesn't represent that big of a percentage of the world's population and we often are members of multiple forums. People who cross post issues without following forum rules soon become regarded as persons to ignore, and I'm hopeful here that you don't want to go down that track.