Count Problem- cells with diagonal line

  • Thread starter Thread starter SKF Adam
  • Start date Start date
S

SKF Adam

Hi

I'm trying to count all cells in a range that have a diagonal lin
accross the cell but so far no luck! :confused:

Background:

The Worksheet is an audit schedule and when an audit is completed
diagonal (xldiagonalup) line is placed in the cell, for reporting I'
like to obtain the total number of cells with the line.

Would be sooooo grateful for a reply, I'm starting to loose the wil
the live!!!!!

Thanks to anyone who has taken the time to read this....

Ada
 
Hi Adam

Push Alt+F11 and go to Insert>Module and in here paste;

Function CountLines(SumRange As Range) As Long
Dim rCell As Range
For Each rCell In SumRange
If rCell.Borders(xlDiagonalUp).LineStyle <> xlNone _
Or rCell.Borders(xlDiagonalDown).LineStyle <> xlNone Then
CountLines = SumLines + 1
End If
Next rCell
End Function

Now click the top right X to get back to Excel. Use in any cell like

=CountLines(A1:A20)

Try to ONLY include the cell housing data. as with array formulas, most
Custom Functions are very slow compared to Excel's built in ones.



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
=SUMPRODUCT(--isDiagonal(range))

using the following VBA UDF (user defined function). Note that
reformatting cells does not trigger any event, so the count will not
automatically update until something else forces a recalc.

Jerry

Function IsDiagonal(ByVal Target As Excel.Range) As Variant
' must be manually recalculated since hidding/unhiding colums does
not trigger recalc
Dim Results()
rs = Target.Rows.Count
cs = Target.Columns.Count
ReDim Results(1 To rs, 1 To cs)
i = 0
For Each c In Target.Cells
Results(Int(i / cs) + 1, (i Mod cs) + 1) = Not
((c.Borders(xlDiagonalUp).LineStyle = xlNone) And
(c.Borders(xlDiagonalDown).LineStyle = xlNone))
i = i + 1
Next c
IsDiagonal = Results
End Function
 
Cheers Dave :)

Used:

Function CountLines(SumRange As Range) As Long
Dim rCell As Range
For Each rCell In SumRange
If rCell.Borders(xlDiagonalUp).LineStyle <> xlNone _
Or rCell.Borders(xlDiagonalDown).LineStyle <> xlNone Then
CountLines = CountLines + 1
End If
Next rCell
End Function

and it worked perfectly!!

Thanks again to everyone.....
 
Back
Top