Need to get cell references from a formula

  • Thread starter Thread starter Todd Virlee
  • Start date Start date
T

Todd Virlee

I am trying to translate a formula into meaningful text like I posted
yesterday. How can I get tell where there is an A1 or C1 in the formula?
 
This newsgroup gets hundreds of messages posted to it daily. As a result,
almost no one here would remember what you posted yesterday and you
shouldn't expect them to go looking for your past messages either. By the
way, I did look for your past post and can find no other posts by you in
this newsgroup. Perhaps you posted it in a different newsgroup... again, you
shouldn't expect anyone to go looking (all over the net) for it. It is
always best to state your existing conditions and what you are trying to
accomplish with it... and giving examples of existing and what you want
afterwards is always helpful too.
 
I will rephrase the question in the hopes that no one else will belittle me.

How does Excel know where there is a cell reference in a formula? How does
it know to change the color when you are editing?

I want to be able to write a macro that will output the value of a cell
above the referenced cell.

If the worksheet looks like this

Today Years New date in future
2/11/2009 5 2/14/2009

where C2's formula is A2+B2, I want to get an output of "=Today+Years" in C3.
 
I wasn't attempting to belittle you, only to educate you on what we
volunteers need to work with in trying to answer your, as well as all the
other poster's, questions. You question in this thread was a little thin on
the detail you wanted and it looked like you were saying "go find my other
post to find out what I am looking for". By the way, I do apologize for
saying I couldn't find your other thread... I'm not sure what happened but,
when I first looked, I could find any other posts by you... I just looked
again and this time I see them.

Anyway... to your question. The macro I am posting below works, but only for
simple cell references. If you have any range references, those will remain
as is. Also, the macro will not work for references on other worksheets
(seems to be a limitation of the Precedents property). I **might** be able
to develop code to work around that, but I'm thinking it will be difficult
(unless all your worksheet references always had apostrophes around their
names... I'm thinking that might let me hone in on the worksheet's name
easier).

To use the following macro, select the cell or cells you want to perform
this operation on and then run the macro. One thing you will need to do
manually is set the location for the output as a row/column offset from the
selected cell or cells. To do this, just change the two Const statements to
set the row offset (0 means same row) and the column offset (0 means same
column). As set in my code, I post the formulas in the row underneath the
selected cell or cells (as per your stated desire to have C2's modified
formula posted in C3). Note that to tell which is the Row 1 header text,
that text is listed with angle brackets around it. For example, if the
header text is Year, it will be shown in the modified formula as <Year> so
that you can tell it from the rest of the text (function names, etc.) that
may be in the formula as well...

Sub ReferenceHeaderText()
Dim R As Range, V As Range
Dim CellFormula As String

Const RowOffset As Long = 0
Const ColumnOffset As Long = 1

For Each R In Selection
CellFormula = Replace(R.Formula, "$", "")
For Each V In R.Precedents
If InStr(CellFormula, V.Address(0, 0) & ":") = 0 And _
InStr(CellFormula, ":" & V.Address(0, 0)) = 0 Then
CellFormula = Replace(CellFormula, V.Address(0, 0), "<" & _
Cells(1, V.Column).Value & ">")
End If
Next
With R.Offset(RowOffset, ColumnOffset)
.NumberFormat = "@"
.Value = CellFormula
End With
Next
End Sub
 
Try this:

Sub document_it1()
Dim r As Range, v As String
Dim w As String, x
Dim y As String, z As String
Set r = ActiveCell
If InStr(r.Formula, "(") > 0 Then
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "("))
Else
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "="))
End If
If InStr(v, ")") > 0 Then
w = Left(v, InStr(v, ")") - 1)
Else
w = v
End If
x = Split(w, "+", -1)
y = "="
For n = LBound(x) To UBound(x)
y = y & Range(x(n)).Offset(-1, 0).Value & "+"
Next n
z = Left(y, Len(y) - 1)
r.Offset(1, 0).NumberFormat = "@"
r.Offset(1, 0).Value = z
End Sub

I have uploaded a sample file with this macro (and the version I wrote for
your request yesterday) with some dummy data to:
http://freefilehosting.net/download/454ll

Download & open the file. Select C2 on SheetX and run the above macro.

Hope this helps,

Hutch
 
I don't think that is what the OP wants... I believe he is asking for the
header text to be put in the formula in place of the cell reference, not the
cell's value (that solution was the subject of his previous thread).
 
That's what this version (document_it1) does. The .Offset(-1,0) looks at the
cell above the cell referenced in the formula. The sample file I uploaded
also has yesterday's version (document_it2), which puts the referenced cell's
value in the output.

Hutch
 
I see. What threw me was I tried it on this formula...

=LOOKUP(B3,G10:G20,H10:H20)

and it returned a numerical value of 5 (for some data I set up), which is
what prompted me to reply the way I did. I just tried it on this formula...

=B2+B3

and it returned this...

=Years+5

which is not exactly correct. It did work correctly for this formula
though...

=A1+B1

So I'm not sure how universal your solution would be.
 
Back
Top