| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat a marco with number of times or loop until End
Dear Expert,
I know this may be easy for some of the experts, but I still how you can help ... Say following scripts ... I would like to run this scripts for 100 times ... Can you advise how to write? Application.Run "SkyDaysHour_X31_H2_Hour" Can you also tell how to write loop until it reach error and jump to next step ..? Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat a marco with number of times or loop until End
Hi Elton,
The following will perform the loop. Sub RunInLoop() Dim i As Long For i = 1 To 100 Application.Run "SkyDaysHour_X31_H2_Hour" Next i End Sub I am not sure what you are attempting to achieve with the On Error. I shouldn't think that you will get an error in the loop code as above. Perhaps you mean in the sub that is being called so it would be as follows. Note the comments. The On Error needs to be in the sub where the error is likely to be produced. Sub SkyDaysHour_X31_H2_Hour() 'The following line goes immediately prior 'to the line that is likely to produce error On Error Resume Next 'The code likely to produce error here 'The following immediately after the 'line likely to produce error On Error GoTo 0 'Resumes errror trapping End Sub -- Regards, OssieMac "Elton Law" wrote: Dear Expert, I know this may be easy for some of the experts, but I still how you can help ... Say following scripts ... I would like to run this scripts for 100 times ... Can you advise how to write? Application.Run "SkyDaysHour_X31_H2_Hour" Can you also tell how to write loop until it reach error and jump to next step ..? Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat a marco with number of times or loop until End
Hi again elton,
Re-reading your question; perhaps what you mean is that you want to return a flag to the loop if an error has occurred in the called sub and therefore want to exit the loop. If so then the following. At the top of the VBA editor before any subs insert the following declaration. Public errorFlag As Boolean Then your loop code like the following. Sub RunInLoop() Dim i As Long errorFlag = False 'Initialize to false For i = 1 To 100 If errorFlag = True Then Exit For End If Application.Run "SkyDaysHour_X31_H2_Hour" Next i End Sub and include the following code in your called sub Sub SkyDaysHour_X31_H2_Hour() 'The following line goes immediately prior 'to the line/s that likely to produce error On Error GoTo SubError 'The code likely to produce error here 'The following immediately after the 'code likely to produce error On Error GoTo 0 'Resumes errror trapping 'The following 3 lines of code are the 'last lines of code before the End Sub Exit Sub SubError: errorFlag = True End Sub -- Regards, OssieMac "Elton Law" wrote: Dear Expert, I know this may be easy for some of the experts, but I still how you can help ... Say following scripts ... I would like to run this scripts for 100 times ... Can you advise how to write? Application.Run "SkyDaysHour_X31_H2_Hour" Can you also tell how to write loop until it reach error and jump to next step ..? Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat a marco with number of times or loop until End
Hi Expert,
Thanks for your reply. I should have typed more clearly. If I want to repeat this action in a marco. I would copy and paste ... Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" ............... .............. ............. on and on until 100 times .... Sound like it is very stipud. I would like to know whether there is script .... Say define at the begining and run this action for 100 times rather than copy/paste this sentence for 100 times. Hope you know what I mean. Another one is .... I even don't know when this action will be ended .... (sometimes it is less than 100 times) So just let it re-run and re-run .... until it cannot run due to error ... Then jump to next step.... (maybe less than 100 times) Hope you can help... Regards, Elton "OssieMac" wrote: Hi again elton, Re-reading your question; perhaps what you mean is that you want to return a flag to the loop if an error has occurred in the called sub and therefore want to exit the loop. If so then the following. At the top of the VBA editor before any subs insert the following declaration. Public errorFlag As Boolean Then your loop code like the following. Sub RunInLoop() Dim i As Long errorFlag = False 'Initialize to false For i = 1 To 100 If errorFlag = True Then Exit For End If Application.Run "SkyDaysHour_X31_H2_Hour" Next i End Sub and include the following code in your called sub Sub SkyDaysHour_X31_H2_Hour() 'The following line goes immediately prior 'to the line/s that likely to produce error On Error GoTo SubError 'The code likely to produce error here 'The following immediately after the 'code likely to produce error On Error GoTo 0 'Resumes errror trapping 'The following 3 lines of code are the 'last lines of code before the End Sub Exit Sub SubError: errorFlag = True End Sub -- Regards, OssieMac "Elton Law" wrote: Dear Expert, I know this may be easy for some of the experts, but I still how you can help ... Say following scripts ... I would like to run this scripts for 100 times ... Can you advise how to write? Application.Run "SkyDaysHour_X31_H2_Hour" Can you also tell how to write loop until it reach error and jump to next step ..? Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat a marco with number of times or loop until End
Try something like Dim N As Long For N = 1 to 100 Application.Run..... Next N Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 28 Mar 2010 08:22:01 -0700, Elton Law wrote: Hi Expert, Thanks for your reply. I should have typed more clearly. If I want to repeat this action in a marco. I would copy and paste ... Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" .............. ............. ............ on and on until 100 times .... Sound like it is very stipud. I would like to know whether there is script .... Say define at the begining and run this action for 100 times rather than copy/paste this sentence for 100 times. Hope you know what I mean. Another one is .... I even don't know when this action will be ended .... (sometimes it is less than 100 times) So just let it re-run and re-run .... until it cannot run due to error ... Then jump to next step.... (maybe less than 100 times) Hope you can help... Regards, Elton "OssieMac" wrote: Hi again elton, Re-reading your question; perhaps what you mean is that you want to return a flag to the loop if an error has occurred in the called sub and therefore want to exit the loop. If so then the following. At the top of the VBA editor before any subs insert the following declaration. Public errorFlag As Boolean Then your loop code like the following. Sub RunInLoop() Dim i As Long errorFlag = False 'Initialize to false For i = 1 To 100 If errorFlag = True Then Exit For End If Application.Run "SkyDaysHour_X31_H2_Hour" Next i End Sub and include the following code in your called sub Sub SkyDaysHour_X31_H2_Hour() 'The following line goes immediately prior 'to the line/s that likely to produce error On Error GoTo SubError 'The code likely to produce error here 'The following immediately after the 'code likely to produce error On Error GoTo 0 'Resumes errror trapping 'The following 3 lines of code are the 'last lines of code before the End Sub Exit Sub SubError: errorFlag = True End Sub -- Regards, OssieMac "Elton Law" wrote: Dear Expert, I know this may be easy for some of the experts, but I still how you can help ... Say following scripts ... I would like to run this scripts for 100 times ... Can you advise how to write? Application.Run "SkyDaysHour_X31_H2_Hour" Can you also tell how to write loop until it reach error and jump to next step ..? Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat a marco with number of times or loop until End
Hi. Don't know what you have set up, but would any ideas here help? Option Explicit Private ErrorOccuredQ As Boolean Sub MyLoop() ErrorOccuredQ = False Do While Not ErrorOccuredQ Run "SkyDaysHour_X31_H2_Hour" If ErrorOccuredQ Then Exit Do Loop End Sub Sub SkyDaysHour_X31_H2_Hour() 'Do Stuff 'If some type of error occurs ErrorOccuredQ = True 'end if End Sub = = = = = = = HTH Dana DeLouis On 3/28/2010 11:22 AM, Elton Law wrote: Hi Expert, Thanks for your reply. I should have typed more clearly. If I want to repeat this action in a marco. I would copy and paste ... Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" Application.Run "SkyDaysHour_X31_H2_Hour" .............. ............. ............ on and on until 100 times .... Sound like it is very stipud. I would like to know whether there is script .... Say define at the begining and run this action for 100 times rather than copy/paste this sentence for 100 times. Hope you know what I mean. Another one is .... I even don't know when this action will be ended .... (sometimes it is less than 100 times) So just let it re-run and re-run .... until it cannot run due to error ... Then jump to next step.... (maybe less than 100 times) Hope you can help... Regards, Elton "OssieMac" wrote: Hi again elton, Re-reading your question; perhaps what you mean is that you want to return a flag to the loop if an error has occurred in the called sub and therefore want to exit the loop. If so then the following. At the top of the VBA editor before any subs insert the following declaration. Public errorFlag As Boolean Then your loop code like the following. Sub RunInLoop() Dim i As Long errorFlag = False 'Initialize to false For i = 1 To 100 If errorFlag = True Then Exit For End If Application.Run "SkyDaysHour_X31_H2_Hour" Next i End Sub and include the following code in your called sub Sub SkyDaysHour_X31_H2_Hour() 'The following line goes immediately prior 'to the line/s that likely to produce error On Error GoTo SubError 'The code likely to produce error here 'The following immediately after the 'code likely to produce error On Error GoTo 0 'Resumes errror trapping 'The following 3 lines of code are the 'last lines of code before the End Sub Exit Sub SubError: errorFlag = True End Sub -- Regards, OssieMac "Elton Law" wrote: Dear Expert, I know this may be easy for some of the experts, but I still how you can help ... Say following scripts ... I would like to run this scripts for 100 times ... Can you advise how to write? Application.Run "SkyDaysHour_X31_H2_Hour" Can you also tell how to write loop until it reach error and jump to next step ..? Thanks -- = = = = = = = HTH :) Dana DeLouis |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat a marco with number of times or loop until End
Hi Elton,
I see that you checked my second post as the correct answer but based on your later comment that the loop should run until error and you don't know how many times that will be then you should replace the loop code with the following code and then it does not matter if less than or more than 100 loops. The called sub code can remain as per my previous post. Sub RunInLoop() errorFlag = False 'Initialize to false Do While errorFlag = False Application.Run "SkyDaysHour_X31_H2_Hour" Loop End Sub -- Regards, OssieMac |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| repeat number multiple times | Excel Discussion (Misc queries) | |||
| Repeat text a number define of times | Excel Worksheet Functions | |||
| Why can the number of iterations repeat many times as a loop?? | Excel Discussion (Misc queries) | |||
| Repeat statements "x" number of times... | Excel Discussion (Misc queries) | |||
| how do I get a calculation to repeat various number of times? | Excel Worksheet Functions | |||
![Elton Law[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
