Consulting

Results 1 to 5 of 5

Thread: Help Exiting Loop

  1. #1
    VBAX Regular
    Joined
    Oct 2010
    Posts
    49
    Location

    Help Exiting Loop

    Hi Guys,

    With the following code, I can't seem to get out of the two loops below which I have made comments next to.

    Therefore the macro doesn't go onto the next loop. I want the two loops to end when it encounters a blank cell. Everything else, seems to be working alright.

    If anyone can help with this, I'd greatly appreciate it!!

    Thank you!

    Giri

    [VBA]
    Option Explicit

    Public Sub AmpLife()

    Dim c As Range
    Dim n As Integer
    Dim x As Integer
    Dim row As Integer
    Dim stringArray(3) As String
    Dim searchString As String

    'Application.ScreenUpdating = False


    row = Range("D2").End(xlDown).row
    n = 0

    For Each c In Range("D2" & row)


    For x = 0 To 2
    searchString = c.Value
    stringArray(x) = Split(searchString, " ")(n)
    n = n + 1
    Next x

    c.Select

    On Error Resume Next

    If stringArray(0) <> "AMP" Or stringArray(1) <> "Life" Or stringArray(2) <> "Ltd" Then

    c.EntireRow.Delete
    stringArray(0) = Split(ActiveCell.Value, " ")(0)
    stringArray(1) = Split(ActiveCell.Value, " ")(1)
    stringArray(2) = Split(ActiveCell.Value, " ")(2)

    Do While stringArray(0) <> "AMP" Or stringArray(1) _ 'THIS LOOP
    <> "Life" Or stringArray(2) <> "Ltd" And IsEmpty(ActiveCell) = False


    c.Select
    ActiveCell.EntireRow.Delete
    stringArray(0) = Split(ActiveCell.Value, " ")(0)
    stringArray(1) = Split(ActiveCell.Value, " ")(1)
    stringArray(2) = Split(ActiveCell.Value, " ")(2)

    Loop



    End If

    n = 0

    Next c

    On Error GoTo 0





    row = Range("E2").End(xlDown).row

    For Each c In Range("E2:E" & row)

    c.Select
    stringArray(0) = Split(ActiveCell.Value, " ")(0)

    If stringArray(0) <> "Internal" Then
    c.EntireRow.Delete
    stringArray(0) = Split(ActiveCell.Value, " ")(0)
    End If

    Do While stringArray(0) <> "Internal" 'THIS LOOP
    ActiveCell.Select
    ActiveCell.EntireRow.Delete
    stringArray(0) = Split(ActiveCell.Value, " ")(0)
    Loop
    Next c

    'Application.ScreenUpdating = True

    End Sub
    [/VBA]

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,942
    Location
    Not sure about the logic, but

    1. Maybe Parens ?

    [VBA]
    Do While _
    (stringArray(0) <> "AMP" Or _
    stringArray(1) <> "Life" Or _
    stringArray(2) <> "Ltd") And _
    Not IsEmpty(ActiveCell)
    [/VBA]


    2. I did notice that you select a cell, and delete the row it's in. Next you refer to the ActiveCell. I was wondering if that is what you want to do since the ActiveCell would change

    [VBA]
    c.Select
    ActiveCell.EntireRow.Delete
    stringArray(0) = Split(ActiveCell.Value, " ")(0)
    stringArray(1) = Split(ActiveCell.Value, " ")(1)
    stringArray(2) = Split(ActiveCell.Value, " ")(2)
    [/VBA]


    3. You probably don't need to select the cell to act on it

    [VBA]
    ' c.Select
    c.EntireRow.Delete
    stringArray(0) = Split(c.Value, " ")(0)
    stringArray(1) = Split(c.Value, " ")(1)
    stringArray(2) = Split(c.Value, " ")(2)
    [/VBA]


    What are you trying to do? It sort of appears like you want to delete rows if col D doesn't contain AMP Life Ltd. ???? But I didn't trace all the way though

    Paul

  3. #3
    VBAX Regular
    Joined
    Oct 2010
    Posts
    49
    Location
    The parentheses seems to have worked. I did the same for the second loop too.

    Also, I noticed that if I didn't select the ActiveCell, certain rows would not be deleted.

    Thanks Paul!

    Kind Regards,

    Giri

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,942
    Location
    When deleting rows, I've always found it's bettter to start at the bottom and delete and keep working my way to the top

    That way any counters or other indicators (e.g. ActiveCell) don't get messed up and refer to a wrong row.

    Paul

  5. #5
    VBAX Regular
    Joined
    Oct 2010
    Posts
    49
    Location
    Thanks Paul - didn't think about that.

    Kind Regards,

    Giri

Posting Permissions

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