vba help, on error goto label

[Mallesh23]

Hi Team,

I am trying to learn on Error goto Label,

Code is working ...... not sure whether its right approach.
Also can we Combine all labels at the end of code into if else stament to shorten code.

Err_First_Loop:
On Error Resume Next
MsgBox "Error in First Loop in cell " & c.Address, vbCritical
On Error GoTo 0
Exit Sub

[CODE=vba]Sub test()

Dim c As Range
On Error GoTo Err_First_Loop
    For Each c In Range("a2:a4")
        MsgBox c / 2
    Next c


On Error GoTo Err_Second_Loop
    For Each c In Range("c2:c4")
        MsgBox c / 2
    Next c


On Error GoTo Err_Third_Loop
    For Each c In Range("e2:e5")
        MsgBox c / 2
    Next c


Exit Sub

Done:

Err_First_Loop:
    On Error Resume Next
    MsgBox "Error in First Loop in cell " & c.Address, vbCritical
On Error GoTo 0
Exit Sub


Err_Second_Loop:
    On Error Resume Next
    MsgBox "Error at Second loop in Cell " & c.Address, vbCritical
    On Error GoTo 0
Exit Sub


Err_Third_Loop:
    On Error Resume Next
        MsgBox "Error at Third loop in Cell" & c.Address, vbCritical
    On Error GoTo 0

End Sub

[/CODE]


Below is a Sample data on which I am testing.

Book11
	A	B	C	D	E
1	Situation1		Situation2		Situation3
2	20		20		20
3	#N/A		90		#N/A
4	60		#N/A		60
5					88
Sheet1

Thanks
mg

 

[Yongle]

Try this

Sub Test()
    Dim c As Range, msg As String
      
    For Each c In Range("a2:a4")
        On Error Resume Next
        Debug.Print c / 2
        If Err.Number <> 0 Then msg = msg & vbCr & "Error at First loop in Cell " & c.Address(0, 0)
        On Error GoTo 0
    Next c
    If Err.Number <> 0 Then msg = "Error at Second loop in Cell " & c.Address

    For Each c In Range("c2:c4")
        On Error Resume Next
        Debug.Print c / 2
        If Err.Number <> 0 Then msg = msg & vbCr & "Error at Second loop in Cell " & c.Address(0, 0)
        On Error GoTo 0
    Next c

    For Each c In Range("e2:e5")
        On Error Resume Next
        Debug.Print c / 2
        If Err.Number <> 0 Then msg = msg & vbCr & "Error at Third loop in Cell " & c.Address(0, 0)
        On Error GoTo 0
    Next c
    If Len(msg) > 0 Then GoTo ErrorInLoop
Exit Sub

ErrorInLoop:
    MsgBox msg, vbCritical, ""
End Sub

NOTE
Debug.Print writes to immediate window in VBA - avoids all those message boxes popping up
To Display Immediate Window click on View \Immediate Window when in VBA editor

 

[Mallesh23]

Hi Yongle,

Wowww ! Perfect !, It worked !

Appreciate your help and Millions of thanks for it! ?



Thanks
mg