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:
Please help! Thanks in advance!
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:
Code:
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
[COLOR=#ff0000]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[/COLOR]
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 SubPlease help! Thanks in advance!
