Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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. "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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 -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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. "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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 "Todd Virlee" wrote: 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. "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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). -- Rick (MVP - Excel) "Tom Hutchins" wrote in message ... 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 "Todd Virlee" wrote: 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. "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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 "Rick Rothstein" wrote: 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). -- Rick (MVP - Excel) "Tom Hutchins" wrote in message ... 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 "Todd Virlee" wrote: 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. "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to get cell references from a formula
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. -- Rick (MVP - Excel) "Tom Hutchins" wrote in message ... 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 "Rick Rothstein" wrote: 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). -- Rick (MVP - Excel) "Tom Hutchins" wrote in message ... 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 "Todd Virlee" wrote: 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. "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Todd Virlee" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find #ref! invalid cell references within cell formula | Excel Worksheet Functions | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
cell formula references | Excel Discussion (Misc queries) | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
how to use cell contents as references in a formula | Excel Worksheet Functions |