PDA

View Full Version : CustomerAccounts !! HELP Control and Logic Loops

Lesley
09-17-2017, 11:50 AM
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.

SamT
09-17-2017, 12:47 PM
The Code in Question
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

SamT
09-17-2017, 12:58 PM
First Error location, starting at the top of the 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.

Lesley
09-18-2017, 09:05 AM
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

Tommy
09-18-2017, 10:05 AM
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.

SamT
09-18-2017, 01:16 PM
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.