Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if the formula is in a particular format/style
Hi All,
I have a problem or situation. I have excel sheets that have formula that needs to be changed. I have changed one set and done a copy--- paste special ----- formula. Fine till here. Now what I want to do is select the range that I have copied the formula and check if it is in the same format that I require. For example the formula is say "=If(isblank(A2),"N/A",(C2/E2-D2)). So, what I need to do is enter the following in a text box : (if(isblank(xx999),"N/A",(xx999/(xx999-xx999)), where xx represents any column from A to IV and 999 be a row from 1 to 65536. Is this possible? Any help will be highly appreciated. Or else i have to check the 27 sheets with 400 formulae in each sheet manually :-(. Regds |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if the formula is in a particular format/style
Not sure exactly what you intend here (why do you want to enter into a
textbox?), but it sounds like you need to be able to find where your formula is, then to check the formatting and fix it. To cycle through the sheets and find any cells containing your formula you can do it this way: Sub FindCells() Dim ThisSheet as Worksheet, FormulaRange as Range, ThisCell as Range For Each ThisSheet in ThisWorkbook.Worksheets Set FormulaRange = ThisSheet.Cells.Cells.SpecialCells(xlCellTypeFormu las) For Each ThisCell in FormulaRange If ThisCell.Formula like "=if(isblank(*),"N/A",(*/(*-*))" Then ' set the format End If Next ThisCell Next ThisSheet End Sub If you need to set the format to, let's say, numeric with 2 digits after the decimal point you could do it like this: ThisCell..NumberFormat = "#0.00" The If ThisCell.Formula like ... statement may need to be adjusted if you have similar formulas that this should NOT apply to, but see the VBA help file on the like operator for details of how you can specify a pattern to match. -- - K Dales "Shuvro Basu" wrote: Hi All, I have a problem or situation. I have excel sheets that have formula that needs to be changed. I have changed one set and done a copy--- paste special ----- formula. Fine till here. Now what I want to do is select the range that I have copied the formula and check if it is in the same format that I require. For example the formula is say "=If(isblank(A2),"N/A",(C2/E2-D2)). So, what I need to do is enter the following in a text box : (if(isblank(xx999),"N/A",(xx999/(xx999-xx999)), where xx represents any column from A to IV and 999 be a row from 1 to 65536. Is this possible? Any help will be highly appreciated. Or else i have to check the 27 sheets with 400 formulae in each sheet manually :-(. Regds |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if the formula is in a particular format/style
Hi K,
Thank you for your response. I got what I required. Cheers !! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2 | Excel Discussion (Misc queries) | |||
Req help on Formula or vb in excel to calculate between reference style and formula style tia sal2 | Excel Worksheet Functions | |||
Changing from format style to list style | Excel Worksheet Functions | |||
can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
Check my Conditional Format Formula | Excel Programming |