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.

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.

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.

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.

.