Consulting

Results 1 to 6 of 6

Thread: CustomerAccounts !! HELP Control and Logic Loops

  1. #1

    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    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
    5,975
    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 
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    5,975
    Location
    First Error location, starting at the top of the Code
    With wsData 
    
    
    Formatting tags added by mark007
    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
    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 
    
    
    Formatting tags added by mark007
    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
    5,975
    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
  •