Changing cell formula in VBA

[affordsol]

Hi to all !!


I have a small problem in Excel97, although I don't know if the Excel
version is relevant.

In some of my sheets, I've elaborated some complex formulas which,
sometimes, can lead to
the infamous "#NA" result.

I want to replace the "#NA" by a clean 2 characters "NA" string.

As there are MANY formulas, I want to develop a VBA sub which does the work :

a) pick up the actual formula (which we'll define as FormulaContent)
(maybe save the activecell.address so as to be able to write to
that range)

b) replace it by
IF(ISERROR( FC ),"NA", FC)

WHERE FC is FormulaContent without the preceeding "=" sign


I thank you by advance.
Best regards,

 

[carlo]

Hi Herve

you could try something like this

Sub Exchange_Formula()

Dim cell_ As Range

Dim str_formula As String
For Each cell_ In ActiveSheet.Cells
If cell_.HasFormula Then
str_formula = Right(cell_.Formula, Len(cell_.Formula) - 1)
cell_.Formula = "=if(iserror(" & str_formula & "),""NA""," &
str_formula & ")"
End If
Next cell_

End Sub


hth

Carlo

 

[affordsol]

Hi Carlo !


So sorry for the late reply but I wish to thank you for your code : it does
the job quite perfectly.

I just changed it a little bit to match MY OWN ranges and I post it here so
as to 'empower' anyone which might be interested in the post.

Best regards from Belgium,
Hervé+

'===============================================================
Sub VoidNAinPrintout()
Dim cell_ As Range
Dim str_formula As String
'Here is the all sheet startup
'For Each cell_ In ActiveSheet.Cells
For Each cell_ In ActiveSheet.Range("L2526")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J31:U33")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J35:U39")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J41:U44")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J49:U61")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J63:U70")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J75:U77")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J79:U98")
GoSub CLEANME
Next cell_
For Each cell_ In ActiveSheet.Range("J102:U104")
GoSub CLEANME
Next cell_
Exit Sub
'<<<<<<<<<<<<<<<<<<<
CLEANME:
If cell_.HasFormula Then
str_formula = Right(cell_.Formula, Len(cell_.Formula) - 1)
cell_.Formula = "=if(iserror(" & str_formula & "),""NA""," & str_formula
& ")"
End If
Return
'>>>>>>>>>>>>>>>>>
End Sub
'===============================================================