if statement
Could you help me get these conditions in VBA code?
':: first condition IF A3 < A5 AND A4 A5, THEN A6 = A4 - A5 AND A7 = LONG. IF A3 < A5 and A4 < A5 THEN A6 = A4 - A5 AND A7 = LOSS IF A3 A5 AND A4 < A5 THEN A7 = NOT LONG ':: second condition IF A7 = null OR LOSS OR NOT LONG THEN first condition for next column, B IF A7 = LONG THEN IF B4 B5, THEN B6 = B4-A5 AND A6 = 0 AND B7 = LONG. IF B4 < B5 THEN B6 = B4-A5 AND A6 = 0 AND B7 = CLOSE LONG. ':: This loops until the last nonblank value in row 1. |
if statement
I didn't test this extensively, and I'm not sure I completely understand what you are trying to accomplish, but this should at the very least get you on the right track.
Sub mysub() Dim DoFirstCon As Boolean Dim i As Integer i = 1 DoFirstCon = True Do Until IsEmpty(Cells(3, i)) If DoFirstCon Then FirstCondition (i) If Cells(7, i).Value = "LONG" Then 'don't do firstcondition on column i+1 DoFirstCon = False Cells(6, i + 1).Value = Cells(4, i + 1).Value - Cells(5, i).Value Cells(6, i).Value = 0 If Cells(4, i + 1).Value Cells(5, 1 + 1).Value Then Cells(7, i + 1).Value = "LONG" Else: Cells(7, i + 1).Value = "CLOSE" End If End If i = i + 1 Loop End Sub Function FirstCondition(Count As Integer) Dim dRow3 As Double Dim dRow4 As Double Dim dRow5 As Double dRow3 = Cells(3, Count).Value dRow4 = Cells(4, Count).Value dRow5 = Cells(5, Count).Value MsgBox "loop number " & Count If dRow3 dRow5 And dRow4 < dRow5 Then Cells(7, Count).Value = "NOT LONG" Else Cells(6, Count).Value = dRow4 - dRow5 If dRow3 < dRow5 And dRow4 dRow5 Then Cells(7, Count).Value = "LONG" ElseIf dRow3 < dRow5 And dRow4 < dRow5 Then Cells(7, Count).Value = "LOSS" Else: MsgBox "row 3 row 5 and row 4 row 5" End If End If End Function "alldreams" wrote: Could you help me get these conditions in VBA code? ':: first condition IF A3 < A5 AND A4 A5, THEN A6 = A4 - A5 AND A7 = LONG. IF A3 < A5 and A4 < A5 THEN A6 = A4 - A5 AND A7 = LOSS IF A3 A5 AND A4 < A5 THEN A7 = NOT LONG ':: second condition IF A7 = null OR LOSS OR NOT LONG THEN first condition for next column, B IF A7 = LONG THEN IF B4 B5, THEN B6 = B4-A5 AND A6 = 0 AND B7 = LONG. IF B4 < B5 THEN B6 = B4-A5 AND A6 = 0 AND B7 = CLOSE LONG. ':: This loops until the last nonblank value in row 1. |
if statement
I forgot to reset DoFirstCon to True after the first Endif.
"Marcotte A" wrote: I didn't test this extensively, and I'm not sure I completely understand what you are trying to accomplish, but this should at the very least get you on the right track. Sub mysub() Dim DoFirstCon As Boolean Dim i As Integer i = 1 DoFirstCon = True Do Until IsEmpty(Cells(3, i)) If DoFirstCon Then FirstCondition (i) If Cells(7, i).Value = "LONG" Then 'don't do firstcondition on column i+1 DoFirstCon = False Cells(6, i + 1).Value = Cells(4, i + 1).Value - Cells(5, i).Value Cells(6, i).Value = 0 If Cells(4, i + 1).Value Cells(5, 1 + 1).Value Then Cells(7, i + 1).Value = "LONG" Else: Cells(7, i + 1).Value = "CLOSE" End If End If i = i + 1 Loop End Sub Function FirstCondition(Count As Integer) Dim dRow3 As Double Dim dRow4 As Double Dim dRow5 As Double dRow3 = Cells(3, Count).Value dRow4 = Cells(4, Count).Value dRow5 = Cells(5, Count).Value MsgBox "loop number " & Count If dRow3 dRow5 And dRow4 < dRow5 Then Cells(7, Count).Value = "NOT LONG" Else Cells(6, Count).Value = dRow4 - dRow5 If dRow3 < dRow5 And dRow4 dRow5 Then Cells(7, Count).Value = "LONG" ElseIf dRow3 < dRow5 And dRow4 < dRow5 Then Cells(7, Count).Value = "LOSS" Else: MsgBox "row 3 row 5 and row 4 row 5" End If End If End Function "alldreams" wrote: Could you help me get these conditions in VBA code? ':: first condition IF A3 < A5 AND A4 A5, THEN A6 = A4 - A5 AND A7 = LONG. IF A3 < A5 and A4 < A5 THEN A6 = A4 - A5 AND A7 = LOSS IF A3 A5 AND A4 < A5 THEN A7 = NOT LONG ':: second condition IF A7 = null OR LOSS OR NOT LONG THEN first condition for next column, B IF A7 = LONG THEN IF B4 B5, THEN B6 = B4-A5 AND A6 = 0 AND B7 = LONG. IF B4 < B5 THEN B6 = B4-A5 AND A6 = 0 AND B7 = CLOSE LONG. ':: This loops until the last nonblank value in row 1. |
if statement
Thank you !
Cheers, JJ -----Original Message----- I forgot to reset DoFirstCon to True after the first Endif. "Marcotte A" wrote: I didn't test this extensively, and I'm not sure I completely understand what you are trying to accomplish, but this should at the very least get you on the right track. Sub mysub() Dim DoFirstCon As Boolean Dim i As Integer i = 1 DoFirstCon = True Do Until IsEmpty(Cells(3, i)) If DoFirstCon Then FirstCondition (i) If Cells(7, i).Value = "LONG" Then 'don't do firstcondition on column i+1 DoFirstCon = False Cells(6, i + 1).Value = Cells(4, i + 1).Value - Cells(5, i).Value Cells(6, i).Value = 0 If Cells(4, i + 1).Value Cells(5, 1 + 1).Value Then Cells(7, i + 1).Value = "LONG" Else: Cells(7, i + 1).Value = "CLOSE" End If End If i = i + 1 Loop End Sub Function FirstCondition(Count As Integer) Dim dRow3 As Double Dim dRow4 As Double Dim dRow5 As Double dRow3 = Cells(3, Count).Value dRow4 = Cells(4, Count).Value dRow5 = Cells(5, Count).Value MsgBox "loop number " & Count If dRow3 dRow5 And dRow4 < dRow5 Then Cells(7, Count).Value = "NOT LONG" Else Cells(6, Count).Value = dRow4 - dRow5 If dRow3 < dRow5 And dRow4 dRow5 Then Cells(7, Count).Value = "LONG" ElseIf dRow3 < dRow5 And dRow4 < dRow5 Then Cells(7, Count).Value = "LOSS" Else: MsgBox "row 3 row 5 and row 4 row 5" End If End If End Function "alldreams" wrote: Could you help me get these conditions in VBA code? ':: first condition IF A3 < A5 AND A4 A5, THEN A6 = A4 - A5 AND A7 = LONG. IF A3 < A5 and A4 < A5 THEN A6 = A4 - A5 AND A7 = LOSS IF A3 A5 AND A4 < A5 THEN A7 = NOT LONG ':: second condition IF A7 = null OR LOSS OR NOT LONG THEN first condition for next column, B IF A7 = LONG THEN IF B4 B5, THEN B6 = B4-A5 AND A6 = 0 AND B7 = LONG. IF B4 < B5 THEN B6 = B4-A5 AND A6 = 0 AND B7 = CLOSE LONG. ':: This loops until the last nonblank value in row 1. . |
All times are GMT +1. The time now is 10:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com