Change existing formula to =n

[mccmark]

I am trying to learn VBA but to be honest I am mostly relying on "record macro" to get things done.
An associate is being weened away from old Lotus spreadsheets. Evidently, text in a cell that is included in a formula is treated as a zero in Lotus, in Excel it returns #value.
Some of tis formulas are similar to this:

Item	Original bid	Current Bid	Difference
Concrete	Not included	100​	#VALUE!​
Paving	See above	See above​	#VALUE!​
Permits	100​	125​	-25​

I have recorded a macro (bellow) that works to change his formula =a2-b2 to =n(a2)-n(b2) so he gets the results he wants:

=n formula

-100​

0​

-25​

VBA from recorded macro
Sub CellText()
'
' CellText Macro
'

'
ActiveCell.FormulaR1C1 = "=N(RC[-2])-N(RC[-1])"
'Range("D3").Select
End Sub

What I can't figure out is how to do the same thing in the event the original formula contains more than two cell references (other than record a new macro for 3, 4, 5, etc.)
For example, if the original formula is =b2-c2-d2-e2.
Any help would be most appreciated and will help me be more self-reliant in the future.
Mark

 

[OaklandJim]

Mark

Just so you know...

I'm kind of busy -- and trying to cope with political situation without going bonkers -- but am TRYING to finish a function that takes the formula -- including cell addresses and math operators -- and converts to include N function call for each cell reference.

Converting a formula like =a1-CC2-AD33-f4 to =N(a1)-N(CC2)-N(AD33)-N(f4)

No promises. My programming is a bit unsophisticated so I hope that I can finish it.

Maybe tomorrow I'll have something.

Seems that Mr. Excel does not like workbook sharing so I'll try to make code portable and postable here.

Jim

 

[mccmark]

Thank you, and I feel your pain re: political situation!
Mark

 

[Peter_SSs]

Welcome to the MrExcel board!

Are all the formulas that you want to convert simple addition/subtraction formulas?

 

[mccmark]

Yes, that is correct, thank you.
Mark

 

[mccmark]

To clarify, he wants to leave the existing formulas as is, and then when he sees an error in the result, such as #value, he wants to select that cell, run the macro, then move to the next one. As opposed to running the macro on the entire worksheet. Does that make sense?
Thank you,
Mark

 

[Peter_SSs]

When he sees an error in the result, such as #value, he wants to select that cell, run the macro, ..... As opposed to running the macro on the entire worksheet.

OK, you could try something like this. Test with a copy of the workbook.

Sub Convert_Formula()
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "^=([A-Z]{1,3}\d{1,6})([\+\-][A-Z]{1,3}\d{1,6})+$"
  With ActiveCell
    If RX.Test(.Formula) Then
      .Formula = "=N(" & Replace(Replace(Mid(.Formula, 2), "+", ")+N("), "-", ")-N(") & ")"
    Else
      MsgBox "Formula does not fit the pattern. Not converted."
    End If
  End With
End Sub

 

[mccmark]

I will give it a test later when company leaves, thank you.
Mark

 

[mccmark]

Peter,
This works perfectly on my first few attempts, thank you. For my own edification, I am going to use your code to try to learn how approach similar issues in the future. I am going to experiment on some different scenarios. I should be able to adapt this to accommodate an existing formula that contains other operands (* and /). I also want to see if I can get it to work on a formula that already contains parentheses, such as =(a1-b1)+(d1+d2). Not that anyone would write a formula like that, but just to help me get a handle on the regular expressions and VBA. I'm sure I will be back when I run into the inevitable error.
Thank you again,
Mark

 

[Peter_SSs]

This works perfectly on my first few attempts, thank you.

You're welcome.

I'm sure I will be back when I run into the inevitable error.

No problem. Post back with any follow-up questions.