Replace Formula with another Formula

Slowgawd

New Member
Joined
Nov 15, 2005
Messages
5
Hey everyone, long time lurker, first time poster here! I have a question I couldn't seem to find on the board. If it exists could someone please point me there!

I have a custom excel fomula that looks like so:

ReturnCellvalue(x,y,z)
X = WorkSheet Name
Y = Row Location
Z = Column Location

I'm exporting data from a source system that returns null values as spaces, which the formula above can't resolve (gives value error).

I CAN resolve it by using the following formula N(returncellvalue(x,y,z)).

My question is, is there is a way, macro or not, to find any occurence of the Returncellvalue(x,y,z) formula and replace it with the wrapped version
N(returncellvalue(x,y,z).

I would do it manually but I've inhereted these Excel models and there has to be at least 1,000,000 of these formulas that need wrapping :)

Keep in mind that the variables x,y,z are dynamic values, and in some cases other formulas.

I'm a novice with VBA, so any help is appreciated!!

Thanks in advance!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Why not just change the code for the custom function to deal with the problem?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Welcome to the board.

Here's one approach...

1) Select the column with the ReturnCellvalue.
2) Use find and replace to replace = with '=
3) In a new column, make a formula =if(isnumber(search("returncellvalue",A1)),"'"&A1&"@",A1) or some other unused character instead of @
4) Copy and paste values of the new column over itself
5) Use find and replace in the new column to replace )@ with ))
6) Use find and replace in the new column to replace =ReturnCellvalue with =N(ReturnCellvalue
7) Replace the ReturnCellvalue column with the new column.
8) Use Data --> Text to columns on the new column, using ' as a delimiter.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
My suggestion would be:

Turn on Formula View, Tools => View => Formula View.

Then use replace to change "ReturnCellvalue" to "N(ReturnCellvalue".

Then Turn Off Formula View.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988

ADVERTISEMENT

Datsmart said:
My suggestion would be:

Turn on Formula View, Tools => View => Formula View.

Then use replace to change "ReturnCellvalue" to "N(ReturnCellvalue".

Then Turn Off Formula View.

I was worried about the:

Slowgawd said:
Keep in mind that the variables x,y,z are dynamic values, and in some cases other formulas.

comment... parentheses would be a problem. [As a dumb example, replacing IF with N(IF in =IF(B1=5,SUM(A2:A10)) will not work]
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Oaktree,

Good point. In the sample I tested with, I had to click OK for each replacement to allow Excel to correct the Parentheses. This would be unaceptable with Slowgawd's "1,000,000 formulas".

Thanks for pointing that out.

Datsmart
 

Slowgawd

New Member
Joined
Nov 15, 2005
Messages
5

ADVERTISEMENT

Thanks everyone for responding!

QUOTE from NORIE
**Why not just change the code for the custom function to deal with the problem?**

Again i'm a VBA novice here is the code, maybe you could show me how I can solve it?:

********************************
Public Function ReturnCellValue(ByVal strSheetName As String, ByVal intRow As Integer, ByVal intCol As Integer) As Variant

Dim wbk As Workbook
Dim sht As Worksheet

Set wbk = ActiveWorkbook
Set sht = wbk.Worksheets(strSheetName)
ReturnCellValue = sht.Cells(intRow, intCol).Value

End Function
********************************

Oaktree, Thanks for the approach seems logical, I just have to find the time to work through each column (1000+)

Datsmart, that's exactly the approach I tried yesterday after I posted the message, frustrating!!!

If anyone could recommend another quick fix via a looped macro that would be wonderful.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Slowgawd

What values could be null?
Code:
Public Function ReturnCellValue(ByVal strSheetName As String, ByVal intRow As Variant, ByVal intCol As Variant) As Variant
Dim wbk As Workbook
Dim sht As Worksheet
    On Error GoTo FunctionErr
    
    Set wbk = ActiveWorkbook
    Set sht = wbk.Worksheets(strSheetName)
    ReturnCellValue = sht.Cells(intRow, intCol).Value
    Exit Function
    
FunctionErr:
    
    ReturnCellValue = "Error"
End Function
 

Slowgawd

New Member
Joined
Nov 15, 2005
Messages
5
Not sure what you completely mean?

The null values i'm getting now are spaces inserted by the source system export into null value cells.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Well which parameter could be Null/spaces.

Did you try the code I posted?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,300
Latest member
Chaneycr
Top