| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| returning back to loop check condition without completing the loop | Excel Programming | |||
| Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Hybrid Mode
