ExcelBanter - Number Stored as Text Issue

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Number Stored as Text Issue (https://www.excelbanter.com/excel-programming/420466-number-stored-text-issue.html)

Number Stored as Text Issue

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

Number Stored as Text Issue

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

Number Stored as Text Issue

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

Number Stored as Text Issue

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

Number Stored as Text Issue

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

Number Stored as Text Issue

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

Number Stored as Text Issue

No joy; still getting the green triangles in the upper left corner of each
cell.

I get the results without the green triangles when I use a for next loop to
write the array element to the appropriate cell in the worksheet.

The results in the MaterialTbl array are numbers but there is text in one
column. By the way, MaterialTbl is defined as variant. For some reason the
range method treats the all values in the array as text and the loop method
recognizes the diference between numbers and text.

Thanks,
Raul

"Jim Thomlinson" wrote:

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