Results 1 to 5 of 5

Thread: Breaking a "With" "End With" Procedure

  1. #1

    Breaking a "With" "End With" Procedure



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    A for while loop?!?

    With..End With is not a loop.

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

  3. #3
    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

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    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

  5. #5
    Thank you P45, Works like a champ. I should have known to put the test before the "with" statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •