AboutTom Ogilvy Expertise Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
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).
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
