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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,141,587
Messages
5,707,258
Members
421,498
Latest member
matinebi

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