Hello,
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.
Code
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
Display More
Code
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
Display More