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. |
Counting Bordered Cells
|
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? |
Counting Bordered Cells
|
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 |
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. |
All times are GMT +1. The time now is 11:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com