Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
Hi,
I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND match another condition. I would be very happy just counting the first character of the cells if that makes life any easier. Preferably I'm looking for a formula to do this, but will settle for a VBA solution if necessary. 8^) Thanks, Norm |
#2
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
Hi!
There's a *real clunky* formula method to do this but a VBA UDF or macro would be much better. If nobody posts a VBA solution I'll be back and post the formula method. Biff "Father Guido" wrote in message ... Hi, I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND match another condition. I would be very happy just counting the first character of the cells if that makes life any easier. Preferably I'm looking for a formula to do this, but will settle for a VBA solution if necessary. 8^) Thanks, Norm |
#3
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
A UDF solution:
Option Explicit Function CountStrikeThrough(myRng As Range) As Long Application.Volatile Dim myCell As Range Dim ctr As Long ctr = 0 For Each myCell In myRng.Cells If myCell.Font.Strikethrough = True Then ctr = ctr + 1 End If Next myCell CountStrikeThrough = ctr End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Then use a formula like: =countstrikethrough(a1:a20) Be aware that changing the format won't make this function recalculate. You'll want to force a recalc before you trust the results. Father Guido wrote: Hi, I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND match another condition. I would be very happy just counting the first character of the cells if that makes life any easier. Preferably I'm looking for a formula to do this, but will settle for a VBA solution if necessary. 8^) Thanks, Norm -- Dave Peterson |
#4
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
Maybe a little nit-picky, but.........
This doesn't account for empty cells. It counts cells that are formatted but may be empty. Example: Format cells A1:A10 for strikethrough. Don't enter anything in those cells. =CountStrikeThrough(A1:A10) returns 10 Biff "Dave Peterson" wrote in message ... A UDF solution: Option Explicit Function CountStrikeThrough(myRng As Range) As Long Application.Volatile Dim myCell As Range Dim ctr As Long ctr = 0 For Each myCell In myRng.Cells If myCell.Font.Strikethrough = True Then ctr = ctr + 1 End If Next myCell CountStrikeThrough = ctr End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Then use a formula like: =countstrikethrough(a1:a20) Be aware that changing the format won't make this function recalculate. You'll want to force a recalc before you trust the results. Father Guido wrote: Hi, I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND match another condition. I would be very happy just counting the first character of the cells if that makes life any easier. Preferably I'm looking for a formula to do this, but will settle for a VBA solution if necessary. 8^) Thanks, Norm -- Dave Peterson |
#5
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
Hi!
Please post your kludged formula for the heckuvit so I can see what/how you go about creating weird formulae to count cells with strikethroughs. OK! Assume the range to check is A1:A10 Create this named formula: Goto InsertNameDefine Name: Strike Refers to: =AND(INDIRECT("RC[-1]",FALSE)<"",GET.CELL(23,INDIRECT("RC[-1]",FALSE))) OK out Now in cell B1 enter this formula and copy down to B10: =Strike This will return either TRUE or FALSE. Then to count the number of cells that have strikethrough formatting (excluding empty cells that are formatted): =COUNTIF(B1:B10,TRUE) This works the same as the VBA UDF in that changing a format does not trigger a calculation. You either have to initiate a calc or wait until the next event triggered calc for the formula to update. That formula is specifically written to be placed in the column to the immediate right of the target column. I use R1C1 reference style because a defined name using the A1 style has to use absolute referencing. Biff "Father Guido" wrote in message ... On Wed, 26 Oct 2005 21:30:41 -0400, "Biff" wrote: ~Maybe a little nit-picky, but......... ~ ~This doesn't account for empty cells. It counts cells that are formatted but ~may be empty. ~ ~Example: ~ ~Format cells A1:A10 for strikethrough. Don't enter anything in those cells. ~ ~=CountStrikeThrough(A1:A10) returns 10 ~ ~Biff ________________________________________ Good point, fortunately for me I have no blank cells formatted as strikethrough. Please post your kludged formula for the heckuvit so I can see what/how you go about creating weird formulae to count cells with strikethroughs. Thanks Norm ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ ~ ~"Dave Peterson" wrote in message ... ~A UDF solution: ~ ~ Option Explicit ~ Function CountStrikeThrough(myRng As Range) As Long ~ ~ Application.Volatile ~ ~ Dim myCell As Range ~ Dim ctr As Long ~ ~ ctr = 0 ~ For Each myCell In myRng.Cells ~ If myCell.Font.Strikethrough = True Then ~ ctr = ctr + 1 ~ End If ~ Next myCell ~ ~ CountStrikeThrough = ctr ~ ~ End Function ~ ~ ~ If you're new to macros, you may want to read David McRitchie's intro at: ~ http://www.mvps.org/dmcritchie/excel/getstarted.htm ~ ~ Short course: ~ ~ Open your workbook. ~ Hit alt-f11 to get to the VBE (where macros/UDF's live) ~ hit ctrl-R to view the project explorer ~ Find your workbook. ~ should look like: VBAProject (yourfilename.xls) ~ ~ right click on the project name ~ Insert, then Module ~ You should see the code window pop up on the right hand side ~ ~ Paste the code in there. ~ ~ Now go back to excel. ~ ~ Then use a formula like: ~ ~ =countstrikethrough(a1:a20) ~ ~ Be aware that changing the format won't make this function recalculate. ~ You'll ~ want to force a recalc before you trust the results. ~ ~ Father Guido wrote: ~ ~ Hi, ~ ~ I'm using XL2002 and am looking to count the number of cells in a ~ column that have words struck out. In my case all the words in each ~ cell are either all struck out, or none are struck out. ~ ~ First I can't figure out what character a strikethrough is, anyone ~ know? And I can't get the CODE because excel will only return the code ~ for the first character in a cell, which is nothing when there is ~ nothing in the cell, even if you pre-select strikethrough from format ~ cells. ~ ~ In the end I'm looking to make an array formula that will tell me how ~ many cells have a strikethrough AND match another condition. ~ ~ I would be very happy just counting the first character of the cells ~ if that makes life any easier. ~ ~ Preferably I'm looking for a formula to do this, but will settle for a ~ VBA solution if necessary. 8^) ~ ~ Thanks, ~ ~ Norm ~ ~ -- ~ ~ Dave Peterson ~ |
#6
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
You could always check to see if there was anything in the cell:
Option Explicit Function CountStrikeThrough(myRng As Range) As Long Application.Volatile Dim myCell As Range Dim ctr As Long ctr = 0 For Each myCell In myRng.Cells If myCell.Value = "" Then 'do nothing Else If myCell.Font.Strikethrough = True Then ctr = ctr + 1 End If End If Next myCell CountStrikeThrough = ctr End Function But as a generic routine, I'm not sure which I'd want. Biff wrote: Maybe a little nit-picky, but......... This doesn't account for empty cells. It counts cells that are formatted but may be empty. Example: Format cells A1:A10 for strikethrough. Don't enter anything in those cells. =CountStrikeThrough(A1:A10) returns 10 Biff "Dave Peterson" wrote in message ... A UDF solution: Option Explicit Function CountStrikeThrough(myRng As Range) As Long Application.Volatile Dim myCell As Range Dim ctr As Long ctr = 0 For Each myCell In myRng.Cells If myCell.Font.Strikethrough = True Then ctr = ctr + 1 End If Next myCell CountStrikeThrough = ctr End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Then use a formula like: =countstrikethrough(a1:a20) Be aware that changing the format won't make this function recalculate. You'll want to force a recalc before you trust the results. Father Guido wrote: Hi, I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND match another condition. I would be very happy just counting the first character of the cells if that makes life any easier. Preferably I'm looking for a formula to do this, but will settle for a VBA solution if necessary. 8^) Thanks, Norm -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
Excellent!!!
This allows me to use auto-filter to count and/or hide the struck cells (rows). Thank you very much - even if I don't understand the named range formula. Norm On Thu, 27 Oct 2005 01:15:29 -0400, "Biff" wrote: Hi! Please post your kludged formula for the heckuvit so I can see what/how you go about creating weird formulae to count cells with strikethroughs. OK! Assume the range to check is A1:A10 Create this named formula: Goto InsertNameDefine Name: Strike Refers to: =AND(INDIRECT("RC[-1]",FALSE)<"",GET.CELL(23,INDIRECT("RC[-1]",FALSE))) OK out Now in cell B1 enter this formula and copy down to B10: =Strike This will return either TRUE or FALSE. Then to count the number of cells that have strikethrough formatting (excluding empty cells that are formatted): =COUNTIF(B1:B10,TRUE) This works the same as the VBA UDF in that changing a format does not trigger a calculation. You either have to initiate a calc or wait until the next event triggered calc for the formula to update. That formula is specifically written to be placed in the column to the immediate right of the target column. I use R1C1 reference style because a defined name using the A1 style has to use absolute referencing. Biff "Father Guido" wrote in message .. . On Wed, 26 Oct 2005 21:30:41 -0400, "Biff" wrote: ~Maybe a little nit-picky, but......... ~ ~This doesn't account for empty cells. It counts cells that are formatted but ~may be empty. ~ ~Example: ~ ~Format cells A1:A10 for strikethrough. Don't enter anything in those cells. ~ ~=CountStrikeThrough(A1:A10) returns 10 ~ ~Biff ________________________________________ Good point, fortunately for me I have no blank cells formatted as strikethrough. Please post your kludged formula for the heckuvit so I can see what/how you go about creating weird formulae to count cells with strikethroughs. Thanks Norm ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ ~ ~"Dave Peterson" wrote in message ... ~A UDF solution: ~ ~ Option Explicit ~ Function CountStrikeThrough(myRng As Range) As Long ~ ~ Application.Volatile ~ ~ Dim myCell As Range ~ Dim ctr As Long ~ ~ ctr = 0 ~ For Each myCell In myRng.Cells ~ If myCell.Font.Strikethrough = True Then ~ ctr = ctr + 1 ~ End If ~ Next myCell ~ ~ CountStrikeThrough = ctr ~ ~ End Function ~ ~ ~ If you're new to macros, you may want to read David McRitchie's intro at: ~ http://www.mvps.org/dmcritchie/excel/getstarted.htm ~ ~ Short course: ~ ~ Open your workbook. ~ Hit alt-f11 to get to the VBE (where macros/UDF's live) ~ hit ctrl-R to view the project explorer ~ Find your workbook. ~ should look like: VBAProject (yourfilename.xls) ~ ~ right click on the project name ~ Insert, then Module ~ You should see the code window pop up on the right hand side ~ ~ Paste the code in there. ~ ~ Now go back to excel. ~ ~ Then use a formula like: ~ ~ =countstrikethrough(a1:a20) ~ ~ Be aware that changing the format won't make this function recalculate. ~ You'll ~ want to force a recalc before you trust the results. ~ ~ Father Guido wrote: ~ ~ Hi, ~ ~ I'm using XL2002 and am looking to count the number of cells in a ~ column that have words struck out. In my case all the words in each ~ cell are either all struck out, or none are struck out. ~ ~ First I can't figure out what character a strikethrough is, anyone ~ know? And I can't get the CODE because excel will only return the code ~ for the first character in a cell, which is nothing when there is ~ nothing in the cell, even if you pre-select strikethrough from format ~ cells. ~ ~ In the end I'm looking to make an array formula that will tell me how ~ many cells have a strikethrough AND match another condition. ~ ~ I would be very happy just counting the first character of the cells ~ if that makes life any easier. ~ ~ Preferably I'm looking for a formula to do this, but will settle for a ~ VBA solution if necessary. 8^) ~ ~ Thanks, ~ ~ Norm ~ ~ -- ~ ~ Dave Peterson ~ |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any way to count cells contining one, or more STRIKETHROUGHS?
Worked for me as well, thank you very much.
You could always check to see if there was anything in the cell: Option Explicit Function CountStrikeThrough(myRng As Range) As Long Application.Volatile Dim myCell As Range Dim ctr As Long ctr = 0 For Each myCell In myRng.Cells If myCell.Value = "" Then 'do nothing Else If myCell.Font.Strikethrough = True Then ctr = ctr + 1 End If End If Next myCell CountStrikeThrough = ctr End Function But as a generic routine, I'm not sure which I'd want. Biff wrote: Maybe a little nit-picky, but......... This doesn't account for empty cells. It counts cells that are formatted but may be empty. Example: Format cells A1:A10 for strikethrough. Don't enter anything in those cells. =CountStrikeThrough(A1:A10) returns 10 Biff "Dave Peterson" wrote in message ... A UDF solution: Option Explicit Function CountStrikeThrough(myRng As Range) As Long Application.Volatile Dim myCell As Range Dim ctr As Long ctr = 0 For Each myCell In myRng.Cells If myCell.Font.Strikethrough = True Then ctr = ctr + 1 End If Next myCell CountStrikeThrough = ctr End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Then use a formula like: =countstrikethrough(a1:a20) Be aware that changing the format won't make this function recalculate. You'll want to force a recalc before you trust the results. Father Guido wrote: Hi, I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND match another condition. I would be very happy just counting the first character of the cells if that makes life any easier. Preferably I'm looking for a formula to do this, but will settle for a VBA solution if necessary. 8^) Thanks, Norm -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count # of cells b/w cells ... | Excel Discussion (Misc queries) | |||
count cells that have *text1* and don't have *text2* | Excel Discussion (Misc queries) | |||
Count cells in a column that contain dates | New Users to Excel | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
How do I count shaded cells | Excel Worksheet Functions |