Doesn't work when moved.

[Lee Hunter]

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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

you also need to qualify this:

Set MyRange = Range("a7")

to

Set MyRange = ActiveSheet.Range("a7")

 

[Lee Hunter]

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.

 

[Tom Ogilvy]

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

 

[Lee Hunter]

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

 

[Tom Ogilvy]

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.