Breaking a "With" "End With" Procedure

BACK2BASIC

New member
Joined
Oct 14, 2013
Messages
25
Reaction score
0
Points
0
Hi, will someone help me figure out the best way to end a "with" statement. I am using inside a for while loop.

My problem is if I receive an error while searching for a data string, example data not found, I want to break out and jump to the end of the loop. I can not break out using the "end with" statement. What can I do?:confused2:
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
A for while loop?!?

With..End With is not a loop.

Try Exit For or Exit Do, failing that post your existing code.
 

BACK2BASIC

New member
Joined
Oct 14, 2013
Messages
25
Reaction score
0
Points
0
Thank you for your reply. I understand however I am ingorant on this command and not sure how to replace it. Any help you can give me is greatly appreciated.

What is happening below is when the "find" returns a Null becuase "WR" is not found, the with statement crashes. That is OK because I need to skip the next three lines of code anyways; However I do need to continue the loop I am in. How do I replace the "with" statement and get the same result in the variable ".value"?

For i = 1 to 100
WR = Cells(x, 6)
Sheets("JL").Activate
Set GCell = ActiveSheet.Cells.Find(WR)
With ThisWorkbook.ActiveSheet.Range(GCell.Address)
On Error GoTo ErrorHandler
JLdataSub = .Offset(0, 6).Value
JLdataCG = .Offset(0, -2).Value
JLGFDate = .Offset(0, 9).Value
End With
x= x+1
next i
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
Try the adjustments in blue below:

Dim GCell As Range

For i = 1 To 100
WR = Cells(x, 6)
Sheets("JL").Activate
Set GCell = ActiveSheet.Cells.Find(WR)
If Not GCell Is Nothing Then
With GCell
On Error GoTo ErrorHandler
JLdataSub = .Offset(0, 6).Value
JLdataCG = .Offset(0, -2).Value
JLGFDate = .Offset(0, 9).Value
End With
End If
x = x + 1
Next i
 

BACK2BASIC

New member
Joined
Oct 14, 2013
Messages
25
Reaction score
0
Points
0
Thank you P45, Works like a champ. I should have known to put the test before the "with" statement.
 
Top