Simple IF give me nothing?
 

Hi the following code runs just fine and gives me All my data...
Sub backtest()
Application.Calculation = xlCalculationManual
counter = 0
lastcell = ThisWorkbook.Sheets("backtest").Range("a4000").End (xlUp).Address
nxtday = ThisWorkbook.Sheets("P&L").Range("a4000").End(xlUp ).Row + 1
For Each i In ThisWorkbook.Sheets("backtest").Range("a4", lastcell)
rw = i.Row

Sheets("P&L").Range("a" & nxtday + counter) =
Sheets("backtest").Range("b" & rw)
Sheets("P&L").Range("b" & nxtday + counter) =
Sheets("backtest").Range("H" & rw)

counter = counter + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub


When I add the IF statement in the middle it returns nothing...
Sub backtest()
Application.Calculation = xlCalculationManual
counter = 0
lastcell = ThisWorkbook.Sheets("backtest").Range("a4000").End (xlUp).Address
nxtday = ThisWorkbook.Sheets("P&L").Range("a4000").End(xlUp ).Row + 1
For Each i In ThisWorkbook.Sheets("backtest").Range("a4", lastcell)
rw = i.Row
If Sheets("backtest").Range("H" & rw) < 0 Then
Sheets("P&L").Range("a" & nxtday + counter) =
Sheets("backtest").Range("b" & rw)
Sheets("P&L").Range("b" & nxtday + counter) =
Sheets("backtest").Range("H" & rw)
End If
counter = counter + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Column H has 0 and non zero values and I only want the non 0s

Also of note... If I run the code with the IF statement, then delete it the
code does not work and i have to close the file and reopen it... something
wrong in calculation enablement? I don't recieve any errors

Thanks

Simple IF give me nothing?
 

try ...
If Sheets("Sheet2").Range("H" & rw).Value < 0 Then
AND
I think you want the counter statement (counter = counter + 1) BEFORE the
'End If' statement.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"John" wrote:

Hi the following code runs just fine and gives me All my data...
Sub backtest()
Application.Calculation = xlCalculationManual
counter = 0
lastcell = ThisWorkbook.Sheets("backtest").Range("a4000").End (xlUp).Address
nxtday = ThisWorkbook.Sheets("P&L").Range("a4000").End(xlUp ).Row + 1
For Each i In ThisWorkbook.Sheets("backtest").Range("a4", lastcell)
rw = i.Row

Sheets("P&L").Range("a" & nxtday + counter) =
Sheets("backtest").Range("b" & rw)
Sheets("P&L").Range("b" & nxtday + counter) =
Sheets("backtest").Range("H" & rw)

counter = counter + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub


When I add the IF statement in the middle it returns nothing...
Sub backtest()
Application.Calculation = xlCalculationManual
counter = 0
lastcell = ThisWorkbook.Sheets("backtest").Range("a4000").End (xlUp).Address
nxtday = ThisWorkbook.Sheets("P&L").Range("a4000").End(xlUp ).Row + 1
For Each i In ThisWorkbook.Sheets("backtest").Range("a4", lastcell)
rw = i.Row
If Sheets("backtest").Range("H" & rw) < 0 Then
Sheets("P&L").Range("a" & nxtday + counter) =
Sheets("backtest").Range("b" & rw)
Sheets("P&L").Range("b" & nxtday + counter) =
Sheets("backtest").Range("H" & rw)
End If
counter = counter + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Column H has 0 and non zero values and I only want the non 0s

Also of note... If I run the code with the IF statement, then delete it the
code does not work and i have to close the file and reopen it... something
wrong in calculation enablement? I don't recieve any errors

Thanks

Simple IF give me nothing?
 

If Sheets("Sheet5").Range("H" & rw).Value < 0 Then

gives me a subscript out of range

If Sheets("backtest").Range("H" & rw).Value < 0 Then

pulled two of the values one time and 4 the next, and then none.... I think
my sheet is too big and is corrupted

"Gary Brown" wrote:

try ...
If Sheets("Sheet2").Range("H" & rw).Value < 0 Then
AND
I think you want the counter statement (counter = counter + 1) BEFORE the
'End If' statement.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"John" wrote:

Hi the following code runs just fine and gives me All my data...
Sub backtest()
Application.Calculation = xlCalculationManual
counter = 0
lastcell = ThisWorkbook.Sheets("backtest").Range("a4000").End (xlUp).Address
nxtday = ThisWorkbook.Sheets("P&L").Range("a4000").End(xlUp ).Row + 1
For Each i In ThisWorkbook.Sheets("backtest").Range("a4", lastcell)
rw = i.Row

Sheets("P&L").Range("a" & nxtday + counter) =
Sheets("backtest").Range("b" & rw)
Sheets("P&L").Range("b" & nxtday + counter) =
Sheets("backtest").Range("H" & rw)

counter = counter + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub


When I add the IF statement in the middle it returns nothing...
Sub backtest()
Application.Calculation = xlCalculationManual
counter = 0
lastcell = ThisWorkbook.Sheets("backtest").Range("a4000").End (xlUp).Address
nxtday = ThisWorkbook.Sheets("P&L").Range("a4000").End(xlUp ).Row + 1
For Each i In ThisWorkbook.Sheets("backtest").Range("a4", lastcell)
rw = i.Row
If Sheets("backtest").Range("H" & rw) < 0 Then
Sheets("P&L").Range("a" & nxtday + counter) =
Sheets("backtest").Range("b" & rw)
Sheets("P&L").Range("b" & nxtday + counter) =
Sheets("backtest").Range("H" & rw)
End If
counter = counter + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Column H has 0 and non zero values and I only want the non 0s

Also of note... If I run the code with the IF statement, then delete it the
code does not work and i have to close the file and reopen it... something
wrong in calculation enablement? I don't recieve any errors

Thanks