ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   loop with many if (https://www.excelbanter.com/excel-programming/433835-loop-many-if.html)

Tim
loop with many if
 
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 doesnt work. Tried also some Elseif statements with no success.
Any Help is Highly Appreciated.

Jacob Skaria
loop with many if
 
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
---------------
Jacob Skaria


"Tim" wrote:

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 doesnt work. Tried also some Elseif statements with no success.
Any Help is Highly Appreciated.

Rick Rothstein
loop with many if
 
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

--
Rick (MVP - Excel)


"Tim" wrote in message
...
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 doesnt work. Tried also some Elseif statements with no success.
Any Help is Highly Appreciated.

Tim
loop with many if
 

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




"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Tim" wrote:

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 doesnt work. Tried also some Elseif statements with no success.
Any Help is Highly Appreciated.

Jacob Skaria
loop with many if
 
Tim, you are most welcome!

If this post helps click Yes
---------------
Jacob Skaria


"Tim" wrote:


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




"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Tim" wrote:

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 doesnt work. Tried also some Elseif statements with no success.
Any Help is Highly Appreciated.


All times are GMT +1. The time now is 10:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com