Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the formula of a cell as text
Hi,
I want to create a macro that puts the cursor over a cell which is retrieved from the cell it's standing on. Wow, I notice english is not my first language, because even I can't really understand what I just wrote :) An example would be better. Let's say I'm standing on a cell with the formula "=A25". Now when I'm standing on this cell I want to run a macro that moves the cursor over the referenced cell (A25). Now I can go to a cell if I have the position of the cell as text in the current cell as: Range(Activecell.Value).Select How can I achieve this to replace Activecell.value with the text gotten from the formula? TIA Regards, -- Beto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the formula of a cell as text
Try something like this
x = Mid(Selection.Formula, 2, Len(Selection.Formula)) Range(x).Select -- "Beto" wrote in message om... Hi, I want to create a macro that puts the cursor over a cell which is retrieved from the cell it's standing on. Wow, I notice english is not my first language, because even I can't really understand what I just wrote :) An example would be better. Let's say I'm standing on a cell with the formula "=A25". Now when I'm standing on this cell I want to run a macro that moves the cursor over the referenced cell (A25). Now I can go to a cell if I have the position of the cell as text in the current cell as: Range(Activecell.Value).Select How can I achieve this to replace Activecell.value with the text gotten from the formula? TIA Regards, -- Beto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the formula of a cell as text
Actually It should be directprecedents - precendents includes more than
directprecedents. Sub MoveTo() Dim rng As Range If ActiveCell.HasFormula Then On Error Resume Next Set rng = ActiveCell.DirectPrecedents On Error GoTo 0 If Not rng Is Nothing Then rng.Areas(1).Select End If End If End Sub -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Assuming you are not trying to move to another sheet: Sub MoveTo() Dim rng As Range If ActiveCell.HasFormula Then On Error Resume Next Set rng = ActiveCell.Precedents On Error GoTo 0 If Not rng Is Nothing Then rng.Areas(1).Select End If End If End Sub -- Regards, Tom Ogilvy Beto wrote in message om... Hi, I want to create a macro that puts the cursor over a cell which is retrieved from the cell it's standing on. Wow, I notice english is not my first language, because even I can't really understand what I just wrote :) An example would be better. Let's say I'm standing on a cell with the formula "=A25". Now when I'm standing on this cell I want to run a macro that moves the cursor over the referenced cell (A25). Now I can go to a cell if I have the position of the cell as text in the current cell as: Range(Activecell.Value).Select How can I achieve this to replace Activecell.value with the text gotten from the formula? TIA Regards, -- Beto |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the formula of a cell as text
Hello Beto
A similar solution to Tom's is: Sub MyIndirect() Dim Cell As Range Dim s As String If ActiveCell.HasFormula = False Then MsgBox "There is no formula in this cell", , "Error_ Alert" Exit Sub Else: s = "" & ActiveCell.Formula & "" Range(s).Select End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the formula of a cell as text
Thanks to all who answered. I'll try the solutions soon!
Regards, -- Beto "Peter Atherton" wrote in message ... Hello Beto A similar solution to Tom's is: Sub MyIndirect() Dim Cell As Range Dim s As String If ActiveCell.HasFormula = False Then MsgBox "There is no formula in this cell", , "Error_ Alert" Exit Sub Else: s = "" & ActiveCell.Formula & "" Range(s).Select End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bold text of referenced cell show in formula cell | Excel Worksheet Functions | |||
The result of a formula referring to a cell with formula as text? | Excel Worksheet Functions | |||
Concatenate text cell and formula cell result | Excel Worksheet Functions | |||
Excel Formula if cell is empty then no if text in cell then yes | Excel Worksheet Functions | |||
match cell text with text in formula | Excel Worksheet Functions |