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]

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

=INDEX(A1,2)

...that produces a Mismatch. Great.

Change to:

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

 

[surkdidat]

Could it be if B2 is in #REF! status, ie the first value does not have a numeric value? Is it easy to amend so I can add "IFERROR" with either a blank or a " - "?

Thanks, and apologies for all the queries on this!

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

 

[Zyndstoff]

This (as posted above) will work:

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

If you cut out the code betwen Sub ChgF() and End Sub and place it in the Workbook in the Workbook_BeforeClose sub, the macro will execute automatically every time you close the workbook.

Just because I'm curious: why is it, that you want the formulas replaced by numeric values formatted as text?

 

[surkdidat]

This (as posted above) will work:

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

If you cut out the code betwen Sub ChgF() and End Sub and place it in the Workbook in the Workbook_BeforeClose sub, the macro will execute automatically every time you close the workbook.

Just because I'm curious: why is it, that you want the formulas replaced by numeric values formatted as text?

NUmeric/Text, apologies probably lost in what I was saying. basically, was trying to distinguish the end result from a formula

 

[Zyndstoff]

NUmeric/Text, apologies probably lost in what I was saying. basically, was trying to distinguish the end result from a formula

If distinguishing in a sense of "seeing" is meant, wouldn't it be sufficient to mark the cells containing a formula by, let's say changing the background collor?

 

[surkdidat]

If distinguishing in a sense of "seeing" is meant, wouldn't it be sufficient to mark the cells containing a formula by, let's say changing the background collor?

Need it to be converted form a formula, as other spreadsheets link into this, so the issue I was having is when the were closed, it "lost" the data. Having something that has all the formulas in, waiting to "capture" the data when its fed in, and then converting it to a number/text in the cell gets around the problem

 

[Zyndstoff]

Need it to be converted form a formula, as other spreadsheets link into this, so the issue I was having is when the were closed, it "lost" the data. Having something that has all the formulas in, waiting to "capture" the data when its fed in, and then converting it to a number/text in the cell gets around the problem

Okay, I see.
Please note that my vba-macro I provided converts all numbers to text. So if a numerical value is to be read out, the text needs to be reconverted to a number.
You can change this behaviour by changing

Rng = "'" & Format(Rng)

in the code to

Rng = Rng

Then all texts will remain text, all numbers will remain numbers.
I understood your initial post that you wanted them converted to text.