Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Simon Lloyd
 
Posts: n/a
Default Counting Function Dilemma


Hi All,

I have some code in my Auto_open that references a function to count by
colour only if a date exists which works fine, my problem is i have
tried to replicate it but to count over a range only if the cell
contains text (or if easier could get it to look for a number entered
in the cell between 1 and 10) but if the cell contains #N/A to either
skip it or when counting delete it from the total.

Here's what i have so far, and the second half doesnt work!

simon



Sub Auto_open()

Dim ccount As Integer
Dim cccount As Variant
Application.DisplayAlerts = False
Application.DisplayFormulaBar = False

Range("B5").Select

ActiveCell.FormulaR1C1 =
"=COUNTBYCOLOR(R[9]C[-1]:R[484]C[-1],38,FALSE)"


Range("B7").Select
Range("d14").Select
ccount = Range("b5")
Range("B6").Select
ActiveCell.FormulaR1C1 = "=CntByColor(R[8]C[2]:R[485]C[33],38,FALSE)"
Range("B7").Select
Range("d14").Select
cccount = Range("B6")
Worksheets("holidays").Visible = True
Worksheets("Holiday Count").Visible = True
Worksheets("Xtra's & Count").Visible = True
Sheets("holidays").Activate
MsgBox "There Are " & ccount & " Holiday Clashes" & Chr(13) & "
There Have Been " & cccount & " accomodations", vbOKOnly, "Clash Count"


End Sub



Function CountByColor(InRange As Range, WhatColorIndex As Integer,
Optional OfText As Boolean = False) As Long
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If IsDate(Rng) Then
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function

Function CntByColor(InRange As Range, WhatColorIndex As Integer,
Optional OfText As Boolean = False) As Long
Dim Rng As Range
Dim cccount As Integer
Application.Volatile True
Range("D14:AI491") = Rng
For Each Rng In InRange.Cells
If OfText = True Then
If ActiveCell.Text = "#N/A" Then
Range("B6").Value = Range("B6").Value - 1
CntByColor = CntByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CntByColor = CntByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=276228

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Averaging function Sarah Excel Discussion (Misc queries) 0 January 18th 05 05:09 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
change function variable prompts?? thinkingfield Excel Worksheet Functions 1 November 8th 04 05:01 PM
need to save values from a function before it changes Ron Excel Worksheet Functions 1 October 29th 04 06:29 AM


All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"