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

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

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

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

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

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