#1
|
|||
|
|||
“Error handler process” in a “for loop”
I want to use “error handler process” in a “for loop” such that if the condition defined for the loop is not met, the macro increases i(the variable defined for loop) by 1 unit and get back to beginning of the loop .
Consider following macro, if it get “run time error” at i=4, I expect that it continue at i=5. As the Excel attachment shows, this code dose not work as I expect and stop as i=4 with “run time” error. Can you give me some guides on this issue? Option Explicit Sub forlloop() Dim cell0 As Range Dim i As Integer Dim n As Integer Dim j As Integer 'MsgBox Selection.Font.ThemeColor On Error GoTo I_handle_error Set cell0 = Cells(1, 1) n = 45 For i = 0 To ActiveSheet.Range("A1").SpecialCells(xlCellTypeLas tCell).Row - 1 a: cell0.Offset(i).Select If Selection.Value = "total" Then cell0.Offset(i, n - 1).Value = "end" ElseIf Selection.Font.ThemeColor = xlThemeColorDark1 Then cell0.Offset(i, n - 1).Value = "beg" End If Next i Exit Sub I_handle_error: i = i + 1 GoTo a End Sub |
#2
|
|||
|
|||
I think this may be happening because you have jumped out of the loop - you cannot simply jump back in again.
So you need to trap and handle the error whilst remaining inside the loop - for instance using the iserror function. I am always wary of creating infinite loops when playing with the loop counter, so watch out for that ! PS Is there meant to be a space in ElseIF Selection . . . ? Last edited by MartinGM; 12-18-2023 at 01:05 PM. |
#3
|
|||
|
|||
Thank you for your guidance. I don't understand what you mean by "PS". According to your hint, I have changed the code as following. But it still gives error. (this time at i=3)
Option Explicit Sub forlloop() Dim cell0 As Range Dim i As Integer Dim n As Integer Dim j As Integer Set cell0 = Cells(1, 1) n = 45 For i = 0 To ActiveSheet.Range("A1").SpecialCells(xlCellTypeLas tCell).Row - 1 cell0.Offset(i).Select If Selection.Value = "total" Then cell0.Offset(i, n - 1).Value = "end" Else MsgBox IsError(Selection.Font.ThemeColor) If IsError(Selection.Font.ThemeColor) = True Then If Selection.Font.ThemeColor = xlThemeColorDark1 Then cell0.Offset(i, n - 1).Value = "beg" End If End If End If Next i End Sub |
Tags |
error handler |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
error handler: inserting building blocks when multiple cells in a table are selected creates error | LSLSLS | Word VBA | 2 | 01-07-2022 05:46 AM |
Error Handler If User Does Not Want to Overwrite File | ChrisOK | Excel Programming | 4 | 04-03-2020 08:31 PM |
Process Automation | saurabhlotankar | Excel Programming | 10 | 06-03-2015 05:50 PM |
How to Control Worksheet Event Handler in Module? | tinfanide | Excel Programming | 2 | 10-19-2014 09:46 AM |
'Infinite Loop' error with Infopath 2010 | Debbie25 | Misc | 2 | 05-18-2011 08:38 AM |