Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a cell even if its contents are moved moved/replaced | Excel Discussion (Misc queries) | |||
worksheet to be moved | Excel Discussion (Misc queries) | |||
I moved a file from work to home and now my color macro does not w | Excel Worksheet Functions | |||
Maintaining a hyperlink to moved data within the work book | Excel Discussion (Misc queries) | |||
When I moved data from one workbook to another... | Excel Programming |