Display multiple lines on one form (97 SR1)

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

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 – . 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.

Reply | Quote