| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable error on second use???
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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
quotemarks missing: Range(nex****ch)
put OPTION EXPLICIT at the top of the module. "John" wrote: 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
nope - I was wrong. sorry
"John" wrote: 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
chaneg this
nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) to dim nx****ch as range SET nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Offset(1) nx****ch.Value = Sheets("scores").Cells( i.Row,"A") "John" wrote: 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
What is the error message?
"John" wrote in message ... 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
oh my I had nx****ch in one place and nex****ch in another....
I think your works too... thanks "Patrick Molloy" wrote: chaneg this nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) to dim nx****ch as range SET nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Offset(1) nx****ch.Value = Sheets("scores").Cells( i.Row,"A") "John" wrote: 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
oops ;)
"John" wrote: oh my I had nx****ch in one place and nex****ch in another.... I think your works too... thanks "Patrick Molloy" wrote: chaneg this nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) to dim nx****ch as range SET nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Offset(1) nx****ch.Value = Sheets("scores").Cells( i.Row,"A") "John" wrote: 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable error on second use???
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. "John" wrote in message ... oh my I had nx****ch in one place and nex****ch in another.... I think your works too... thanks "Patrick Molloy" wrote: chaneg this nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) to dim nx****ch as range SET nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Offset(1) nx****ch.Value = Sheets("scores").Cells( i.Row,"A") "John" wrote: 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(nex****ch).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 nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross UP" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If If Range("c" & i.Row) 0 And Range("b" & i.Row) < 0 Then nx****ch = Sheets("watch list").Range("a4000").End(xlUp).Address Sheets("watch list").Range(nx****ch).Offset(1, 0) = Sheets("scores").Range("a" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 1) = Sheets("scores").Range("b" & i.Row) Sheets("watch list").Range(nex****ch).Offset(1, 2) = "Cross DOWN" Sheets("watch list").Range(nex****ch).Offset(1, 3) = Sheets("Scores").Range("b1") End If Next End Sub Thanks for help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime Error 91 Object variable or With block variable not set. | Excel Programming | |||
| Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
| Getting inconsistent Error 91-Object variable or With block variable not set | Excel Programming | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Linear Mode
