loop with many if

[Tim]

Hi ALL,


Sub addnumbers()

Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim i As Integer, j As Integer
Dim numTrades As Integer, Amt As Double, StartDate As Date


numTrades = Range("X1")



i = 1
For j = 1 To numTrades
If Cells(j + 3, 23) <> "" And Cells(j + 3, 27) = "" Then

Cells(j + 3, 2) = Cells(i + 3, 31)

i = i + 1

End If
Next j
Application.Calculation = xlAutomatic

End Sub


I want to add additional IF STATEMENTS to the above macro:

After the line : If Cells(j + 3, 23) <> "" And Cells(j + 3, 27) = "" Then
I need to add the following 2 if conditions:
1. If Cells(j + 3, 25) = "S"
Cells(j + 3, 2) = Cells(i + 3, 31)

and 2. If Cells(j + 3, 25) = "L"
Cells(j + 3, 4) = Cells(i + 3, 31)

The final code should be something like:
i = 1
For j = 1 To numTrades ' go thru all trades
If Cells(j + 3, 23) <> "" And Cells(j + 3, 27) = "" Then
If Cells(j + 3, 25) = "S" Then
Cells(j + 3, 2) = Cells(i + 3, 31)
If Cells(j + 3, 25) = "L" Then
Cells(j + 3, 4) = Cells(i + 3, 31)
i = i + 1
End If
End If
End If
Next j

but it doesn’t work. Tried also some Elseif statements with no success.
Any Help is Highly Appreciated.

 

[Jacob Skaria]

Try the below...I am not sure where i is getting incremented.Review and place
that as required...

i = 1
For j = 1 To numTrades ' go thru all trades
If Cells(j + 3, 23) <> "" And Cells(j + 3, 27) = "" Then
Select Case UCase(Cells(j + 3, 25))
Case "S"
Cells(j + 3, 2) = Cells(i + 3, 31)
Case "L"
Cells(j + 3, 4) = Cells(i + 3, 31)
End Select
i = i + 1
End If
Next j

OR

i = 1
For j = 1 To numTrades ' go thru all trades
If Cells(j + 3, 23) <> "" And Cells(j + 3, 27) = "" Then
If Cells(j + 3, 25) = "S" Then
Cells(j + 3, 2) = Cells(i + 3, 31)
ElseIf Cells(j + 3, 25) = "L" Then
Cells(j + 3, 4) = Cells(i + 3, 31)
End If
i = i + 1
End If
Next j



If this post helps click Yes

 

[Rick Rothstein]

I don't see why you are tracking the "i" variable since it appears to
iterate the same as the "j" variable, so I simply replaced the "i" variables
with the "j" variable. On top of that, I think you are looking for this
construction...

For j = 1 To numTrades
If Cells(j + 3, 23) <> "" And Cells(j + 3, 27) = "" Then
If Cells(j + 3, 25) = "S" Then
Cells(j + 3, 2) = Cells(j + 3, 31)
ElseIf Cells(j + 3, 25) = "L" Then
Cells(j + 3, 4) = Cells(j + 3, 31)
End If
End If
Next j

 

[Tim]

Your both suggestions work great.
Thank you Jacob!!!

 

[Jacob Skaria]

Tim, you are most welcome!

If this post helps click Yes