# CustomerAccounts !! HELP Control and Logic Loops

• 09-17-2017, 11:50 AM
Lesley
CustomerAccounts !! HELP Control and Logic Loops
We are in chapter 7 - Control logic and loops and I cannot figure out what is wrong with my code for example 20

The file Customer Accounts.xlsx contains account information on a company’s customers. For each customer listed by customer ID, the Data worksheet has the amount the customer has purchased during the current year and the amount the customer has paid on these purchases so far.

For example, the first customer purchased an amount worth \$2466 and has paid up in full. In contrast, the second customer purchased an amount worth \$1494 and has paid only \$598 of this.

Write a sub to create a list on the Results worksheet of all customers who still owe more than \$1000. (It should first clear the contents of any previous list on this worksheet.) The list should show customer IDs and the amounts owed. This sub should work even if the data change, including the possibility of more or fewer customer accounts.

I have attached the file I am working with, please tell me what I am doing wrong! I have messed with this code and I am completely lost.
• 09-17-2017, 12:47 PM
SamT
The Code in Question
Code:

```Option Explicit Dim wsData As Worksheet Dim wsResults As Worksheet Dim rngData As Range Dim custID As String Dim amtOwed As Integer Dim refData As Range Dim refAns As Range Dim noAns As Integer Sub AmountOwed() With wsData     Set rngData = .Range("A4", .Range("A10000").End(xlUp)) End With For Each refData In rngData.Cells     amtOwed = refData.Offset(0, 1) - refData.Offset(0, 2)     If amtOwed > 1000 Then         custID = refData         refAns = custID         refAns.Offset(0, 1) = amtOwed         Set refAns = refAns.Offset(1, 0)         noAns = noAns + 1     End If Next With wsResults     .Range("A3").Resize(noAns, 2).Sort .Range("B4"), xlDescending, .Range("A4"), , xlAscending, , , xlYes End With End Sub```
• 09-17-2017, 12:58 PM
SamT
First Error location, starting at the top of the Code
Code:

`With wsData`
Since this is Homework our help will be to help you figure out the errors, not to correct them.

In the VBA Editor (VBIDE), Tools Menu >> Options >> Editor Tab, check every box in the Code Settings Frame.,

Then, with the Debug Menu >> Compile. Recompile after correcting each error until it successfully Compiles.

You have none, but if you did, disable all error handling code until the code Compiles.
• 09-18-2017, 09:05 AM
Lesley
Code:

```Option Explicit Dim wsData As Worksheet Dim wsResults As Worksheet Dim rngData As Range Dim custID As String Dim amtOwed As Integer Dim refData As Range Dim refAns As Range Dim noAns As Integer Sub AmountOwed() With wsData   rngData = .Range("A4", .Range("A10000").End(xlUp)) -  I still do not know what I am doing wrong - this one is showing an error End With For Each refData In rngData.Cells     amtOwed = refData.Offset(0, 1) - refData.Offset(0, 2)     If amtOwed > 1000 Then         custID = refData         refAns = custID         refAns.Offset(0, 1) = amtOwed         Set refAns = refAns.Offset(1, 0)         noAns = noAns + 1     End If Next With wsResults     .Range("A3").Resize(noAns, 2).Sort .Range("B4"), xlDescending, .Range("A4"), , xlAscending, , , xlYes End With End Sub```
• 09-18-2017, 10:05 AM
Tommy
Hi Lesley,

I haven't downloaded your excel book but your code does not show you have set the wsData to a worksheet. You have defined it as such, I just don't see where it is equal to a sheet with data.
• 09-18-2017, 01:16 PM
SamT
Quote:

I still Do Not know what I am doing wrong - this one Is showing an error
I'll bet that it is highlighted yellow. Yellow means it is the next line to be run.

Did you not read my troubleshooting directions? Did you not read where I told you the line with the first error?

Now, Tommy, has explained what that error is.

Until you fix that error, the VBIDE won't proceed.