Unwanted conversion from Null to Zero
 

I am missing something...

A zero value eventually shows in the spreadsheet as zero.

Let me explain: my Function has a Range (a vertical vector) as a
parameter. The range may contain empty cells. The range is then
converted to a Variant that contains an array. The function puts all
the Null (empty) values at the bottom of the array. Till here
everything is fine.

But in the spreadsheet, instead of empty cells I see zeroes, which is
wrong. What am I missing?

Function Igual2(Rng As Range) As Variant
Dim Vector() As Variant
Dim i As Integer, j As Integer
Vector = Rng.Value
i = UBound(Vector, 1)
ReDim Preserve Vector(1 To i, 1 To 2)
For j = 1 To i
Vector(j, 2) = j
Next j
Vector = NullsAtTheBottom(Vector)
....
Igual2 = Vector
End Function

Unwanted conversion from Null to Zero
 

You need to post the code of NullsAtTheBottom.

RBS


"vsoler" wrote in message
...
I am missing something...

A zero value eventually shows in the spreadsheet as zero.

Let me explain: my Function has a Range (a vertical vector) as a
parameter. The range may contain empty cells. The range is then
converted to a Variant that contains an array. The function puts all
the Null (empty) values at the bottom of the array. Till here
everything is fine.

But in the spreadsheet, instead of empty cells I see zeroes, which is
wrong. What am I missing?

Function Igual2(Rng As Range) As Variant
Dim Vector() As Variant
Dim i As Integer, j As Integer
Vector = Rng.Value
i = UBound(Vector, 1)
ReDim Preserve Vector(1 To i, 1 To 2)
For j = 1 To i
Vector(j, 2) = j
Next j
Vector = NullsAtTheBottom(Vector)
...
Igual2 = Vector
End Function

Unwanted conversion from Null to Zero
 

You can go to ToolsOptionsView and uncheck "zero values". Excel otherwise
will convert null to zero.

"vsoler" wrote:

I am missing something...

A zero value eventually shows in the spreadsheet as zero.

Let me explain: my Function has a Range (a vertical vector) as a
parameter. The range may contain empty cells. The range is then
converted to a Variant that contains an array. The function puts all
the Null (empty) values at the bottom of the array. Till here
everything is fine.

But in the spreadsheet, instead of empty cells I see zeroes, which is
wrong. What am I missing?

Function Igual2(Rng As Range) As Variant
Dim Vector() As Variant
Dim i As Integer, j As Integer
Vector = Rng.Value
i = UBound(Vector, 1)
ReDim Preserve Vector(1 To i, 1 To 2)
For j = 1 To i
Vector(j, 2) = j
Next j
Vector = NullsAtTheBottom(Vector)
....
Igual2 = Vector
End Function

Unwanted conversion from Null to Zero
 

On 1 sep, 00:57, "RB Smissaert"
wrote:
You need to post the code of NullsAtTheBottom.

RBS

"vsoler" wrote in message

...

I am missing something...

A zero value eventually shows in the spreadsheet as zero.

Let me explain: my Function has a Range (a vertical vector) as a
parameter. The range may contain empty cells. The range is then
converted to a Variant that contains an array. The function puts all
the Null (empty) values at the bottom of the array. Till here
everything is fine.

But in the spreadsheet, instead of empty cells I see zeroes, which is
wrong. What am I missing?

Function Igual2(Rng As Range) As Variant
Dim Vector() As Variant
Dim i As Integer, j As Integer
Vector = Rng.Value
i = UBound(Vector, 1)
ReDim Preserve Vector(1 To i, 1 To 2)
For j = 1 To i
*Vector(j, 2) = j
Next j
Vector = NullsAtTheBottom(Vector)
...
Igual2 = Vector
End Function

Here is the code for NullsAtTheBottom: what's wrong with it?

Function NullsAtTheBottom(List()) As Variant
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp
First = LBound(List)
Last = UBound(List)
For i = 1 To Last - 1
If List(i, 1) = "" Then
For j = i + 1 To Last
If List(j, 1) < "" Then
Temp = List(j, 1)
List(j, 1) = List(i, 1)
List(i, 1) = Temp
End If
Next j
End If
Next i
NullsAtTheBottom = List
End Function