Expert: Tom Ogilvy - 5/28/2008

Question
QUESTION: Hello, Tom,

well, I need some help in excel, Please check this.
A B
1   
2   
3 1
4 2
5   
6 3
7 4



I am tryin to write a function in excel where, I have to compare 2 columns and if a number is strike throughed the corresponding value should become blank,
Here, in A if 1,2 are stirkethroughed the corresponding cells in B should be blank and start the number 1 for 3 in A.

let me know if you can help me to write a function for this.

Thanks and Regards,


ANSWER: Kiran,
So you want to write a User Defined function in VBA?  

So what you show in column B is the results of the funciton being entered in column B1 and drag filled down the column.  In column A, the numbers 1, 2, and 5 are using strike through in the font?

If all the answers are yes, then I can help you write a UDF.  Note that changing the font of a cell does not trigger a calculate event, so such a function would only be accurate in what it returns after you force a re-calculation.  

so you would enter  

=Nostrike($A$1:A1)    in B1 and drag fill down

Public Function Nostrike(r As Range) As Variant
Dim cell As Range, r1 As Range
Dim r2 As Range, cnt As Long
Set r1 = Application.Caller
If r1.Column = 1 Then
 Nostrike = CVErr(xlErrRef)
 Exit Function
End If
Set r2 = r1(r1.Count)(1, 0)
If r2.Font.Strikethrough Then
 Nostrike = vbNullChar
 Exit Function
End If
cnt = 0
For Each cell In r
If Not cell.Font.Strikethrough Then
  cnt = cnt + 1
End If
Next
Nostrike = cnt
End Function

the above function should be placed in a General module in the same workbook where you want to use it (not in a sheet module or the thisworkbook module).


--
regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: Hello, Tom,
Thanks for the response,
I still cant get it figured, when I called the function into excel it is not recognising the function in column B.

Any suggestions ?

Thanks


Answer
Before I posted the function, I installed it as I described in a workbook and put the formula I showed in B1, then drag filled it down to row 7.  It produced this result:

A B
1   
2   
3 1
4 2
5   
6 3
7 4


identical to what you describe.  Did you put it in a general module in the same workbook (with only your workbook open and the active project in the vbe, In the VBE, do Insert=>Module and put the code in that module).  

Usually when the name is not recognized as you seem to indicate, it means you did not put it in a general/standard module in the same workbook as I explained you must do.

Although unrelated to what you describe, I would suggest putting in one additional command in the function:

Public Function Nostrike(r As Range) As Variant
Application.Volatile  '<= added instruction
Dim cell As Range, r1 As Range
Dim r2 As Range, cnt As Long
Set r1 = Application.Caller
If r1.Column = 1 Then
Nostrike = CVErr(xlErrRef)
Exit Function
End If
Set r2 = r1(r1.Count)(1, 0)
If r2.Font.Strikethrough Then
Nostrike = vbNullChar
Exit Function
End If
cnt = 0
For Each cell In r
If Not cell.Font.Strikethrough Then
 cnt = cnt + 1
End If
Next
Nostrike = cnt
End Function

This makes the function volatile - like rand(), so it executes on each recalcualtion.

If you can't get it working, post back with your email address and I will send you a workbook where it is working quite well.

--
Regards,
Tom Ogilvy