CustomerAccounts !! HELP Control and Logic Loops

[Lesley]

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]

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]

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]

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]

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]

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.