Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Bordered Cells
Complete VBA noob.
I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet. Line style and color do not matter. Function CountBrd(rng As Range) Dim i As Integer Dim cell As Range i = 0 For Each cell In rng If cell.Borders(xlEdgeLeft).LineStyle < xlLineStyleNone AndIf cell.Borders(xlEdgeRight).LineStyle < xlLineStyleNone AndIf cell.Borders(xlEdgeBottom).LineStyle < xlLineStyleNone AndIf cell.Borders(xlEdgeTop).LineStyle < xlLineStyleNone Then i = i + 1 End If Next CountBrd = i End Function On another sheet in a cell I have =CountBrd("Sheetname""Range") I get a couple of syntax errors and since I know zero about this I come to you for help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Bordered Cells
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Bordered Cells
On Sunday, May 25, 2014 10:06:37 PM UTC-6, wrote:
Complete VBA noob. I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet. Line style and color do not matter. Function CountBrd(rng As Range) Dim i As Integer Dim cell As Range i = 0 For Each cell In rng If cell.Borders(xlEdgeLeft).LineStyle < xlLineStyleNone AndIf cell.Borders(xlEdgeRight).LineStyle < xlLineStyleNone AndIf cell.Borders(xlEdgeBottom).LineStyle < xlLineStyleNone AndIf cell.Borders(xlEdgeTop).LineStyle < xlLineStyleNone Then i = i + 1 End If Next CountBrd = i End Function On another sheet in a cell I have =CountBrd("Sheetname""Range") I get a couple of syntax errors and since I know zero about this I come to you for help. Thanks Claus, When I run the function I do receive a result, but it is not accurate. IT may be counting the cells that have a left border by default. This may be helpful, by default the range will have the top and left cell already with a border. I need to count the cells in the range when they have all four sides bordered. I see this line " If rngC.Borders(xlEdgeLeft).LineStyle _" is that looking at only cells with a left border? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Bordered Cells
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Bordered Cells
wrote in message ... Complete VBA noob. I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet. Line style and color do not matter. Another one, based on Claus' but could be a few times faster be faster with a large range Function CountBordersAround(rng As Range) As Long Dim n As Long Dim c As Range n = rng.Count For Each c In rng For i = xlEdgeLeft To xlEdgeRight If c.Borders(i).LineStyle = xlNone Then n = n - 1 Exit For End If Next Next CountBordersAround = n End Function Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Bordered Cells
On Monday, May 26, 2014 11:50:43 AM UTC-6, Peter T wrote:
wrote in message ... Complete VBA noob. I have created a module in VBA and have tried the following to count cells in a range on one sheet that have four sides bordered and have the count results in a cell on another sheet. Line style and color do not matter. Another one, based on Claus' but could be a few times faster be faster with a large range Function CountBordersAround(rng As Range) As Long Dim n As Long Dim c As Range n = rng.Count For Each c In rng For i = xlEdgeLeft To xlEdgeRight If c.Borders(i).LineStyle = xlNone Then n = n - 1 Exit For End If Next Next CountBordersAround = n End Function Regards, Peter T Wooot,Thank you Claus and Peter! Both work well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of cells between 2 seperate cells | Excel Discussion (Misc queries) | |||
Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |