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!