how to increment by one using do loop until......

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
I have the below mention code which I need to run from a particular worksheet to a particular worksheet in increment of 1. My worksheet names are 1, 2, 3, 4, 5, 6, etc.

I am using Do Loop Until loop to execute the data entry part

I am getting stuck with line ws = ws + 1. Here ws is defined as Worksheets and I need to increment this by one (1). How do I do this? Currently as per below code I'm trying to add 1 which is number to ws which is Worksheets object.


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

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

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

Do

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

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtname.Value
ws.Cells(iRow, 4).Value = Me.txtcode.Value


'clear the data
Me.txtname.Value = ""
Me.txtcode.Value = ""


ws = ws + 1

Loop Until ws = Worksheets(Cstr(rng2))

End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
If you need to loop through every worksheet you would be better off with a for each loop

otherwise set an index counter i which gets incremented by one each time through the loop and refer as:

thisworkbook.sheets(i)
 

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
This won't work because I have worksheets like...

Index, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...till 31

Now, the start date would be the start sheet ,i.e. say 5 and the end date would be the last sheet till which data needs to be updated. i.e. say 8.

Thus, if I use thisworkbook.sheets(i) It will blindly start from xth sheet and go till x+n th sheet.

So, if I want data to be updated from 5 to 8 sheets...thisworkbook.sheets will load data from....4 to 7 because Index sheet is the first sheet.

Kindly advice.
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
try

Code:
For i= rng1 to rng2  'or rng1+1 to rng2+1 cause of index sheet

   with thisworkbook.sheets(i)
         'code here
   end with

next i
 

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
loop

The below mention code only updated the first worksheet, it doesn't updates the same data in the remaining sheets. Can anyone advice on how to make this work. The loop goes through but the data is not inputed, except for the first sheet.

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

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

For kctr = rng1 + 1 To rng2 + 1

With ws

Set ws = ThisWorkbook.Sheets(kctr)

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

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtname.Value

'clear the data
Me.txtname.Value = ""

End With

Next kctr

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,133,460
Messages
5,658,899
Members
418,475
Latest member
ExcelBeginner233

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