how to loop a vba code

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
I have the following piece of code which I'd used in a form. However, with this I could only do entry in one worksheet at a time.

The code was,

Sub update_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(CStr(Worksheets("Data List").Cells(4, 18).Value))

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
............................

Now, I need to enter the same data inputed into multiple worksheets. Hence, I made the following changes.

Sub update_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim rng1, rng2 As Integer

Set rng1 = Worksheets("Data List").Cells(4, 18).Value
Set rng2 = Worksheets("Data List").Cells(9, 18).Value

Set ws = Worksheets(CStr(Worksheets("Data List").Cells(4, 18).Value))

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
............................

I have all worksheets named as numbers, i.e. 1, 2, 3, 4, etc.

On one worksheet named "Data List" I get the Start Date and End Date.
Obviously the cell value is (4, 18) and (9, 18) is the reference point.


I now wish to run the above code through a loop,
i.e. if I enter Start Date as 5 and End Date as 9, then I need to make the same entry in worksheets 5, 6, 7, 8, 9.

or if the start date is 21 and end date is 30 then I need to make the same data entry in worksheets named, 21, 22, 23, 24, 25, 25

The second piece of code is still been developed, hence not complete.

Please help.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,176,626
Messages
5,904,103
Members
435,073
Latest member
McKay_S

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
Top