I come to you looking for a solution that is probably simple for others, but I really can't figure it out.
Here is the resume:
I have the code below that works great, but I want it to stop completely and go to MyEnd if XShts (which is in module 1) turnes out to be an error.
If xShts doesn't return an error, then both of them should do what they do.
But if xShts gives an error, I want to see MyErr on the screen and the 1st code should stop there (The code for xShts already does that).
If you have any solutions, can you please respond with the updated code for this issue ? I'm not good enough to do it myself based on instructions.
Thank you in advance.
Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H14:H25")) Is Nothing Then Dim x As Long, MyBool As Boolean Dim ws2 As Worksheet: Set ws2 = Sheet2 Dim ws3 As Worksheet: Set ws3 = Sheet3 On Error Resume Next x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0) MyBool = False If IsNumeric(x) Then For i = 7 To Columns.Count If ws2.Cells(1, i).EntireColumn.Hidden = False Then ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 + CStr(Target.Value) If MyBool = False Then Call XShts(Target.Row) MyBool = True GoTo MyEnd End If Next i End If End If MyEnd: End Sub
Sub XShts(TgtRW As Long) GoTo MyStart MyErr: MsgBox "Error: The street may not be defined or the street may not have this item code" GoTo MyEnd MyStart: On Error GoTo MyErr Dim ws3 As Worksheet: Set ws3 = Sheet3 If Len(ws3.Range("G9")) Then Dim wsX As Worksheet: Set wsX = Worksheets(Range("G9").Value2) Dim MyXRW As Long MyXRW = Application.Match(Range("B" & TgtRW), wsX.Range("B6:B500"), 0) + 5 If IsNumeric(MyXRW) Then wsX.Range("F" & MyXRW) = wsX.Range("F" & MyXRW) + CStr(ws3.Range("H" & TgtRW)) End If MyEnd: End Sub