Doesn't work when moved.
 

This sub was working just fine when the code was in the
workbook that contained the data. Now that I moved it
and use the "GetOpenFile" function to get the data sheet,
I get Run Time Error (1004) on the line beginning with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range, endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer = Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is = "
& answer
End With
End If
End With
End Sub

Doesn't work when moved.
 

The unqualified Range in the troublesome statement refers to the worksheet
containing the code. You need to qualify it.

Set MRange = ActiveSheet.Range(MyRange, endR)

You may run into similar problems further down in your code - I can't say as
I didn't look.

--
Regards,
Tom Ogilvy


"Lee Hunter" wrote in message
...
This sub was working just fine when the code was in the
workbook that contained the data. Now that I moved it
and use the "GetOpenFile" function to get the data sheet,
I get Run Time Error (1004) on the line beginning with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range, endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer = Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is = "
& answer
End With
End If
End With
End Sub

Doesn't work when moved.
 

you also need to qualify this:

Set MyRange = Range("a7")

to

Set MyRange = ActiveSheet.Range("a7")

--
Regards,
Tom Ogilvy

"Lee Hunter" wrote in message
...
This sub was working just fine when the code was in the
workbook that contained the data. Now that I moved it
and use the "GetOpenFile" function to get the data sheet,
I get Run Time Error (1004) on the line beginning with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range, endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer = Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is = "
& answer
End With
End If
End With
End Sub

Doesn't work when moved.
 

Thanks Tom,

Made those two correction, verified that Mrange has
value "$A$7:$a$67", but then the next statement

With Worksheets("No Stock Log-Daily").Range(MRange)

provides the same error (1004.

Can you explain what happens from a reference perspective
when the workbook being acted on is not the workbook that
has the code? If I could understand that, I can probably
make the necessary correction myself.

Thanks again for your time and trouble.
-----Original Message-----
you also need to qualify this:

Set MyRange = Range("a7")

to

Set MyRange = ActiveSheet.Range("a7")

--
Regards,
Tom Ogilvy

"Lee Hunter" wrote
in message
...
This sub was working just fine when the code was in the
workbook that contained the data. Now that I moved it
and use the "GetOpenFile" function to get the data
sheet,
I get Run Time Error (1004) on the line beginning with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range,
endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer =
Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is
= "
& answer
End With
End If
End With
End Sub



.

Doesn't work when moved.
 

If you moved all your code to a general module and just called it from the
event, it would probably work. In a general module, an unqualified object
like range would refer to the activesheet. In a sheet module, an
unqualified Range is the same as Me.Range so it refers to the sheet with
the code.

But that is not your primary problem here. MRange is a reference to a
range, not a string. So Range(MRange) would cause a problem anywhere.

Try

With Worksheets("blah blah").Range(MRange.Address)

I looked through your code and added some qualifications. See if this will
run:

Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range, endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = worksheets("No Stock Log-Daily").Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell

Set MRange = ActiveSheet.Range(MyRange, endR)



With Worksheets("No Stock Log-Daily") _
.Range(mrange.address)
Set c = .Find("wave", LookIn:=xlValues, _
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = .parent.Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range _
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = .Parent.Range(startR, MRange)
answer = Application.WorksheetFunction.SumIf _
(MyRange, "0")
MsgBox "The number of No Stock events is = " _
& answer
End With
End If
End With
End Sub



--
Regards,
Tom Ogilvy

"Lee Hunter" wrote in message
...
Thanks Tom,

Made those two correction, verified that Mrange has
value "$A$7:$a$67", but then the next statement

With Worksheets("No Stock Log-Daily").Range(MRange)

provides the same error (1004.

Can you explain what happens from a reference perspective
when the workbook being acted on is not the workbook that
has the code? If I could understand that, I can probably
make the necessary correction myself.

Thanks again for your time and trouble.
-----Original Message-----
you also need to qualify this:

Set MyRange = Range("a7")

to

Set MyRange = ActiveSheet.Range("a7")

--
Regards,
Tom Ogilvy

"Lee Hunter" wrote
in message
...
This sub was working just fine when the code was in the
workbook that contained the data. Now that I moved it
and use the "GetOpenFile" function to get the data
sheet,
I get Run Time Error (1004) on the line beginning with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range,
endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer =
Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is
= "
& answer
End With
End If
End With
End Sub



.

Doesn't work when moved.
 

Tom,

You're a wizard! Thanks so much. This thing has been
driving me crazy.

Just one last thing. As you can see, this sub is part of
a taxt box change event. How do I return the "answer"
back to that text box?

As easy as Me.textbox1.value = answer? or do I have to
save the reference on entry to the sub?

Just want you t knw that you're my hero.
-----Original Message-----
If you moved all your code to a general module and just
called it from the
event, it would probably work. In a general module, an
unqualified object
like range would refer to the activesheet. In a sheet
module, an
unqualified Range is the same as Me.Range so it refers
to the sheet with
the code.

But that is not your primary problem here. MRange is a
reference to a
range, not a string. So Range(MRange) would cause a
problem anywhere.

Try

With Worksheets("blah blah").Range(MRange.Address)

I looked through your code and added some
qualifications. See if this will
run:

Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range, endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = worksheets("No Stock Log-Daily").Range
("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell

Set MRange = ActiveSheet.Range(MyRange, endR)



With Worksheets("No Stock Log-Daily") _
.Range(mrange.address)
Set c = .Find("wave", LookIn:=xlValues, _
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = .parent.Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range _
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = .Parent.Range(startR, MRange)
answer = Application.WorksheetFunction.SumIf
_
(MyRange, "0")
MsgBox "The number of No Stock events is = "
_
& answer
End With
End If
End With
End Sub



--
Regards,
Tom Ogilvy

"Lee Hunter" wrote
in message
...
Thanks Tom,

Made those two correction, verified that Mrange has
value "$A$7:$a$67", but then the next statement

With Worksheets("No Stock Log-Daily").Range(MRange)

provides the same error (1004.

Can you explain what happens from a reference
perspective
when the workbook being acted on is not the workbook
that
has the code? If I could understand that, I can
probably
make the necessary correction myself.

Thanks again for your time and trouble.
-----Original Message-----
you also need to qualify this:

Set MyRange = Range("a7")

to

Set MyRange = ActiveSheet.Range("a7")

--
Regards,
Tom Ogilvy

"Lee Hunter"
wrote
in message
...
This sub was working just fine when the code was in
the
workbook that contained the data. Now that I moved
it
and use the "GetOpenFile" function to get the data
sheet,
I get Run Time Error (1004) on the line beginning
with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range,
endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename
(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a
file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range
("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range
(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer =
Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is
= "
& answer
End With
End If
End With
End Sub



.



.

Doesn't work when moved.
 

I would think

Me.textbox1.value = answer

I don't think this triggers another change event, but you might want to put
a msgbox in the routine to test it. You do know that change fires on every
character typed into the textbox, however - so you may want to use another
event - depends on what your functionality is.



--
Regards,
Tom Ogilvy

"Lee Hunter" wrote in message
...
Tom,

You're a wizard! Thanks so much. This thing has been
driving me crazy.

Just one last thing. As you can see, this sub is part of
a taxt box change event. How do I return the "answer"
back to that text box?

As easy as Me.textbox1.value = answer? or do I have to
save the reference on entry to the sub?

Just want you t knw that you're my hero.
-----Original Message-----
If you moved all your code to a general module and just
called it from the
event, it would probably work. In a general module, an
unqualified object
like range would refer to the activesheet. In a sheet
module, an
unqualified Range is the same as Me.Range so it refers
to the sheet with
the code.

But that is not your primary problem here. MRange is a
reference to a
range, not a string. So Range(MRange) would cause a
problem anywhere.

Try

With Worksheets("blah blah").Range(MRange.Address)

I looked through your code and added some
qualifications. See if this will
run:

Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range, endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range("a7").Activate
Set MyRange = worksheets("No Stock Log-Daily").Range
("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell

Set MRange = ActiveSheet.Range(MyRange, endR)



With Worksheets("No Stock Log-Daily") _
.Range(mrange.address)
Set c = .Find("wave", LookIn:=xlValues, _
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = .parent.Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range _
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = .Parent.Range(startR, MRange)
answer = Application.WorksheetFunction.SumIf
_
(MyRange, "0")
MsgBox "The number of No Stock events is = "
_
& answer
End With
End If
End With
End Sub



--
Regards,
Tom Ogilvy

"Lee Hunter" wrote
in message
...
Thanks Tom,

Made those two correction, verified that Mrange has
value "$A$7:$a$67", but then the next statement

With Worksheets("No Stock Log-Daily").Range(MRange)

provides the same error (1004.

Can you explain what happens from a reference
perspective
when the workbook being acted on is not the workbook
that
has the code? If I could understand that, I can
probably
make the necessary correction myself.

Thanks again for your time and trouble.
-----Original Message-----
you also need to qualify this:

Set MyRange = Range("a7")

to

Set MyRange = ActiveSheet.Range("a7")

--
Regards,
Tom Ogilvy

"Lee Hunter"
wrote
in message
...
This sub was working just fine when the code was in
the
workbook that contained the data. Now that I moved
it
and use the "GetOpenFile" function to get the data
sheet,
I get Run Time Error (1004) on the line beginning
with
*********.

Does anyone see the problem?

Thanks for all your help.




Private Sub TextBox1_Change()
Dim MyRange As Range, answer, mess, startR As Range,
endR
As Range, MRange As Range
NewFN = Application.GetOpenFilename
(FileFilter:="Excel
Files (*.xls), *.xls", Title:="Please select a
file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
Worksheets("No Stock Log-Daily").Range
("a7").Activate
Set MyRange = Range("a7")
answer = 0
Do Until answer = 1
If IsEmpty(ActiveCell.Value) Then
answer = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Set endR = ActiveCell


*********** Set MRange = Range(MyRange, endR)******



With Worksheets("No Stock Log-Daily").Range
(MRange)
Set c = .Find("wave", LookIn:=xlValues,
LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
d = c.Row
d = d - 1
Set startR = Range("L1")
Set startR = startR.Offset(d, 0)
Set endR = endR.Offset(0, 11)
With Worksheets("No Stock Log-Daily").Range
(startR, endR)
Set MRange = startR
Do While MRange.Row < endR.Row
If MRange.MergeCells Then
MRange.MergeArea.UnMerge
End If
Set MRange = MRange.Offset(1, 0)
Loop
Set MyRange = Range(startR, MRange)
answer =
Application.WorksheetFunction.SumIf
(MyRange, "0")
MsgBox "The number of No Stock events is
= "
& answer
End With
End If
End With
End Sub



.



.