.cells reference?
 

Hi I am trying to find when a value crosses above or below zero in a table
for multiple test objects... my code does not return any crosses... when
column "B" should find one

Sub Scorecross()
Sheets("Scores").Select
lastticker = Range("b2").End(xlToRight).Address
lstcol = Range("b2").End(xlToRight).Column
todayrow = Range("a2").End(xlDown).Row
For Each i In Sheets("Scores").Range("b2", lastticker)
If Cells(i.Column, todayrow) < 0 And Cells(i.Column, todayrow - 1) 0
Then
nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nx****ch).Offset(1, 0) =
Sheets("scores").Cells(i.Column, 2)
Sheets("watch list").Range(nx****ch).Offset(1, 1) =
Sheets("scores").Cells(i.Column, todayrow)
Sheets("watch list").Range(nx****ch).Offset(1, 2) = "Cross UP"
Sheets("watch list").Range(nx****ch).Offset(1, 3) =
Sheets("Scores").Range("a" & todayrow).Value
End If
If Cells(i.Column & todayrow) < 0 And Cells(i.Column & todayrow - 1) < 0
Then
nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nx****ch).Offset(1, 0) =
Sheets("scores").Cells(i.Column, 2)
Sheets("watch list").Range(nx****ch).Offset(1, 1) =
Sheets("scores").Cells(i.Column, todayrow)
Sheets("watch list").Range(nx****ch).Offset(1, 2) = "Cross DOWN"
Sheets("watch list").Range(nx****ch).Offset(1, 3) =
Sheets("Scores").Range("a" & todayrow).Value
End If
Next
'Call scoreEmail
Sheets("watch list").Select
End Sub


Thanks for any help!

.cells reference?
 

I had the row and column reversed :)

"John" wrote:

Hi I am trying to find when a value crosses above or below zero in a table
for multiple test objects... my code does not return any crosses... when
column "B" should find one

Sub Scorecross()
Sheets("Scores").Select
lastticker = Range("b2").End(xlToRight).Address
lstcol = Range("b2").End(xlToRight).Column
todayrow = Range("a2").End(xlDown).Row
For Each i In Sheets("Scores").Range("b2", lastticker)
If Cells(i.Column, todayrow) < 0 And Cells(i.Column, todayrow - 1) 0
Then
nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nx****ch).Offset(1, 0) =
Sheets("scores").Cells(i.Column, 2)
Sheets("watch list").Range(nx****ch).Offset(1, 1) =
Sheets("scores").Cells(i.Column, todayrow)
Sheets("watch list").Range(nx****ch).Offset(1, 2) = "Cross UP"
Sheets("watch list").Range(nx****ch).Offset(1, 3) =
Sheets("Scores").Range("a" & todayrow).Value
End If
If Cells(i.Column & todayrow) < 0 And Cells(i.Column & todayrow - 1) < 0
Then
nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nx****ch).Offset(1, 0) =
Sheets("scores").Cells(i.Column, 2)
Sheets("watch list").Range(nx****ch).Offset(1, 1) =
Sheets("scores").Cells(i.Column, todayrow)
Sheets("watch list").Range(nx****ch).Offset(1, 2) = "Cross DOWN"
Sheets("watch list").Range(nx****ch).Offset(1, 3) =
Sheets("Scores").Range("a" & todayrow).Value
End If
Next
'Call scoreEmail
Sheets("watch list").Select
End Sub


Thanks for any help!