Excel: Broken "Do Until" loop


Hi. I have a macro that's supposed to check many individual accounts for a stock symbol, then see if there is enough of that stock symbol in the portfolio to sell or to see if there is enough cash to buy that stock symbol. The part checking if there are enough shares to sell the amount I'm validating for is the part that's not working. If I try to validate the last account in the list of accounts, the macro checks column B indefinitely (even though the "END" is present). If I try to validate any account(s) other than the last one, it works fine. It looks like this with the (assumed) malfunctioning part in red:

Sub Validate()
Dim TransactionType As String
Dim Symbol As String
Dim EstPrice As Double
Dim Confirm As String
Dim CurRelRow As Integer
CurRelRow = 0
Dim CurShare As Integer
CurShare = 0
Dim FidTotal, SchTotal, TDAtotal, OthTotal As Integer
 
FidTotal = 0
SchTotal = 0
TDAtotal = 0
OthTotal = 0
 
TransactionType = Range("R1")
If TransactionType = "" Then
    MsgBox "Please enter transaction type"
    Exit Sub
End If
Symbol = Range("R2")
If Symbol = "" Then
    MsgBox "Please enter symbol"
    Exit Sub
End If
EstPrice = Range("R3")
If EstPrice <= 0 Then
    MsgBox "Please enter an estimated price"
    Exit Sub
End If
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Validate Sell All
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If TransactionType = "Sell All" Then
    Confirm = MsgBox("Are you sure you want to sell all?", vbYesNo, "Confirm")
    If Confirm = vbYes Then
        Range("B9").Select
        Do Until ActiveCell.Value = "END"
            Cells(ActiveCell.Row, 18) = ""
            If ActiveCell.Value <> "" Then
                CurRelRow = ActiveCell.Row
            End If
            If UCase(Cells(ActiveCell.Row, 3).Value) = UCase(Symbol) Then
                Cells(CurRelRow, 18) = Cells(CurRelRow, 18) + Cells(ActiveCell.Row, 6)
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
    Else
        MsgBox ("Validation Aborted")
    End If
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Validate Sell
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf TransactionType = "Sell" Then
    Range("B9").Select
    Do Until ActiveCell.Value = "END"
        CurShare = 0
        If Cells(ActiveCell.Row, 18) <> "" Then
            CurRelRow = ActiveCell.Row
            ActiveCell.Offset(1, 0).Select
            Do Until ActiveCell.Value <> ""
                If UCase(Cells(ActiveCell.Row, 3)) = UCase(Symbol) Then
                    CurShare = CurShare + Cells(ActiveCell.Row, 6)
                End If
                If Cells(ActiveCell.Row, 18) <> "" Then
                    Cells(ActiveCell.Row, 18).Select
                    MsgBox ("Please enter shares at the correct row")
                    Exit Sub
                End If
                ActiveCell.Offset(1, 0).Select
            Loop
            If Cells(CurRelRow, 18) > CurShare Then
                Cells(CurRelRow, 18).Select
                MsgBox ("Shares oversold, please fix and validate again")
                Exit Sub
            End If
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Validate Buy
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Else
    Range("B9").Select
    Do Until ActiveCell.Value = "END"
        If Cells(ActiveCell.Row, 18) <> "" Then
            If ActiveCell = "" Then
                Cells(ActiveCell.Row, 18).Select
                MsgBox ("Please enter shares at the correct row")
                Exit Sub
            End If
            If Cells(ActiveCell.Row, 18).Value * EstPrice > Cells(ActiveCell.Row, 13).Value Then
                Cells(ActiveCell.Row, 18).Select
                MsgBox ("Amount bought exceeds available cash")
                Exit Sub
            End If
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
End If
 
Range("R9").Select
Do Until Cells(ActiveCell.Row, 1) = "END"
    If ActiveCell <> "" Then
        If Len(Cells(ActiveCell.Row, 3)) = 9 Then
            If InStr(Cells(ActiveCell.Row, 3), "-") = 5 Then
                SchTotal = SchTotal + Cells(ActiveCell.Row, 18).Value
            Else
                TDAtotal = TDAtotal + Cells(ActiveCell.Row, 18).Value
            End If
        ElseIf Len(Cells(ActiveCell.Row, 3)) = 10 Then
            FidTotal = FidTotal + Cells(ActiveCell.Row, 18).Value
        Else
            OthTotal = OthTotal + Cells(ActiveCell.Row, 18).Value
        End If
       
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Cells(4, 18) = SchTotal
Cells(5, 18) = FidTotal
Cells(6, 18) = TDAtotal
Cells(7, 18) = OthTotal
 
MsgBox ("Validation completed")
 
End Sub
Please help! Thanks in advance!


This question generated 23 answers. To proceed to the answers, click here.

This thread is current as of August 27, 2014.


For more resources for Microsoft Excel