Variable error on second use???

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi I am checking to see if a "score" crosses above 0 and if so moving its
value to a second sheet. I get an error on Sheets("watch
list").Range(nextwatch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row)...
which I can't figure since the line right before this one works....

Sub scorecross()

For Each i In Sheets("Scores").Range("b2:b501")
If Range("c" & i.Row) < 0 And Range("b" & i.Row) > 0 Then
nxtwatch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nxtwatch).Offset(1, 0) =
Sheets("scores").Range("a" & i.Row)
Sheets("watch list").Range(nextwatch).Offset(1, 1) =
Sheets("scores").Range("b" & i.Row)
Sheets("watch list").Range(nextwatch).Offset(1, 2) = "Cross UP"
Sheets("watch list").Range(nextwatch).Offset(1, 3) =
Sheets("Scores").Range("b1")
End If
If Range("c" & i.Row) > 0 And Range("b" & i.Row) < 0 Then
nxtwatch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nxtwatch).Offset(1, 0) =
Sheets("scores").Range("a" & i.Row)
Sheets("watch list").Range(nextwatch).Offset(1, 1) =
Sheets("scores").Range("b" & i.Row)
Sheets("watch list").Range(nextwatch).Offset(1, 2) = "Cross DOWN"
Sheets("watch list").Range(nextwatch).Offset(1, 3) =
Sheets("Scores").Range("b1")
End If
Next
End Sub

Thanks for help!
 
quotemarks missing: Range(nextwatch)

put
OPTION EXPLICIT
at the top of the module.
 
chaneg this

nxtwatch = Sheets("watch list").Range("a4000").End(xlUp).Address
Sheets("watch list").Range(nxtwatch).Offset(1, 0) =
Sheets("scores").Range("a" & i.Row)

to

dim nxtwatch as range
SET nxtwatch = Sheets("watch list").Range("a4000").End(xlUp).Offset(1)
nxtwatch.Value = Sheets("scores").Cells( i.Row,"A")
 
oh my I had nxtwatch in one place and nextwatch in another....

I think your works too... thanks
 
You will get faster, better response for problem solving if you include the
text of your error messages when you post. This gives those who try to help
a clue as to the problem and reduces the amount of reading or testing
testing that might be required to debug the code.
 
Back
Top