I'm using a routine to retreive data from a SQL Server database and then
output the results to a worksheet. MaterialTbl is a variant array.

The issue is I get a "Number stored as text" error when I use:

Set DestRange = Range(Worksheets(ThisSheet).Cells(3, 3), _
Worksheets(ThisSheet).Cells(UBound(MaterialTbl, 1), _
3 + UBound(MaterialTbl, 2) - 1))

DestRange.Value = MaterialTbl

But I don't get the error if I use:

For i = 0 To UBound(MaterialTbl, 1)
For j = 1 To UBound(MaterialTbl, 2)
Worksheets(ThisSheet).Cells(3+ i, 3 + j - 1).Value = MaterialTbl(i, j)
Next j
Next i

Why would I get an error with the first method and not get an error with the
latter?

Thanks in advance,
Raul

Without looking too closely at exactly what you are trying to do there is an
obvious mistake in the first code. Range() requires 1 argument and can take
an optional second argument. You have supplied 3 arguments. It has no idea
what to do with 3 arguments.
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm using a routine to retreive data from a SQL Server database and then
output the results to a worksheet. MaterialTbl is a variant array.

The issue is I get a "Number stored as text" error when I use:

Set DestRange = Range(Worksheets(ThisSheet).Cells(3, 3), _
Worksheets(ThisSheet).Cells(UBound(MaterialTbl, 1), _
3 + UBound(MaterialTbl, 2) - 1))

DestRange.Value = MaterialTbl

But I don't get the error if I use:

For i = 0 To UBound(MaterialTbl, 1)
For j = 1 To UBound(MaterialTbl, 2)
Worksheets(ThisSheet).Cells(3+ i, 3 + j - 1).Value = MaterialTbl(i, j)
Next j
Next i

Why would I get an error with the first method and not get an error with the
latter?

Thanks in advance,
Raul

I'm pretty sure the range() is working and two arguments are being passed
(all open parentheses and close parentheses make it very messy).

The specified range is being populated, but I'm getting a green triangle in
the upper left corner of each cell flagging the "Number Stored as Text" error
when I use the range() method.

By the way I have used the range method in a number of other routines
without running into this issue.

Thanks,
Raul

"Jim Thomlinson" wrote:

Without looking too closely at exactly what you are trying to do there is an
obvious mistake in the first code. Range() requires 1 argument and can take
an optional second argument. You have supplied 3 arguments. It has no idea
what to do with 3 arguments.
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm using a routine to retreive data from a SQL Server database and then
output the results to a worksheet. MaterialTbl is a variant array.

The issue is I get a "Number stored as text" error when I use:

Set DestRange = Range(Worksheets(ThisSheet).Cells(3, 3), _
Worksheets(ThisSheet).Cells(UBound(MaterialTbl, 1), _
3 + UBound(MaterialTbl, 2) - 1))

DestRange.Value = MaterialTbl

But I don't get the error if I use:

For i = 0 To UBound(MaterialTbl, 1)
For j = 1 To UBound(MaterialTbl, 2)
Worksheets(ThisSheet).Cells(3+ i, 3 + j - 1).Value = MaterialTbl(i, j)
Next j
Next i

Why would I get an error with the first method and not get an error with the
latter?

Thanks in advance,
Raul

Sorry about that...

What are the values of
UBound(MaterialTbl, 1)
3 + UBound(MaterialTbl, 2) - 1
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm pretty sure the range() is working and two arguments are being passed
(all open parentheses and close parentheses make it very messy).

The specified range is being populated, but I'm getting a green triangle in
the upper left corner of each cell flagging the "Number Stored as Text" error
when I use the range() method.

By the way I have used the range method in a number of other routines
without running into this issue.

Thanks,
Raul

"Jim Thomlinson" wrote:

Without looking too closely at exactly what you are trying to do there is an
obvious mistake in the first code. Range() requires 1 argument and can take
an optional second argument. You have supplied 3 arguments. It has no idea
what to do with 3 arguments.
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm using a routine to retreive data from a SQL Server database and then
output the results to a worksheet. MaterialTbl is a variant array.

The issue is I get a "Number stored as text" error when I use:

Set DestRange = Range(Worksheets(ThisSheet).Cells(3, 3), _
Worksheets(ThisSheet).Cells(UBound(MaterialTbl, 1), _
3 + UBound(MaterialTbl, 2) - 1))

DestRange.Value = MaterialTbl

But I don't get the error if I use:

For i = 0 To UBound(MaterialTbl, 1)
For j = 1 To UBound(MaterialTbl, 2)
Worksheets(ThisSheet).Cells(3+ i, 3 + j - 1).Value = MaterialTbl(i, j)
Next j
Next i

Why would I get an error with the first method and not get an error with the
latter?

Thanks in advance,
Raul

UBound(MaterialTbl, 1) = 1296
UBound(MaterialTbl, 2) = 8

Raul

"Jim Thomlinson" wrote:

Sorry about that...

What are the values of
UBound(MaterialTbl, 1)
3 + UBound(MaterialTbl, 2) - 1
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm pretty sure the range() is working and two arguments are being passed
(all open parentheses and close parentheses make it very messy).

The specified range is being populated, but I'm getting a green triangle in
the upper left corner of each cell flagging the "Number Stored as Text" error
when I use the range() method.

By the way I have used the range method in a number of other routines
without running into this issue.

Thanks,
Raul

"Jim Thomlinson" wrote:

Without looking too closely at exactly what you are trying to do there is an
obvious mistake in the first code. Range() requires 1 argument and can take
an optional second argument. You have supplied 3 arguments. It has no idea
what to do with 3 arguments.
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm using a routine to retreive data from a SQL Server database and then
output the results to a worksheet. MaterialTbl is a variant array.

The issue is I get a "Number stored as text" error when I use:

Set DestRange = Range(Worksheets(ThisSheet).Cells(3, 3), _
Worksheets(ThisSheet).Cells(UBound(MaterialTbl, 1), _
3 + UBound(MaterialTbl, 2) - 1))

DestRange.Value = MaterialTbl

But I don't get the error if I use:

For i = 0 To UBound(MaterialTbl, 1)
For j = 1 To UBound(MaterialTbl, 2)
Worksheets(ThisSheet).Cells(3+ i, 3 + j - 1).Value = MaterialTbl(i, j)
Next j
Next i

Why would I get an error with the first method and not get an error with the
latter?

Thanks in advance,
Raul

Try...
with Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(3, 3), _
.Cells(clng(UBound(MaterialTbl, 1)), _
3 + clng(UBound(MaterialTbl, 2)) - 1))
end with


--
HTH...

Jim Thomlinson


"Raul" wrote:

UBound(MaterialTbl, 1) = 1296
UBound(MaterialTbl, 2) = 8

Raul

"Jim Thomlinson" wrote:

Sorry about that...

What are the values of
UBound(MaterialTbl, 1)
3 + UBound(MaterialTbl, 2) - 1
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm pretty sure the range() is working and two arguments are being passed
(all open parentheses and close parentheses make it very messy).

The specified range is being populated, but I'm getting a green triangle in
the upper left corner of each cell flagging the "Number Stored as Text" error
when I use the range() method.

By the way I have used the range method in a number of other routines
without running into this issue.

Thanks,
Raul

"Jim Thomlinson" wrote:

Without looking too closely at exactly what you are trying to do there is an
obvious mistake in the first code. Range() requires 1 argument and can take
an optional second argument. You have supplied 3 arguments. It has no idea
what to do with 3 arguments.
--
HTH...

Jim Thomlinson


"Raul" wrote:

I'm using a routine to retreive data from a SQL Server database and then
output the results to a worksheet. MaterialTbl is a variant array.

The issue is I get a "Number stored as text" error when I use:

Set DestRange = Range(Worksheets(ThisSheet).Cells(3, 3), _
Worksheets(ThisSheet).Cells(UBound(MaterialTbl, 1), _
3 + UBound(MaterialTbl, 2) - 1))

DestRange.Value = MaterialTbl

But I don't get the error if I use:

For i = 0 To UBound(MaterialTbl, 1)
For j = 1 To UBound(MaterialTbl, 2)
Worksheets(ThisSheet).Cells(3+ i, 3 + j - 1).Value = MaterialTbl(i, j)
Next j
Next i

Why would I get an error with the first method and not get an error with the
latter?

Thanks in advance,
Raul