Simple IF give me nothing?

J

John

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
 
G

Gary Brown

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.
 
J

John

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top