Change existing formula to =n

mccmark

New Member
Joined
Oct 1, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:
ItemOriginal bidCurrent BidDifference
ConcreteNot included
100​
#VALUE!​
PavingSee 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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Oct 1, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you, and I feel your pain re: political situation!
Mark
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

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

mccmark

New Member
Joined
Oct 1, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes, that is correct, thank you.
Mark
 

mccmark

New Member
Joined
Oct 1, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

New Member
Joined
Oct 1, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I will give it a test later when company leaves, thank you.
Mark
 

mccmark

New Member
Joined
Oct 1, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top