Give a cell as reference in a module

A

abdul43

Board Regular
Joined
Aug 19, 2011
Messages
58
HI ,
I have a code for getting increasing numbers in a cell.... that is .....
Code: 
Sub Count()
With ActiveSheet.Range("H7")
.Value = .Value + 1
Application.Wait (Now + TimeValue("00:00:01"))
ActiveSheet.PrintOut
If .Value >= 10 Then Exit Sub 'limit
End With
Application.OnTime Now + TimeValue("00:00:02"), "Count" 'every 2 seconds
End Sub
here in line 6 "If .Value >= 10 Then Exit Sub 'limit" i want to replace the value 10 by a cell reference in a sheet within the same work book so it can take the value " limit" from a cell.
Advance thanks.
 
Last edited by a moderator:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Do you need something like this? to change 10 to say cell A1 in Sheet2
Code: 
if .value>=worksheets("Sheet2").range("A1").value
 
Upvote 0
Ah, Momentman has beaten me to the punch!! I won't post my code as it's the same as his but I would to say to ensure that there's is a positive value in the desired cell prior to running or else it just go on until it's manually broken.

HTH

Robert
 
Upvote 0
will it create any problem if no negative value is involved in the whole process?
Click to expand...

No, as long as there's a whole positive number ie 5, 10, 12... should be OK. You could test for the value before running the macro like so:

Code: 
Option Explicit
Sub Count()

    Application.ScreenUpdating = False

    If IsNumeric(Sheets("Sheet2").Range("A1")) = False Or Val(Sheets("Sheet2").Range("A1")) <= 0 Then
        MsgBox "A whole positive number must be entered in cell A1 of Sheet2 prior to commencing." & vbNewLine & "Enter a desired value into the cell and try again.", vbExclamation
        Exit Sub
    End If

    With ActiveSheet.Range("H7")
        .Value = .Value + 1
        Application.Wait (Now + TimeValue("00:00:01"))
        ActiveSheet.PrintOut
        MsgBox "Current value " & Range("H7")
        If .Value >= CLng(Sheets("Sheet2").Range("A1")) Then Exit Sub 'limit
    End With
    
    Application.ScreenUpdating = True
    
    Application.OnTime Now + TimeValue("00:00:02"), "Count" 'every 2 seconds
    
End Sub

HTH

Robert
 
Upvote 0
thanks Momentman this is real solution to my problem permit me for one more question related to this code, that is, to add a button for stoping this code at any time plz.
 
Upvote 0
Are you referring to the code posted by Robert(trebor 76) or which code exactly?
 
Upvote 0
The code which i improved after your help on it...
Code: 
 Sub Count()
With ActiveSheet.Range("H7")
Application.Wait (Now + TimeValue("00:00:02"))
ActiveSheet.PrintOut
.Value = .Value + 1
If .Value > Worksheets("First-14").Range("A62").Value Then Exit Sub 'limit
End With
Application.OnTime Now + TimeValue("00:00:02"), "Count" 'every 2 seconds
End Sub
 
Last edited by a moderator:
Upvote 0
Ok you would probably need an extra module

like this
Rich (BB code): 
Public stopcode As Boolean
Sub Count()
    stopcode = False
    If stopcode = False Then
        With ActiveSheet.Range("H7")
            .Value = .Value + 1
            Application.Wait (Now + TimeValue("00:00:01"))
            ActiveSheet.PrintOut
         If .Value > Worksheets("First-14").Range("A62").Value Then Exit Sub 'limit
        End With
        Application.OnTime Now + TimeValue("00:00:02"), "Count" 'every 2 seconds
    End If
End Sub
Rich (BB code): 
Sub stoptherun()
    stopthecode = True
End Sub

This second code is what you need to assign to a button, simply right click the button and select "Assign Macro"
 
Last edited:
Upvote 0
I modified the first code thus
Code: 
Sub Count()
    stopcode = False
    If stopcode = False Then
        With ActiveSheet.Range("H7")
            .Value = .Value + 1
            Application.Wait (Now + TimeValue("00:00:01"))
            ActiveSheet.PrintOut
        If .Value >= 10 Then Exit Sub 'limit
        End With
        Application.OnTime Now + TimeValue("00:00:02"), "Count" 'every 2 seconds
    Else
        Exit Sub
    End If
End Sub

The second is still same
Code: 
Sub stopthecode()
    stopthecode = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

corentint
  • Solved
A recursive procedure
Replies
3
Views
125
John_w
J
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
149
bh24524
B
B
  • Question
set timer with a variable
Replies
2
Views
94
bbxrider
B
V
  • Question
call Modules
Replies
5
Views
393
RoryA
RoryA
R
  • Question
Issue at time change (spring forward) in excel vba.
Replies
0
Views
91
Russel R
R

Forum statistics

Threads
1,214,787
Messages
6,121,561
Members
449,038
Latest member
Guest1337

Share this page

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
Back
Top