CountNoStrike - need help

[Noopur Suba]

Hello,
I have used following VBA code in attached sheet.

Public Function CountNoStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
  If Not pRng.Font.Strikethrough And pRng = 1 Then
  xOut = xOut + 1
  End If
Next
CountNoStrike = xOut
End Function

I have one issue - in the sheet - tab 4 - you can see in column C copied from source have strikethrough cells. Here on applying above VBA code - I am getting error such that few strikethrough cells are not being counted because in the source sheet these cells are strikedout automatically through some other formula. Kindly help me resolving this error in a way that CountNostrike work properly without having formatting issue. Thanks

 

[Hui]

Noopur

Firstly, Welcome to the Chandoo.org Forums

Can you tell us what values should be where for the issues to be triggered

Please also supply the VBA password

 

[Marc L]

Hi !

I have one issue - in the sheet - tab 4 - you can see in column C copied from source have strikethrough cells.

In fourth worksheet like in any other one of your attachment
column C is empty !

So join a workbook reflecting exactly what you wrote … (Say ♪ Captain ♫ !)
​

 

[Noopur Suba]

Password for VBA code is Donottouch

Let me upload new file for better understanding..

In the newly attached file - you can see in 4th tab - 128 docs - C column is pasted from the source file. Basically this whole excel is the template where we copy paste the C column to get the % completion in L4, L5 and L6.

What happens is as you can see strike through cells in column C - the source file has two types of format of the strike through cells such that few cells are not counted when pasted in this sheet. For e.g. if you can filter column D for '1' you will see that few strike through cells will have 1 instead of 0 (zero). Hence I have to manually change such values to 0 where it is coming 1 against strike through cells to get correct % in L4, L5 and L6.

Can you help me resolving this error of formatting (as I cannot change the source file which is VBA protected and confidential to share) so that I do not have to manually change the values from 1 to 0 for strike through cells?

Let me know in case any other information required.

Thanks in advance for your help and time.

kind regards,
Noopur

 

[Hui]

Noopur

Firstly some nomenclature

I think you are calling the cells Pattern Style, Strike Through

This is Pattern Style It is accessed from the Format Cells, Fill, pattern Style menu

This is Strike Through , It is accessed from the Format Cells, Font, Effects menu

In regards the =CountNoStrike(C5) function

Can you please explain under what circumstances it should return a 0 and a 1 ?

 

[NARAYANK991]

Hi Noopur ,

The function CountNoStrike has been used as a UDF , which means the function should be coded to return just one value ; the use of a loop is not correct.

Since your usage is as follows :

=CountNoStrike(C2)

where C2 is one cell , the code should look at only this one cell , and return a 0 or a 1 depending on processing of data for that one cell.

Secondly , the code is checking for two conditions before it counts the cell as a NoStrike cell ; the cell font should not be strikethrough , and the cell value should be 1. So if you want the cell to be counted as 1 , both these conditions will have to be fulfilled ; if you want these conditions to be removed or changed , please specify the conditions which should be used.


Narayan

 

[Noopur Suba]

Hui

The cell pattern is read as strike through using countnostrike formula. And hence where it is reading correctly it is reflecting 0 or else 1. Now as I said before if you apply filter to column D and filter for 1 you will get fee strike pattern cell also have 1 which I need to manually change to zero. Which means there is formatting issue with strike through pattern in the source file. I just found that in source file few rows get strike through automatically when we say no to certain specifications given. Rest of rows if required we strike them through and hence for those cells we get correct results using countnostrike formula. My query is for those cells where due to formatting issue in source file the formula not working in my file. I want to know if there is some way after copying column c I can do some formatting thing such that formula works properly?

Regards,
Noopur