VBA that converts a formula to text/number if result is more than 0 / (or NOT #REF!)

[surkdidat]

Is there anyway I can have VBA code run automatically/when a work book closes/click of a button etc, that goes through a sheet that contains a formula.

If the formula "answer" is a number more than 0 (or is simply not a #REF!) I want it then to convert the umbers permanently to text

 

[Zyndstoff]

Can be done. In which cell area are the formulas placed? E.g. Cells A1 to K12.

 

[surkdidat]

Can be done. In which cell area are the formulas placed? E.g. Cells A1 to K12.

B2:e100

 

[bebo021999]

May be:

Sub test()
For Each Rng In Range("b2:e100")
If Rng.HasFormula = True And Rng.Value > 0 Then
Rng.Value = Rng.Value
End If
Next
End Sub

 

[Zyndstoff]

Sub ChgF()
Dim Rng As Range
For Each Rng In ActiveSheet.Range("b2:e100").Cells
  If Rng.HasFormula = True And Rng.Value > 0 Then
    Rng = "'" & Format(Rng)
  End If
Next Rng
End Sub

 

[Zyndstoff]

Forgot to check for #REF Errors in above code...:

Sub ChgF()
Dim Rng As Range
For Each Rng In ActiveSheet.Range("b2:e100").Cells
  If Rng.HasFormula = True And Rng.Value > 0 And IsError(Rng)=False Then
    Rng = "'" & Format(Rng)
  End If
Next Rng
End Sub[CODE]

 

[surkdidat]

Hi

Thank you for this - when I run the Macro it comes up as "Type Mismatch"?

Forgot to check for #REF Errors in above code...:

Sub ChgF()
Dim Rng As Range
For Each Rng In ActiveSheet.Range("b2:e100").Cells
  If Rng.HasFormula = True And Rng.Value > 0 And IsError(Rng)=False Then
    Rng = "'" & Format(Rng)
  End If
Next Rng
End Sub[CODE]

 

[Zyndstoff]

Hm... works for me. Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
For Each Rng In Range("B2:E100").Cells
  If Rng.HasFormula = True And IsNumeric(Rng)=True And Rng.Value > 0 And IsError(Rng) = False Then
    Rng = "'" & Format(Rng)
  End If
Next Rng
End Sub

 

[Zyndstoff]

As hard as I try, I can't reproduce any Type Mismatch error with this code...

Public Sub ChgF()
Dim Rng As Range
For Each Rng In ActiveSheet.Range("B2:E100").Cells
  If Rng.HasFormula = True And IsNumeric(Rng) = True And Rng > 0 And IsError(Rng) = False Then
    Rng = "'" & Format(Rng)
  End If
Next Rng
End Sub

 

[FormR]

As hard as I try, I can't reproduce any Type Mismatch error with this code...

Hi, what if you try with this formula in cell B2

=INDEX(A1,2)