VBA: using ignoreblank property
 

i dont understand what the heck is wrong with my code.. i know it is how
i've used the ignoreblank property, but am not sure how the proper
structure of that line should be.. any suggestions???
HELP!...i want it to ignore the cells with no data in them because
right now it says that the value of empty cells is zero, and it ruins
the rest of my code.

Sub lowcolor(lowval As Double, rng As Range)

Dim s As Double
Dim c As Range

lowval = 1000000#
For Each c In rng 'rng is a large range imported from another sub
c.Validation.IgnoreBlank = False

If (c.Font.ColorIndex = xlAutomatic Or c.Font.ColorIndex = 1)
And IsNumeric(c) Then
s = c.value

If s < lowval Then
lowval = s
End If
End If

Next c



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

using ignoreblank property
 

The IgnoreBlank property applies to datavalidation, not to the VBA code.
I assume you just want to skip over any blank cells without assigning 0
to lowval...

Sub lowcolor(lowval As Double, rng As Range)

Dim s As Double
Dim c As Range

'lowval = 1000000# 'I commented this out because I don't
'know why you would have lowval as an argument but
'assign it explicitly as well, this overriding the argument
For Each c In rng 'rng is a large range imported from another sub
If (c.Font.ColorIndex = xlAutomatic Or c.Font.ColorIndex = 1) _
And c.Value < "" And IsNumeric(c) Then
s = c.Value
If s < lowval Then
lowval = s
End If
End If
Next c

End Sub


In ,
chick-racer typed:
i dont understand what the heck is wrong with my code.. i know it is
how i've used the ignoreblank property, but am not sure how the proper
structure of that line should be.. any suggestions???
HELP!...i want it to ignore the cells with no data in them because
right now it says that the value of empty cells is zero, and it ruins
the rest of my code.

Sub lowcolor(lowval As Double, rng As Range)

Dim s As Double
Dim c As Range

lowval = 1000000#
For Each c In rng 'rng is a large range imported from another sub
c.Validation.IgnoreBlank = False

If (c.Font.ColorIndex = xlAutomatic Or c.Font.ColorIndex = 1)
And IsNumeric(c) Then
s = c.value

If s < lowval Then
lowval = s
End If
End If

Next c



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

VBA: using ignoreblank property
 

IgnoreBlank has to do with setting up the rules for Data/Validation. I'm not sure that's
relevant here. You want to ignore blank cells, right?

Sub LowColor(LowVal As Double, Rng As Range)
Dim c As Range
Dim v As Variant

LowVal = 1000000#
For Each c In Rng
v = c.Value
If (IsEmpty(v) = False) And IsNumeric(v) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
If v < LowVal Then LowVal = v
End Select
End If
Next c
End Sub




On Fri, 7 Nov 2003 15:06:47 -0500, chick-racer wrote:


i dont understand what the heck is wrong with my code.. i know it is how
i've used the ignoreblank property, but am not sure how the proper
structure of that line should be.. any suggestions???
HELP!...i want it to ignore the cells with no data in them because
right now it says that the value of empty cells is zero, and it ruins
the rest of my code.

Sub lowcolor(lowval As Double, rng As Range)

Dim s As Double
Dim c As Range

lowval = 1000000#
For Each c In rng 'rng is a large range imported from another sub
c.Validation.IgnoreBlank = False

If (c.Font.ColorIndex = xlAutomatic Or c.Font.ColorIndex = 1)
And IsNumeric(c) Then
s = c.value

If s < lowval Then
lowval = s
End If
End If

Next c



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/