Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
I'm using Excel 2000, and trying to modify a range name within VBA. When I
try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
Hello
How about the sheet name? HTH Cordially Pascal "BigJimmer" a écrit dans le message de news: ... I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
Sub ABC()
Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
I tried it before with and without the sheet name, and had gotten the same
error. "papou" wrote: Hello How about the sheet name? HTH Cordially Pascal "BigJimmer" a écrit dans le message de news: ... I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
The 1004 error will occur if MyArray is a size of 1. UBound(MyArray) - 1
will equal zero which will produce this error. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
More important would be the value of Ubound
in VBA, you can have Sub abcd() Dim myArray(5 To 5) MsgBox UBound(myArray) End Sub which is of size 1, but doesn't cause a problem. But I know what you mean <g -- Regards, Tom Ogilvy "Joel" wrote: The 1004 error will occur if MyArray is a size of 1. UBound(MyArray) - 1 will equal zero which will produce this error. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
I am successful in running the provided routine directly from a standard
module, and even from a worksheet module. When I attempt to call the same code from ThisWorkbook.Workbook_SheetChange event, I get the application error. I'm using the version provided that specifies the worksheet. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
There is no sheet reference. Notice the periods in front of Range and the
two Cells. Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) with sheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) end with End Sub "BigJimmer" wrote: I am successful in running the provided routine directly from a standard module, and even from a worksheet module. When I attempt to call the same code from ThisWorkbook.Workbook_SheetChange event, I get the application error. I'm using the version provided that specifies the worksheet. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
Yes, I saw those. I used the code that Tom provided without an issue until
I tried to reference it from ThisWorkbook.Workbook_SheetChange. There is something about it being referenced from this module that is a problem, or maybe it's just me, as even this simple hard coded logic withing this module generates the same error - ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8") "Joel" wrote: There is no sheet reference. Notice the periods in front of Range and the two Cells. Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) with sheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) end with End Sub "BigJimmer" wrote: I am successful in running the provided routine directly from a standard module, and even from a worksheet module. When I attempt to call the same code from ThisWorkbook.Workbook_SheetChange event, I get the application error. I'm using the version provided that specifies the worksheet. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
Using excel 2003 I put the following code into this wrokbook and dcid not get
any error. I tested the code by putting c hanges into sheet 1, 2, & 3. Try openning a new workbook (close all excel workbooks) and run this code. I bet you don't get an error. Do you have any protections set in your workbook? Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Source As Range) ' runs when a sheet is changed ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8") End Sub "BigJimmer" wrote: Yes, I saw those. I used the code that Tom provided without an issue until I tried to reference it from ThisWorkbook.Workbook_SheetChange. There is something about it being referenced from this module that is a problem, or maybe it's just me, as even this simple hard coded logic withing this module generates the same error - ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8") "Joel" wrote: There is no sheet reference. Notice the periods in front of Range and the two Cells. Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) with sheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) end with End Sub "BigJimmer" wrote: I am successful in running the provided routine directly from a standard module, and even from a worksheet module. When I attempt to call the same code from ThisWorkbook.Workbook_SheetChange event, I get the application error. I'm using the version provided that specifies the worksheet. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
range object error
As you expected, when I copied the code to a new workbook, it worked fine.
There is no protection on the existing document yet, as that is the last step I was going to do. Any idea as to why this doesn't work in the existing workbook? Thanks! "Joel" wrote: Using excel 2003 I put the following code into this wrokbook and dcid not get any error. I tested the code by putting c hanges into sheet 1, 2, & 3. Try openning a new workbook (close all excel workbooks) and run this code. I bet you don't get an error. Do you have any protections set in your workbook? Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Source As Range) ' runs when a sheet is changed ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8") End Sub "BigJimmer" wrote: Yes, I saw those. I used the code that Tom provided without an issue until I tried to reference it from ThisWorkbook.Workbook_SheetChange. There is something about it being referenced from this module that is a problem, or maybe it's just me, as even this simple hard coded logic withing this module generates the same error - ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Rang e("$B$5:$E$8") "Joel" wrote: There is no sheet reference. Notice the periods in front of Range and the two Cells. Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) with sheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) end with End Sub "BigJimmer" wrote: I am successful in running the provided routine directly from a standard module, and even from a worksheet module. When I attempt to call the same code from ThisWorkbook.Workbook_SheetChange event, I get the application error. I'm using the version provided that specifies the worksheet. "Tom Ogilvy" wrote: Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=Range(Cells(Row, col), _ Cells(Row + UBound(MyArray) - 1, col + 3)) End Sub In a general module worked fine for me. If it is in a sheet module then you need to use something like Sub ABC() Row = 3 col = 12 MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) With worksheets("Sheet1") ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:=.Range(.Cells(Row, col), _ .Cells(Row + UBound(MyArray) - 1, col + 3)) End With End Sub so you are sure all range references are on the same page. Otherwise, make sure your references form a legal address. -- regards, Tom Ogilvy "BigJimmer" wrote: I'm using Excel 2000, and trying to modify a range name within VBA. When I try code such as this - Dim row, col as integer row = 1 col = 1 ActiveWorkbook.Names.Add Name:="WHData", _ RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col + 3)) I get 1004 Application-defined or object-defined error. I have confirmed that UBound(MyArray) is defined, and in my code I have logic that changes the values of row and col. To test, I used the following, and did not get this error - ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5") What am I missing to be able to use cells(rwindex, colindex) to set the range? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
STUPID range object error | Excel Programming | |||
Range Object error | Excel Programming | |||
peculiar error with Range() object | Excel Programming | |||
error 1004 Range object | Excel Programming | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming |