Hello all,
I have a function that return certain value depending on calculations.
In certain conditions I would want to return a value so that receiving cell
behaves as if I would press Del-key, this is nothing is displayed and content
is empty.
I tried returning Null, but 0 is displayed in this case.
I tried returning "", but conditional formatting of the cell understand that
this is a value and then gives an undesired format.
Do anybody of you know how to solve this?
Any idea or suggestion will be welcome.
Thanks in advance and kind regards,
Raúl

(You can see here the function:

Public Function LastValue(iRowTitle As Long, _
FirstCell As Range, _
BlockSize As Long, ColInBlock As Long,
ColInBlock2 As Long) As Variant
Dim WS As Excel.Worksheet
Dim ThereIsData As Boolean
Dim i As Long
Dim RGBlock As Excel.Range
LastValue = Null '<<<<<<<========================= Value returned
when I cannot calculate the result

Set WS = FirstCell.Parent
ThereIsData = True
i = FirstCell.Column
Set RGBlock = Range(WS.Cells(FirstCell.Row, FirstCell.Column),
WS.Cells(FirstCell.Row, FirstCell.Column + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False

Do While ThereIsData
If RGBlock.Cells(1, ColInBlock) < "" Then
LastValue = RGBlock.Cells(1, ColInBlock)
ElseIf RGBlock.Cells(1, ColInBlock2) < "" Then
LastValue = RGBlock.Cells(1, ColInBlock2)
End If
Set RGBlock = Range(WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize),
WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False
Loop

End_Zone:
Exit Function

End Function
)

Excel doesn't have the equivalent of a Null.

Maybe you could change your conditional formatting rules or even return a unique
string and use that.

RPC wrote:

Hello all,
I have a function that return certain value depending on calculations.
In certain conditions I would want to return a value so that receiving cell
behaves as if I would press Del-key, this is nothing is displayed and content
is empty.
I tried returning Null, but 0 is displayed in this case.
I tried returning "", but conditional formatting of the cell understand that
this is a value and then gives an undesired format.
Do anybody of you know how to solve this?
Any idea or suggestion will be welcome.
Thanks in advance and kind regards,
Raúl

(You can see here the function:

Public Function LastValue(iRowTitle As Long, _
FirstCell As Range, _
BlockSize As Long, ColInBlock As Long,
ColInBlock2 As Long) As Variant
Dim WS As Excel.Worksheet
Dim ThereIsData As Boolean
Dim i As Long
Dim RGBlock As Excel.Range
LastValue = Null '<<<<<<<========================= Value returned
when I cannot calculate the result

Set WS = FirstCell.Parent
ThereIsData = True
i = FirstCell.Column
Set RGBlock = Range(WS.Cells(FirstCell.Row, FirstCell.Column),
WS.Cells(FirstCell.Row, FirstCell.Column + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False

Do While ThereIsData
If RGBlock.Cells(1, ColInBlock) < "" Then
LastValue = RGBlock.Cells(1, ColInBlock)
ElseIf RGBlock.Cells(1, ColInBlock2) < "" Then
LastValue = RGBlock.Cells(1, ColInBlock2)
End If
Set RGBlock = Range(WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize),
WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False
Loop

End_Zone:
Exit Function

End Function
)

--

Dave Peterson

This is what I thougth. It is a pity but....
Thanks for your answer.
Kind regards,
Raúl


"Dave Peterson" wrote:

Excel doesn't have the equivalent of a Null.

Maybe you could change your conditional formatting rules or even return a unique
string and use that.

RPC wrote:

Hello all,
I have a function that return certain value depending on calculations.
In certain conditions I would want to return a value so that receiving cell
behaves as if I would press Del-key, this is nothing is displayed and content
is empty.
I tried returning Null, but 0 is displayed in this case.
I tried returning "", but conditional formatting of the cell understand that
this is a value and then gives an undesired format.
Do anybody of you know how to solve this?
Any idea or suggestion will be welcome.
Thanks in advance and kind regards,
Raúl

(You can see here the function:

Public Function LastValue(iRowTitle As Long, _
FirstCell As Range, _
BlockSize As Long, ColInBlock As Long,
ColInBlock2 As Long) As Variant
Dim WS As Excel.Worksheet
Dim ThereIsData As Boolean
Dim i As Long
Dim RGBlock As Excel.Range
LastValue = Null '<<<<<<<========================= Value returned
when I cannot calculate the result

Set WS = FirstCell.Parent
ThereIsData = True
i = FirstCell.Column
Set RGBlock = Range(WS.Cells(FirstCell.Row, FirstCell.Column),
WS.Cells(FirstCell.Row, FirstCell.Column + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False

Do While ThereIsData
If RGBlock.Cells(1, ColInBlock) < "" Then
LastValue = RGBlock.Cells(1, ColInBlock)
ElseIf RGBlock.Cells(1, ColInBlock2) < "" Then
LastValue = RGBlock.Cells(1, ColInBlock2)
End If
Set RGBlock = Range(WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize),
WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False
Loop

End_Zone:
Exit Function

End Function
)

--

Dave Peterson