Dynamic Array with If Statement

Question

Hello, I am having trouble filtering the values that go into an array. In the excel file, I have a worksheet that in one column has a list of spenders and in the next column has the amount that they have spent. I need my subroutine to create an two arrays; one that contains the names of all spenders who have spent over $500 and one that contains the amount spent of all spenders who have spent over $500. I then need to write these arrays to the worksheet, making sure that the names and the values correspond to each other. So far I have an array that contains all of the spenders but I am having trouble making an if statement that will filter the values that go into the array. Thanks! 

Sub HighSpenderList()

'Declare variables

Dim HighSpenders() As String

Dim AmtSpent() As Currency

Dim nSpenders As Integer

Dim i As Integer

Dim j As Integer

'Clear out existing records in columns D and E.

'Start in D4 and E4.

Range("D4", Range("D4").End(xlDown)).Clear

Range("E4", Range("E4").End(xlDown)).Clear

'Populate the arrays

With Sheet1.Range("A4")

        nSpenders = Range(.Offset(0, 0), .End(xlDown)).Rows.Count

End With

'S

t up a For loop ranging from i = 1 to the number of customers.

ReDim HighSpenders(nSpenders)

ReDim AmtSpent(nSpenders)

For i = 1 To nSpenders

        HighSpenders(i) = Range("A3").Offset(i, 0).Value

        AmtSpent(i) = Range("A3").Offset(i, 1).Value

    Next

'Check to see if the amount spent is over 500. If it is,

'then ReDim Preserve the two arrays to add another element to each,

'and also write the ith customer name to the end of the

'HighSpenders array and the ith amount spent to the end of the

'AmtSpent array.

If Range("A3").Offset(i, 1).Value >= 500 Then

'Write arrays to worksheet(Starting in D4 and E4)

End Sub

Answer 1

Maybe I am misinterpreting your description but it is not making sense to me. You have 2 columns (D and E) for the output and you are creating 2 arrays each with 2 columns (4 columns of output).

I am thinking that you should be able to use AutoFilter to extract the data and then copy the visible cells to the output area.

I realize that you probably have sensitive data that you cannot share but can you make a copy of the workbook and replace peoples names with random data (simply A, B,C D etc will be OK) and just a sample of the data that is large enough to work with and then upload to OneDrive.

Guidelines to upload a workbook on OneDrive:

1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Do NOT fill in the form; Select "Get a Link" on the left side.
10. Click the button "Create a Link"
11. Click in the box where the link is created and it will highlight.
12. Copy the link and paste into your reply on this forum.