Loop Macro with Counter

[Ricky Wilko]

Hi everybody,

I am trying to write a macro that will repeat based on the number of records in a database. The macro itself will just call 2 other macros which are already written and tested.

I can calculate the number of records in the database using a formula and put this in a field called "Max".

The macro needs to start by putting 1 in a field called "Ref" then call the 2 macros (say Macro1 and Macro2) and then add 1 to "Ref" and repeat.

This should all be repeated until "Ref" equals "Max" and then quit.

Hope that is easy to understand.

Thanks for any help.

Rick

[mehmetcik]

Please Login or Register  to view this content.

[Ricky Wilko]

Thanks - that looks pretty easy. What about the part where I need to put 1 in "Ref", run the macros and then increment it by 1 each time until it gets to "Max"

[mehmetcik]

You are using ref as a counter. I always use count.

Please Login or Register  to view this content.

Does all your looping.

[Ricky Wilko]

Sorry - I am either being unclear or stupid.

I need to have a cell in the worksheet which is the record number as this is used to look up data from the database and generate and print a file.

So the macro needs to start this off at 1 and increment it by 1 for each cycle of the macro until it gets to Max.

Thanks

[mehmetcik]

Please Login or Register  to view this content.

[Ricky Wilko]

Thanks for this. Sorry but I don't think this solves my problem. I will try and explain a little better.

I have a workbook consisting of two sheets.

One sheet contains a database consisting of rows of data. I use this data to populate an invoice in the second sheet which i then save as a pdf and print.

So I need to cycle through each record in the database in turn until I get to the last record.

The invoice has a reference field (named "ref") which is used to lookup data from the relevant record in the database. It also has a field named "max" which is the reference for the last record.

I have sub macros to save the pdf and then print it.

So the macro needs to do the following:

1. Go to "ref" and set to zero.
2. Go to "ref" and increment it by 1
3. If "ref" is < "max" then run macro1 and macro 2.
4. Loop back to 2

Thanks

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.