Increment in Range of Cells Through VBA

[hrayani]

Hello Guys,

A am using a file which increments CELL L1 when i press the Run Button Which is on the Screen and Stops when i press the STOP BUTTON.

What i want is when i press the RUN BUTTON then a range of cells (for example (L1:L25) should be incremented rather then a single cell i.e. L1

Here is what i have (in a standard module)

Public IsRunning As Boolean
Public KeepGoing As Boolean
Public Sub SolveIt()

Dim xlapp As Excel.Application
Dim ws As Worksheet
Dim UpdateRange As Range

Set xlapp = ActiveWorkbook.Application
Set ws = ActiveSheet
Set UpdateRange = ActiveSheet.Range("L1")

xlapp.DisplayStatusBar = True
IsRunning = True: KeepGoing = True

tests = UpdateRange
UpdateRange = tests

KeepLooping:
Do While KeepGoing = True
tests = tests + 1
UpdateRange = tests
ws.Calculate
Application.StatusBar = "# of iterations: " & tests
DoEvents

Loop

Set UpdateRange = Nothing
Set ws = Nothing

End Sub

And in the sheet code

Private Sub cmdRunIt_Click()
SolveIt
End Sub

Private Sub cmdStop_Click()
KeepGoing = False
End Sub

Awaiting reply,

Humayun

 

[xenou]

These are two untested tries (examples). You should be able to use Offset() to move the range. If I wrote these correctly, the first sub Foo should move down one row at a time, and the second sub Bar should move down to the row below the range.

Sub Foo()

Dim r As Range
Dim ws As Worksheet
Dim i As Long

Set ws = ActiveSheet
Set r = ws.Range("L1:L25")

For i = 1 to 10
    r.Offset(1).Select
    Application.Wait(Now + TimeValue("00:00:01"))
Next i

End Sub

'--------------------------------------------------------

Sub Bar()

Dim r As Range
Dim ws As Worksheet
Dim i As Long

Set ws = ActiveSheet
Set r = ws.Range("L1:L25")

For i = 1 to 10
    r.Offset(r.Rows.Count + 1).Select
    Application.Wait(Now + TimeValue("00:00:01"))
Next i

End Sub

 

[hrayani]

These are two untested tries (examples). You should be able to use Offset() to move the range. If I wrote these correctly, the first sub Foo should move down one row at a time, and the second sub Bar should move down to the row below the range.

Sub Foo()
 
Dim r As Range
Dim ws As Worksheet
Dim i As Long
 
Set ws = ActiveSheet
Set r = ws.Range("L1:L25")
 
For i = 1 to 10
    r.Offset(1).Select
    Application.Wait(Now + TimeValue("00:00:01"))
Next i
 
End Sub
 
'--------------------------------------------------------
 
Sub Bar()
 
Dim r As Range
Dim ws As Worksheet
Dim i As Long
 
Set ws = ActiveSheet
Set r = ws.Range("L1:L25")
 
For i = 1 to 10
    r.Offset(r.Rows.Count + 1).Select
    Application.Wait(Now + TimeValue("00:00:01"))
Next i
 
End Sub

Dear Xenou,

First of all thanks for reply.

I tried the code which you posted on a new worksheet. i opened a new worksheet and pasted the above mentioned code in the standard module and try to run the macro and i got a Compile error Msgbx stating Invalid Outside Procedure ...

Sorry dear i am dumbo in VBA... Pls tell me what to do...

Is there anything like i have to merge the code you provided with the code i am currently using?

Humayun

 

[xenou]

Invalid outside procedure usually means you have something that should be in a sub that's not. Whatever line it errors on - that should be inside a subroutine.

This code is an example of using offset. If you run it by itself you will be able to see how offset works. Then you need to apply that in your code - once you understand how it works you'll probably be able to figure out how.

ξ

 

[hrayani]

Thanks Xenou for your reply,

it was my mistake - there is no error in the code.... i didnot copied the whole code that is why i was getting the error...

dear i know nothing about vba - i dont at all know how to ammend the code or insert or input the offset rule in to get the desired result...

can u do it for me??

Humayun

 

[xenou]

I don't really see what you are trying to do with your code so I'm not sure what needs to be edited. You can try to explain what the purpose of your code is, and then I might be able to see where it need to be changed.

ξ

 

[hrayani]

I don't really see what you are trying to do with your code so I'm not sure what needs to be edited. You can try to explain what the purpose of your code is, and then I might be able to see where it need to be changed.

ξ

Dear Xenou,

I am working on the lottery system and the purpose of the file is only to increment cell L1 value whenever the RUN BUTTON is pressed (which is there on the screen). For example cell L1 value is 16 and if I press the RUN BUTTON then the cell L1 will contiue to increase its value by 1 [unless and until (tests = tests + 1) this part of the code is modified or changed] and stops when the STOP BUTTON is pressed.

What required at the moment is when i press the RUN BUTTON then not only the value of CELL L1 should start increasing but all the existing values within the Range L1:L25 should start increasing unless and until i press the STOP BUTTON

You can see my POST # 1 ... thats the current code i am using as of now...

Regards,

HUMAYUN

 

[xenou]

For a small number of cells (25 cells), you can loop through them and update each one:

Sub Foo()
Dim r As Range
Dim c As Range
    Set r = Range("A1:A10")
    For Each c In r
        c.Value = c.Value + 1
    Next c
End Sub

 

[hrayani]

For a small number of cells (25 cells), you can loop through them and update each one:

Sub Foo()
Dim r As Range
Dim c As Range
    Set r = Range("A1:A10")
    For Each c In r
        c.Value = c.Value + 1
    Next c
End Sub

Dear Xenou,

Thanks for the reply,

Exactly what i was looking for... i have created a command button and link the macro to it

Private Sub CommandButton1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call Foo
End Sub


But the problem is that the speed is very slow whereas the code i am using (post # 1) is faster then this one.. any reasons for that.? can we increase the speed... or can we attach this part of the code to the one i am using?

Regards,

Humayun