Check if the formula is in a particular format/style

[Shuvro Basu]

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

 

[Guest]

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(xlCellTypeFormulas)
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.

 

[Shuvro Basu]

Hi K,

Thank you for your response. I got what I required.

Cheers !!