Replace Formula with another Formula

[Slowgawd]

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!

 

[Norie]

Why not just change the code for the custom function to deal with the problem?

 

[Oaktree]

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]

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]

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:

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]

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]

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]

Slowgawd

What values could be null?

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]

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 which parameter could be Null/spaces.

Did you try the code I posted?