How to (re)set a range.value to pass -0- to a "double" variable
 

Using XL 2003

Sub PrintIf()
..........
Dim R As Range
Dim ThisPageTotal as Double
Set R = ActiveSheet.Cells(Rows.count, "P").End(xlUp)
ThisPageTotal = R.Value
If ThisPageTotal < 0 Then _
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
..........
End Sub

This above code works fine until it is used on a worksheet with no
dollar amount in the cell at ActiveSheet.Cells(Rows.count,
"P").End(xlUp)

When that happens, the code prints even though the cell may be empty.

OK R is a range.

When I "Watch" the Variables and there is a dollar value:

R may equal (i.e.) 411.27 (Range)
R.Value will equal 411.27 (Double)
ThisPageTotal = 411.27 (Double)

How does I set "R" to pass a -0- to R.Value AND to ThisPageTotal?

Set R.Value = 0 ?? or
Set R = Nothing ??

Dennis

maybe...

Sub PrintIf()
..........
Dim R As Range
Dim ThisPageTotal as Double
Set R = ActiveSheet.Cells(Rows.count, "P").End(xlUp)
if isempty(R) then
msgbox "That cell is empty!
else
ThisPageTotal = R.Value
If ThisPageTotal < 0 Then _
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
..........
end if
end if
End Sub

If you have text in that cell, you may want to use a check like:

if application.isnumber(r.value) = false then
msgbox "It's not a number!
else
.....

application.isnumber(r.value) tests differently than VBA's isnumber(r.value).


Dennis wrote:

Using XL 2003

Sub PrintIf()
..........
Dim R As Range
Dim ThisPageTotal as Double
Set R = ActiveSheet.Cells(Rows.count, "P").End(xlUp)
ThisPageTotal = R.Value
If ThisPageTotal < 0 Then _
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
..........
End Sub

This above code works fine until it is used on a worksheet with no
dollar amount in the cell at ActiveSheet.Cells(Rows.count,
"P").End(xlUp)

When that happens, the code prints even though the cell may be empty.

OK R is a range.

When I "Watch" the Variables and there is a dollar value:

R may equal (i.e.) 411.27 (Range)
R.Value will equal 411.27 (Double)
ThisPageTotal = 411.27 (Double)

How does I set "R" to pass a -0- to R.Value AND to ThisPageTotal?

Set R.Value = 0 ?? or
Set R = Nothing ??

Dennis

--

Dave Peterson

Thanks Dave!

"Dave Peterson" wrote:

maybe...

Sub PrintIf()
..........
Dim R As Range
Dim ThisPageTotal as Double
Set R = ActiveSheet.Cells(Rows.count, "P").End(xlUp)
if isempty(R) then
msgbox "That cell is empty!
else
ThisPageTotal = R.Value
If ThisPageTotal < 0 Then _
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
..........
end if
end if
End Sub

If you have text in that cell, you may want to use a check like:

if application.isnumber(r.value) = false then
msgbox "It's not a number!
else
.....

application.isnumber(r.value) tests differently than VBA's isnumber(r.value).


Dennis wrote:

Using XL 2003

Sub PrintIf()
..........
Dim R As Range
Dim ThisPageTotal as Double
Set R = ActiveSheet.Cells(Rows.count, "P").End(xlUp)
ThisPageTotal = R.Value
If ThisPageTotal < 0 Then _
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
..........
End Sub

This above code works fine until it is used on a worksheet with no
dollar amount in the cell at ActiveSheet.Cells(Rows.count,
"P").End(xlUp)

When that happens, the code prints even though the cell may be empty.

OK R is a range.

When I "Watch" the Variables and there is a dollar value:

R may equal (i.e.) 411.27 (Range)
R.Value will equal 411.27 (Double)
ThisPageTotal = 411.27 (Double)

How does I set "R" to pass a -0- to R.Value AND to ThisPageTotal?

Set R.Value = 0 ?? or
Set R = Nothing ??

Dennis

--

Dave Peterson