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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Why not just change the code for the custom function to deal with the problem?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Well which parameter could be Null/spaces.

Did you try the code I posted?
 
Upvote 0

Forum statistics

Threads
1,211,457
Messages
6,101,961
Members
447,766
Latest member
cool_aikon

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
Back
Top