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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,051
Messages
5,857,047
Members
431,850
Latest member
mbhakt

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