Consulting

Results 1 to 6 of 6

Thread: CustomerAccounts !! HELP Control and Logic Loops

  1. #1
    VBAX Regular
    Joined
    Sep 2017
    Posts
    7
    Location

    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.
    Attached Files Attached Files
    Last edited by SamT; 09-17-2017 at 12:45 PM. Reason: Removed Text Formatting

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    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
    Please take the time to read the Forum FAQ

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    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.
    Please take the time to read the Forum FAQ

  4. #4
    VBAX Regular
    Joined
    Sep 2017
    Posts
    7
    Location
    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
    Last edited by Tommy; 09-18-2017 at 10:03 AM. Reason: Wrapped with code tags + took out the unnecessary stuff

  5. #5
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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.

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    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.
    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •