How to skip blank cells in Function for Rank in VBA?

**Neight** · 05-23-2007, 10:19 AM

 Function RankSum(InRange, OutRange, RankRange, SegmentStartTime, SegmentMinutes)
For Each Cell In InRange
    StartTime = Cell
    FinishTime = Cell.Offset(0, OutRange.Column - InRange.Column)
    If StartTime - (SegmentMinutes / (24 * 60)) < SegmentStartTime And FinishTime >= SegmentStartTime + (SegmentMinutes / (24 * 60)) - 0.0000000001 Then
        RankSum = RankSum + Cell.Offset(0, RankRange.Column - InRange.Column)
    End If
Next Cell
End Function

how would i get this module to do nothing if certain text is entered in the box

**cbanks** · 05-23-2007, 10:59 AM

why wouldnt you use an if statement?

Function RankSum(InRange, OutRange, RankRange, SegmentStartTime, SegmentMinutes)
For Each Cell In InRange
StartTime = Cell
FinishTime = Cell.Offset(0, OutRange.Column - InRange.Column)
if StartTime = "set value" then
else
If StartTime - (SegmentMinutes / (24 * 60)) < SegmentStartTime And FinishTime >= SegmentStartTime + (SegmentMinutes / (24 * 60)) - 0.0000000001 Then
RankSum = RankSum + Cell.Offset(0, RankRange.Column - InRange.Column)
End If
end if
Next Cell
End Function

**Neight** · 05-23-2007, 11:05 AM

im not real familiar with this code? can you tell me what what "set value" or would be?

**cbanks** · 05-23-2007, 11:13 AM

set value would be the text you are wanting it to avoid.. If i wanted to avoid all start times that were 10:00 then i would put

if StartTime = "10:00" then

**Neight** · 05-23-2007, 11:15 AM

ok, i thought so...so as of now. i have


Function RankSum(InRange, OutRange, RankRange, SegmentStartTime, SegmentMinutes)
For Each Cell In InRange
    StartTime = Cell
    FinishTime = Cell.Offset(0, OutRange.Column - InRange.Column)
    If StartTime = "HOL" Or "VAC" Or "FMLA" Then
    StartTime = 0
    Else
    If StartTime - (SegmentMinutes / (24 * 60)) < SegmentStartTime And FinishTime >= SegmentStartTime + (SegmentMinutes / (24 * 60)) - 0.0000000001 Then
        RankSum = RankSum + Cell.Offset(0, RankRange.Column - InRange.Column)
    End If
    End If
Next Cell
End Function

thats still outputting an error

**cbanks** · 05-23-2007, 11:19 AM

you are setting it equal to 0, but then you are not doing anything as the rest of your process is part of the else.. try this.

Please use code tags as it is the forum rules.

Function RankSum(InRange, OutRange, RankRange, SegmentStartTime, SegmentMinutes)
For Each Cell In InRange
StartTime = Cell
FinishTime = Cell.Offset(0, OutRange.Column - InRange.Column)
If StartTime = "HOL" Or "VAC" Or "FMLA" Then
StartTime = 0
end if
If StartTime - (SegmentMinutes / (24 * 60)) < SegmentStartTime And FinishTime >= SegmentStartTime + (SegmentMinutes / (24 * 60)) - 0.0000000001 Then
RankSum = RankSum + Cell.Offset(0, RankRange.Column - InRange.Column)
End If

Next Cell
End Function