Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display multiple lines on one form (97 SR1)

    Greetings all,
    I am looking for some direction/input on a problem of mine. Every month I have a list of customers, products and amounts. I had set up a rudimentary macro to grab the information and post it into a form to print off per entry. No problems with that. The problem is that I have been asked to group customer purchases to own form, so if a customer has 3 entries, rather than 3 sheets, I need to display the information on successive lines.
    The information is sorted and displayed as Cust#, CustName, Amt, & Product.
    To determine a line entry with the same customer name, this works: If Cells(r, 4) = Cells(r - 1, 4) Then addon
    But then it gets stupid (or should I say my attempts at coding).
    Sub addon()
    i = 8
    While Sheets("Blank").Cells(i, 3) <> ""
    i = i + 1
    Wend
    End Sub
    At which point it goes back to the copy/paste macro and should add successive products and amounts to the lines below it. Of course this does not work - that would be too easy.

    So, when doing a copy/paste macro, how do I get it to check for multiple entries and add them on rather than process them separately?
    Your suggestions please.
    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display multiple lines on one form (97 SR1)

    1. Do you have/know Access? This kind of reporting is much easier in Access then in Excel.

    2. About your macro: you should keep track of where you are in the spreadsheet in a variable. I don't really understand what you want to do, but perhaps you can use something like the following. If not, try to explain what you want to do in more detail; perhaps you can attach a small sample spreadsheet, or a screenshot.

    Dim lngNextRow As Long
    ...
    If Cells(r, 4) = Cells(r - 1, 4) Then
    lngNextRow = lngNextRow + 1
    Sheets("Blank").Cells(lngNextRow, ?) = ?
    End If

  3. #3
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display multiple lines on one form (97 SR1)

    In the greater scheme of things, yes, Access would be better but the system (and my skills) are not ready for that transition.
    Thank you for the code. As soon as I saw it - <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> . I knew what had to be done. Then I discovered the complete error in my logic.
    I had the macro copy / paste the information into a form and print it. The premise for this inquiry was to addon additional lines - which does work.
    However, since each page is set to print at the completion of the cycle, it would print the first customer line, then it would print the next line . Then (assuming multiple entries for that customer) print another page once each product line was added. Very linear.

    To make a long story short - you idea does work - but my idea of printing a form for each customer doesn't. My whole exercise was to have the data fit the form at hand when I should have been thinking about the data and having the final form fit the data. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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