PDA

View Full Version : Solved: Extracting cell addresses from a formula



Digita
10-05-2007, 02:56 PM
Hi there,

When a cell contains a long winded formula, it would be a time consuming task to manually go to each individual cell addresses within the formula to find out the values these input cells hold.

Would there be a quicker way by vba to extract the input cell addresses & exclude math operators such as: "+", "-", "*", "/", "(",")", "^", pure numbers and "&"? The cell addresses sometimes come with sheet prefix - eg Sheet2!A1. Once the cell addresses are extracted, they would be populated in some kind of a loop to jump to the individual cells one after another.

Thanks in advance for your thoughts and input. Have a great weekend.

Kind regards


KP

Bob Phillips
10-05-2007, 04:20 PM
Public Sub Test()
Dim cell As Range
Dim prec
For Each cell In Selection
For Each prec In cell.Precedents
Debug.Print cell.Address(, , , True) & " - " & prec.Address(, , , True) & " - " & prec.Value
Next prec
Next cell
End Sub

Aussiebear
10-05-2007, 04:31 PM
If you are simply reviewing the formula in Excel 2007 ( and I don't know about earlier versions), you have the option of highlighting a section of a formula - whether its a cell reference or a section of formula - and pressing F9 to calculate what it represents, by changing the highlighted section to either its cell value or part of formula value. Simply cancelling the change will bring you back to the old formula.

Hope this helps even though its not a vba assist.

Digita
10-07-2007, 05:21 PM
Hi guys,

Sorry I couldn't reply earlier. Aussiebear's suggestion is brilliant. I tested under XL 2003 and it works. Xld's code is also good. However, when part of the formula refers to a cell on different sheet the code does not seem to pick up the cell on a different sheet and hence the underlying value of that cell is not shown on the immediate window.

Anyhow, thanks a ton for your wonderful inputs. Have a good day.

Regards


KP

Aussiebear
10-08-2007, 01:42 AM
..it was what??? :SHOCKED:

Damn, I'll have to be more careful in future. Fancy me saying anything useful...

....I've got a rep to protect.....