help getting my first macro to loop

music_man

New Member
Joined
Aug 25, 2002
Messages
6
Hi guys

My first post - my first excel 2000 macro.

I have been reading and searching for a while now, but have had no luck, getting desparate now, can anyone help me complete my "simple" macro?

I thought by recording the bulk of the code, I would then be able to edit in vba, to add the finishing touches.

What I have is two worksheets, the first is split into a number of diffirent sections of data.
Each section is identical, in that, it has a small table of static data, with a nos of rows of data (which use the table above for calculation purposes) below it.

I have tried recording a "relative" macro.

From the starting position in the table, the macro goes down to the first cell in the row of data, then names the cell, before working across the row, naming the cells, until it reaches the end of the row. It then prints a form on worksheet 2 which has formulae referencing the named cells on worksheet 1. After printing the form, the macro returns to the first cell in the next row of worksheet 1.

My problem is, how do I get the macro to repeat the instructions until such time the first cell in the row does not contain any data, when it would then stop.
I find the "looping" code confusing.
I also thought by recording the macro as "relative", by starting from the same relevant cell in each of the diffirent sections that it would print the data from the rows within each diffirent section.
Surprise surprise, when testing each section, it just prints the same cells from the first section.
I have copied below, a few of the lines of code from the start, and the end of the macro, because I don't think I have probably explained my problem very well.

I hope someone out there can point me in the right direction.

Great board by the way ( no i'm not sucking up )

Any tips on good books on vba, I'm in UK.

cheers
Ian
Scotland

. . .
Application.ScreenUpdating = False
ActiveCell.Offset(12, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="='Sheet 1'!R19C1"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Annual_Basic_Pay", RefersToR1C1:= _
"='Sheet 1'!R19C5"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Hypo_Tax", RefersToR1C1:="='Sheet 1'!R19C8"
'ActiveWindow.SmallScroll ToRight:=7
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Annual_Hypo_Net", RefersToR1C1:= _
"='Sheet 1'!R19C9"
. . . . . . . . . . .

ActiveCell.Offset(0, 3).Range("A1").Select
ActiveWorkbook.Names.Add Name:="Car_Euro", RefersToR1C1:= _
"='Sheet 1'!R19C55"
Sheets("Sheet 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet 1").Select
ActiveCell.Offset(1, -54).Range("A1").Select
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try this to make your loop :

<pre>
Sub MyFirstLoop()
Do Until IsEmpty(ActiveCell)
'Put your code here
MsgBox ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
End Sub
</pre>
 
Upvote 0
Thanks Bruno,

I will try this when I get home this evening.

Not all the cells on each row that are being named contain data, would this not stop the macro before it reached an empty cell in col "a"

Thanks for taking time to help out a novice.

Ian
Scotland
 
Upvote 0
Music_man,
Yes, my macro will stop on the first empty cell...
Maybe you can use something like this to select your cells :
<pre> Range("A3").select
'some code here
Range("B17").select
'some code here
etc. </pre>
 
Upvote 0
Did not understand your last suggestion, so I tried your first code, after filling all blank cells with 0.00's.
The macro ran through all the rows required and stopped at the first blank cell.
Brilliant!!! - just what I wanted, but when I looked at the printed forms, they all had the same data from the first row of data (row 19).
When I recorded the macro I selected it to use relative cells.
Looking at the code it would suggest that the named cells refer to specific cells in row 19 only.

Have I done something wrong, have I used an incorrect method to name active cell?

Any ideas or help would be gratefully appreciated as I am desperate to have this working by the weekend.

Maybe this is all to confusing for me, but I do not want to give up.

cheers
Ian
in Scotland

Do Until IsEmpty(ActiveCell)
ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="='Sheet 1'!R19C1"
 
Upvote 0
Music_man,
Now I think I understand your problem :

When you use
<pre>ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="='Sheet 1'!R19C1"</pre>
the !R19C1 ALWAYS refers to cell A19 !
When use you 'ACTIVECELL' instead, it will refers to... the active cell :wink:

For example :
***NOT TESTED ***
<pre>
Sub MyFirstLoop()
'Add names in a loop
Do Until IsEmpty(ActiveCell)
ActiveWorkbook.Names.Add Name:="Name", RefersTo:=ActiveCell
'jump to next cell
ActiveCell.Offset(0,4).Select
ActiveWorkbook.Names.Add Name:="Annual_Basic_Pay, RefersTo:=ActiveCell
'jump to next cell
'...
Loop
'Print the sheet
Sheets("Sheet 2").PrintOut Copies:=1, Collate:=True
'Select the first row of the next section
ActiveCell.Offset(2,-54).Select
End Sub
</pre>

Edit the code here and there will you ?
Regards,
 
Upvote 0
Bruno

Many thanks for this.

I shall try today.

Will let you know how I get on.

Cheers
Ian
in Scotland
 
Upvote 0
Bruno

I have edited the code to what you advised and tested.
It now works perfect and I am really pleased.
I can't thank you enough for your help. Its been brilliant for me.
This will save me lots of time each month.

As this runs only one section of the worksheet at a time, would the following code
work for printing all the sections (each for a diffirent country)if I needed to print all at once.
I am sure it won't be the best way, but would do until I gain more experience.

Any recommendations for a book to buy, for a beginer to vba?
"dummies programming for excel 2000"?

Many thanks once again. . .

cheers
Ian


Sub
Application.Goto Reference:="France"
Application.Run_
"New Macro amended.xls My_Fourth_Macro"
Application.Goto Reference:="indonesia"
Application.Run _
"New Macro amended.xls My_Fourth_Macro"
Application.Goto Reference:="holland"
. . .
Sub
 
Upvote 0
Ian,
''As this runs only one section of the worksheet at a time, would the following code work for printing all the sections (each for a diffirent country)if I needed to print all at once. ''
Move the print section within the loop :
<pre>
'jump to next cell
'...
'Print the sheet
Sheets("Sheet 2").PrintOut Copies:=1, Collate:=True
'Select the first row of the next section
ActiveCell.Offset(2,-54).Select
Loop
End Sub</pre>


''Any recommendations for a book to buy, for a beginer to vba? "dummies programming for excel 2000"? ''
I'm living in Belgium (Flanders) and I can't help you on this... Maybe one of the other forum members can help you ?

''Many thanks once again. . . ''
You're welcome
 
Upvote 0
Bruno.

I am going to try and run the macro on the whole worksheet today.
I will try what you suggest.

Many thanks
Ian
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top