Hello all, I have a macro set-up to check a through a range of cell
looking for members code 1 & changing it to a sequential number
dependent code 2 & setting it to null.

What I'd like to do is when it pops up the msgbox at the end informin
the user how many members records were changed & dependent record
changed I can also include something like "Task completed in #
seconds".

How do I set up a timer to count of the seconds from the start of th
code until the last record has been changed?

Do you need me to post the code I have written so far?

Thanks in advance,

eri

--
cjsas
-----------------------------------------------------------------------
cjsasl's Profile: http://www.excelforum.com/member.php...fo&userid=2871
View this thread: http://www.excelforum.com/showthread.php?threadid=48400

Hi

While not extremely accurate, you could do something like this which will
give you a rough idea of the time.

Dim totaltime As Long
Dim Starttime As Long
Dim Endtime As Long
Dim Min as Integer
Dim Sec as Integer



starttime = Now()


'Your code here



Endtime = Now()

totaltime = DateDiff("s", starttime, endtime)

'convert seconds into minutes and seconds to display
Min = Int(totaltime \ 60)
sec = totaltime Mod 60

Totaltimetaken = Min & " Minute(s) and " & sec & " Seconds."

MsgBox(Totaltimetaken)



HTH

Ken

Sub a()
Dim StartTime As Double
Dim Counter As Long
StartTime = Timer
For Counter = 1 To 100000
DoEvents
Next
MsgBox Format(Timer - StartTime, "0") & " seconds"
End Sub


--
Jim
"cjsasl" wrote in
message ...
|
| Hello all, I have a macro set-up to check a through a range of cells
| looking for members code 1 & changing it to a sequential number &
| dependent code 2 & setting it to null.
|
| What I'd like to do is when it pops up the msgbox at the end informing
| the user how many members records were changed & dependent records
| changed I can also include something like "Task completed in ##
| seconds".
|
| How do I set up a timer to count of the seconds from the start of the
| code until the last record has been changed?
|
| Do you need me to post the code I have written so far?
|
| Thanks in advance,
|
| eric
|
|
| --
| cjsasl
| ------------------------------------------------------------------------
| cjsasl's Profile:
http://www.excelforum.com/member.php...o&userid=28712
| View this thread: http://www.excelforum.com/showthread...hreadid=484006
|

Ken I tried your code but it keeps displaying 0:0 for the time

Jim yours won't work where I'm trying to display the time in a seperat
function message box.

Below is the code I have written.

Down in the Function CheckCell() section under the If (Vol = "") the
statement is were I am trying to get the elapse time to be stored t
use in the msgbox below it.

Hope this helps



------------Excel Code----------
Option Explicit
Dim MemRecords As Integer ' Creates MemRecords memory variable fo
numeric values.
Dim TotalRecords As Integer ' Creates TotalRecords memory variable fo
numeric values.
Dim Vol As String ' Creates Vol memory variable for string values.
Dim StartPoint As String ' Creates StartingPoint memory variable fo
string values.
Dim TotalTime As Long
Dim StartTime As Double

' Start section looks for the first cell that contains the Member
code
' or the code for Depentent, then resets the main count variables t
0.

one other thing, if you try to copy this code & run it you need a spread
sheet open with a couple of celles with the value 1 or 2 in them
starting in the A1 cell and working down, it stops at the 1st empy
cell.

eric


--
cjsasl
------------------------------------------------------------------------
cjsasl's Profile: http://www.excelforum.com/member.php...o&userid=28712
View this thread: http://www.excelforum.com/showthread...hreadid=484006

Sub Start()

On Error Resume Next ' if any errors encountered continue without
crashing macro.
StartTime = Timer

--------------------------
then just before your message box

TotalTime = timer - StartTime


By dimming TotalTime as Long, you will only get whole seconds, but this
shouldn't be a problem it your routine takes more than a second.

--
Regards,
Tom Ogilvy



"cjsasl" wrote in
message ...

Ken I tried your code but it keeps displaying 0:0 for the time

Jim yours won't work where I'm trying to display the time in a seperate
function message box.

Below is the code I have written.

Down in the Function CheckCell() section under the If (Vol = "") then
statement is were I am trying to get the elapse time to be stored to
use in the msgbox below it.

Hope this helps



------------Excel Code----------
Option Explicit
Dim MemRecords As Integer ' Creates MemRecords memory variable for
numeric values.
Dim TotalRecords As Integer ' Creates TotalRecords memory variable for
numeric values.
Dim Vol As String ' Creates Vol memory variable for string values.
Dim StartPoint As String ' Creates StartingPoint memory variable for
string values.
Dim TotalTime As Long
Dim StartTime As Double

' Start section looks for the first cell that contains the Members
code
' or the code for Depentent, then resets the main count variables to
0.
Sub Start()

On Error Resume Next ' if any errors encountered continue without
crashing macro.

StartPoint = "A1" ' Stores the cell number A1 in the StartPoint
variable.
Range(StartPoint).Select 'Moves the pointer to the StartPoint value.
Vol = ActiveCell.Value ' Stores the contents of the active cell in the
Vol variable.

' Checks each cell from A1 down until it finds a cell containing a 1 or
2.
Do Until (Vol = 1) Or (Vol = 2) ' Checks the contentes of current cell
for a 1 or 2 value.
ActiveCell.Offset(1, 0).Select ' Moves the cursor down 1 cell &
selects it.
Vol = ActiveCell.Value ' Stores the contents of the current cell in
the Vol variable.
Loop ' Loops back the check the value in Vol variable for a 1 or 2.

' The Do loop has completed the search for the start of the Members
code and now the
' commands below are run.
MemRecords = 0 ' Sets MemRecords to 0.
TotalRecords = 0 ' Sets TotalRecords to 0.
CheckCell ' Tells the macro to jump to the CheckCell Sun routine.
End Sub

' This CheckCell Sub routine checks to see if the contents of the cells
are a 1 & if so
' changes it to the next incremented number. If the cell contains a 2
then change it to
' empty.


Function CheckCell()

' Stores the contents of the current cell to the Vol variable before it
checks to verify
' if the cell contents is a 1 or 2 to make the correct changes.
Vol = ActiveCell.Value

' When Vol = "" then the current cell being checked means the cell
is empty & last record was found.
If (Vol = "") Then ' Checking to see if an empty cell is found
which denotes the end of the records.
ActiveCell.Offset(-1, 0).Select ' When the 1st empty cell is
found on this moves the cursor back up to last cell containing a 1 or
2.
' MsgBox command below displays the message that the search has
been completed & shows how many Member & Dependent records were changed
& the total records searched.

(I want to get the time it too to run code set to TotalTime variable
here to use in msgbox statement below)

MsgBox MemRecords & " Member records were numbered in sequence." &
vbCrLf & TotalRecords - MemRecords & " Dependent records were set to
Null." & vbCrLf & "-----" & vbCrLf & TotalRecords & " Total records
found/changed.", vbOKOnly, "ClaimSecure's Members Eligibility Search &
Replace -Kim Blake-" ' When a 1 is found in the current cell being
checked the code below is run."
Range(StartPoint).Select ' Moves the cursor back to the A1
starting point after the clicks Ok on the Message box from line
above."
End ' Ends the macro since the blank cell at bottom of
spreadsheed has been found.

' When a 1 is found in the current cell being checked the code
below is run.
ElseIf (Vol = 1) Then ' Checks to verify the cell contains a 1 then
runs the code below.
ActiveCell.Formula = MemRecords + 1 ' Changes the value of the
current cell from the 1 to the next number in the sequence.
ActiveCell.Offset(1, 0).Select ' Drops down one cell & selects
it.
MemRecords = MemRecords + 1 ' Changes the value in the
MemRecords variable by adding 1 to it.
TotalRecords = TotalRecords + 1 ' Changes the value in the
TotalRecords variable by adding 1 to the total.
CheckCell ' Calls the CheckCell Sub rouutine again to check the
value in the cell it just moved to.

' When a 2 is found in the current cell being checked the code
below is run.
ElseIf (Vol = 2) Then ' Checks to verify the cell contains a 2 then
runs the code below.
ActiveCell.Formula = Null ' Changes the 2 value in the cell and
set it to Null/empty.
ActiveCell.Offset(1, 0).Select ' Drops down to the next cell
and selects it.
TotalRecords = TotalRecords + 1 ' Changes the value in the
TotalRecords variable by adding 1 to the total.
CheckCell ' Calls the CheckCell Sub rouutine again to check the
value in the cell it just moved to.

End If
End Function


--
cjsasl
------------------------------------------------------------------------
cjsasl's Profile:
http://www.excelforum.com/member.php...o&userid=28712
View this thread: http://www.excelforum.com/showthread...hreadid=484006

Thank you very much Tom, the only thing I had to change was the code:

TotalTime = (Timer - StartTime) ' This gave me a - time (ie -3
Second(s))
to
TotalTime = (StartTime - Timer)

And now it works perfect.

Thanks Tom you are the best...:)


--
cjsasl
------------------------------------------------------------------------
cjsasl's Profile: http://www.excelforum.com/member.php...o&userid=28712
View this thread: http://www.excelforum.com/showthread...hreadid=484006

If you set StartTime to the value of Timer at the start of your code, then
when you are ready to show the time, you should subtract StartTime from
Timer.

Timer is the elapsed number of seconds since midnight.

If you were getting a negative time with

Timer - StartTime

then something isn't working correctly.

You should only set TotalTime in the same code block where the message box
string is built, so it is set just before showing the message.

--
Regards,
Tom Ogilvy


"cjsasl" wrote in
message ...

Thank you very much Tom, the only thing I had to change was the code:

TotalTime = (Timer - StartTime) ' This gave me a - time (ie -3
Second(s))
to
TotalTime = (StartTime - Timer)

And now it works perfect.

Thanks Tom you are the best...:)


--
cjsasl
------------------------------------------------------------------------
cjsasl's Profile:
http://www.excelforum.com/member.php...o&userid=28712
View this thread: http://www.excelforum.com/showthread...hreadid=484006

Hi

Sorry about that. It works fine if you remove some of the dims like this.

Dim totaltime As Long



starttime = Now()


'Your code here



Endtime = Now()

totaltime = DateDiff("s", starttime, endtime)

'convert seconds into minutes and seconds to display
Mins = Int(totaltime \ 60)
Secs = totaltime Mod 60

Totaltimetaken = Mins & " Minute(s) and " & secs & " Seconds."

MsgBox(Totaltimetaken)



HTH

Ken