Extract text from a formula when in error

Paella1

Active Member
Joined
Mar 10, 2005
Messages
382
Hi all,

I have a workbook which produces #NAME errors in some cells when not connected to a third party application. I would like to know if there is a way to extract some of the text from the formula. I am finding that any formula I write that references the error cell also returns a #NAME error.

For the record, the formula is =EVRNG(E3:E11), and I am trying to extract the E3:E11 part.

(I am about to start on a solution in the third party app, but a solution in the disconnected state would be more robust)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Code:
If Range("C12").HasFormula Then myPrec = Range("C12").DirectPrecedents.Address
This vba line will return in variable myPrec the address of the "direct precedents" of cell C12, if it holds a formula

Does it help?
Bye
 
Upvote 0
Anthony,

It doesn't work from me because the range does not have a formula. It has an error.

It is however a very useful piece of code. I will use it in my thirdy party solution.
 
Upvote 0
You said that the cell contains a formula, it is the formula that returns the error...
Did you test it?

Bye
 
Upvote 0
The cell I am trying to interrogate is returning an error. It contains =EVRNG(E3:E11), which returns a #NAME error when disconnected from the third party app.

However, despite the error, the cell's contents contain information which I would like to use (specifically, the E3:E11 part). But any attempt to access the cell contents when the cell is in error also results in an error.
 
Upvote 0
The cell I am trying to interrogate is returning an error. It contains =EVRNG(E3:E11), which returns a #NAME error when disconnected from the third party app.

However, despite the error, the cell's contents contain information which I would like to use (specifically, the E3:E11 part). But any attempt to access the cell contents when the cell is in error also results in an error.
Probably I misunderstand your request or your context...
Test this macro:
Code:
Sub testprec()
If ActiveCell.HasFormula Then myPrec = ActiveCell.DirectPrecedents.Address
MsgBox (myPrec)
End Sub
(it is better if you assign it a shortcut)
Now select a cell with a formula and run the macro: a message box should inform which are the "predecessor" of the activecell. Test the macro with different cells, with different formulas (se Note**) or without formula.

If you wish to get that result with a formula, you cannot do it (to my knowledge...); but you can do it with a UDF. For example:
Code:
Function ParentAddr(myRan As Range) As String
    ParentAddr = Replace(Replace(Replace(myRan.Formula, "=EVRNG", ""), "(", ""), ")", "")
End Function
Then in a cell use the formula =parentAddr(TheCellAddress)
Note**: These functions are specialized on your formula =EVRNG(CellRange) but I guess you will undertsand how making a general UDF.
Since ParentAddr return a string, you can use it in an INDIRECT statemente to point to the actual range; for example =SUM(INDIRECT(parentAddr(C12)))
This second UDF will return directly the Range
Code:
Function ParentRange(myRan As Range) As Range
    Set ParentRange = Range(Replace(myRan.Formula, "=EVRNG", ""))
End Function
So you can use directly, as in =SUM(ParentRange(C12))

I hope these examples can be of some help.

Bye
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,059
Members
453,592
Latest member
bcexcel

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