• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

combine timeon and number of repeats

S

sswcharlie

New Member
Hello


I have a macro that needs to be run every say 10 seconds and for a set number of times, say 5. I have tried to combine the two types of code, timeon and repeat codes. Not quite correct yet. Anyone able to assist.


Run every 10 seconds for in this case 5 times

"RefreshData" is the macro to be run

Thankyou Chuck


******

(

Sub RunOnTime()


For X = 1 to 5


' "RefreshData" code to run - in this case, five times


Application.OnTime Date + TimeSerial(0, 0, 10), "RefreshData"


Next X


End Sub


)


**********
 
Hi

Still not quite ok. I am copy/paste value from G15 to n15 every 10 seconds for 5 times.

When I 'step thru' code ok. When I run the repeat code it does the first one ok. I then change the value in G15 expecting it to be placed in N15 with the next repeat. But no it does not. Code is:


(Sub RunSettings()


For X = 1 To 5


' "RefreshData" code to run - in this case, five times


Application.OnTime Date + TimeSerial(0, 0, 10), "RefreshData"


Next X


End Sub


Sub RefreshData()

'

' RefreshData Macro


Range("G15").Select

Selection.Copy

Range("N15").Select

ActiveSheet.Paste

End Sub)


When I change the value in G15 does that stop the macro ?


Thankyou


Chuck
 
Hi Chuck ,


In your RefreshData routine , just have the following code :


Range("G15").Copy Destination:=Range("N15")


That should replace the 4 copy and paste statements that are there at present.


Let us know whether this does the job.


Narayan
 
Hi Chuck ,


The problem with the ONTIME statement is that the statement is executed within the FOR loop 5 times in quick succession , one after the other.


What this does is that it schedules the RefreshData procedure to execute after 10 seconds , but since the 5 executions take place instantaneously after the 10 second delay , what you intend to do , i.e. delay the 5 executions , with a 10 second delay between them , does not happen.


Only the first execution of RefreshData happens after 10 seconds ; thereafter the remaining 4 happen without any delay between them.


Narayan
 
Hi Narayan


The new code for copy/paste is great and works well.


With code to run the macro 5 times etc, just checking again, it runs immediately the macro is run, does not wait the 10 seconds. And probably does the five actions then.


What needs to be done to wait 10 seconds, run code, wait another 10 seconds, etc. I thought that was already in the code.

Your comments would be appreciated.

Thankyou Chuck
 
Hi Chuck ,


Yes , but I am still not sure how to get that to happen.


The statement Application.Wait can be used to wait 10 seconds in between each repeat , but this statement essentially stops you from using Excel ! Is that OK with you ?


Replace the ONTIME statement with the following :


Application.Run "ThisWorkBook.RefreshData"

Application.Wait Now + TimeValue("0:00:10")


Narayan
 
Hi Narayan


No there will be keypresses happening and also some macros will need to run during this time.


With the OnTime with running say 10 seconds is this a once only event or does it keep repeating. I need only a single event as the For X covers the repeats.


Thnaks


Charlie
 
Hi Chuck ,


What the ONTIME statement does , is that it schedules an event ; the event is the running of the RefreshData procedure. Once this ONTIME statement is executed from within your RunSettings procedure , execution proceeds with the next statement , which is the NEXT X statement. The next execution of the ONTIME statement now takes place , within a few milliseconds of the earlier execution.


Thus 5 executions of the RefreshData procedure are scheduled within a few milliseconds of each other ; the first execution of the RefreshData procedure takes place 10 seconds after the first execution of the ONTIME statement , but thereafter , the remaining 4 executions of the RefreshData procedure happen in an instant , without the 10 seconds delay between each execution.


So , if you are OK with using the WAIT statement , then this is one way. Others may suggest a different approach.


I think the TIMER function along with the DOEVENTS function can also be used.


Narayan
 
If you want the executions to start in 10 seconds from now and be spaced by about 10 seconds, just multiply your 10 seconds times X:

[pre]
Code: 
For X = 1 to 5
Application.OnTime Now + TimeSerial(0, 0, 10 * X), "RefreshData"
Next X
[/pre]
 
Hello Narayan and ASA


Thanks for your input. The Time Serial change is magic. Works very well. Thanks.


Narayan you fixed the code in the refreshdata macro to one line, and it works great. I would now like to paste it into the next available row starting at O15. Have used your code with a change I found on the internet to:


Range("N15").Copy Destination:=Range("O15").End(xlDown)(2)


but it comes up with a runtime error application defined or object defined error. What does the (2) on the end ? I tried adding a paste instruction.

Thanks

Chuck
 
Hi Chuck ,


The END function takes only a direction , which is either xlDown , xlUp , xlToRight or xlToLeft. The (2) is the source of the error.


If you remove the (2) , then the statement will copy the contents of N15 to the next non-blank cell after O15. I don't think you want to copy the contents of N15 to the entire row after O15.


If all the cells O15 , O16 ,... are blank , then the statement may copy N15 to O65536 !


So , if the cell O24 is the first non-blank cell after O15 , then N15 will be copied to O24 ; is that what you want ?


Narayan
 
Hi Narayan


Yes, you are correct as per your last line.


I have removed the (2) and it step thru is ok. But nothing seems to happen at all.


It is in a module (4) General. Excel 2000


*****


Sub RefreshDataOne()


Range("N15").Copy Destination:=Range("O15").End(xlDown)


End Sub


*****


I am not doing something correctly. But what. I have tried a few things but no luck.

Thankyou

Chuck
 
Hi


Got it working with this xlup code off the net. But the xldown seems to be more simple if I can get it working.


**

Range(Range("n15"), Range("n65536").End(xlUp)).Copy Destination:=Range("o65536").End(xlUp).Offset(1, 0)

**


Thanks


Chuck
 
Hi Chuck ,


Good that you have solved your problem ; the way you have done seems to be the best.


The problem is that the End + Down combination works differently based on whether the intermediate cells are empty or filled with data ; suppose your cursor is at O15 ; now , there can be several possibilities :


1. If the cell O15 is blank , then End + Down takes you to the first non-blank cell ; if the cells beyond O15 in column O are all blank , then you will end up ( ! ) at O65536. If any cell in between , say O37 is non-blank , you will end up with your cursor at O37.


2. If the cell O15 is non-blank , then , the action of End + Down depends on how many more cells after O15 are non-blank ; if O15 is the only non-blank cell in column O , again you end up at O65536. If any cell in between , say O37 is non-blank , you will end up with your cursor at O37. However , if O15 and O16 are non-blank , then the End + Down combination will take you to the last non-blank cell in the range of non-blank cells , which in this case would be O16.


3. The best thing is to go to the last cell in column O , which is O65536 ; then use End + Up to find the last non-blank cell in column O , then back down by 1 to find the first blank cell available. Your use of OFFSET(1,0) does just that.


Narayan
 
Hi Narayan


Thanks for your great explanation. I will go and look at my workbook. I may find some info at row 65536 !

You explained very well and I will stay with the xlUp version.


Thankyou for your help. I have learnt lots about ontime, repeats, and xlUp, xlDown, etc


Thankyou


Chuck
 
Hi Chuck,

I'm glad you got things worked out nicely!


A couple other useful properties of ranges that come to mind, in VBA:

Code: 
range.UsedRange --- = the used part of the range (the part of the range that falls within the last used row and column of the whole worksheet; blanks within that area have no bearing)

[code]range.SpecialCells([i]cell type[,optional subtype][/i][code]) --- = subset of the range that has the given cell type. e.g. xlCellTypeLastCell (automatically limited to last cell of the used part of the range), xlCellTypeBlanks, xlCellTypeVisible.

range.rows.count

range.columns.count[/code]

range.cells.count[/code]

Take care!

Asa
 
You must log in or register to reply here.
Back
Top