Hi All, I'm using a piece of code that returns the last term in a string, which here is the number 44.
Where the following string is in D101:
AAPL May21 44
The following code returns the 44:
=TRIM(RIGHT(SUBSTITUTE(D101," ",REPT(" ",100)),100))
This seems to work fine but I keep getting a circular references error message a few times an hour.
unnamed.png
Does anyone know why or an error free way to do this?
What cell is this formula in? Is D101 a value or a formula? Does the status bar at the bottom of the window tell you what cell is causing the circular reference?
Can you share the file? See yellow banner at the top of the page.
For info, a Circ Ref Error is where a formula in a cell references itself - either directly or indirectly.
In it's simplest form...
A1=+A1
or
A1=B1, but B1=A1
In its most complex form, it can be hidden inside dozens of referenced formulas.
Excel will attempt to show you where the error is (on the bottom left corner), and in simple errors, this is usually pretty spot on. But in more complex files, excel tries its best, but often misses the exact location by many levels.
A method I use to find the location is (1st SAVE the file, so you can recover it later) to delete the cell (or row/column if it is the same formula) that excel says is the culprit. If the error goes away, you have found the sourse. If it shows another location, you have not yet found the location, so repeat the delete process again. Keep doing that until the error message disappears.
Once you find the location, undo the last action and examine that formula to see if you can ID where the problem lies.
Good luck, this can be a tedious process.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks