pull the cell ref from a formula

[tonywatsonhelp]

Hi everyone,

i need a macro that can take the cell ref from a formula, that is "=Control!A9" so i need the macro to pull the "A9" part only

and paste it into D7

please must be a macro not a formula


Thanks

Tony

 

[Peter_SSs]

Are all the formulas just like that or might there be ones like these and if so what should these results be?

=A5+$B$33+200
=SUM(A34:K$15)
=INDEX(A:A,5)

 

[tonywatsonhelp]

they are all like my example

 

[tonywatsonhelp]

bump

 

[Michael M]

@Rick Rothstein posted this previously....All credit to Rick
I've modified to suit

Sub MM1()
    Dim Rng As Range, Frm As String
    Dim WF As WorksheetFunction, Sh As String, Ref As String
'   *** Change sheet and range references to suit ***
    Set Rng = Worksheets("Control").Range("E1")
    Frm = Rng.Formula
    Set WF = WorksheetFunction
    Ref = Right(Frm, Len(Frm) - WF.Find("!", Frm, 1))
    Range("D7").value= Ref
End Sub

 

[Rick Rothstein]

Seems like an awful lot of code for something I would have posted. I have no recollection of that code, but my thinking now would be for something a lot more condensed...

Sub RR1()
  Range("D7").Value = Split(Worksheets("Control").Range("E1").Formula, "!")(1)
End Sub

 

[Michael M]

My sincerest apologies Rick.......@Andrew Poulsom was the writer......?

 

[Peter_SSs]

I'm not seeing anything that suggests the formula is on the 'Control' worksheet. To me the inference is quite the opposite.
So assuming the formula is on the active sheet in cell C7 & the result is to go on the active sheet cell D7 ..

Sub ExtractRef()
  Range("D7").Value = Split(Range("C7").Formula, "!")(1)
End Sub

 

[tonywatsonhelp]

Thank you Peter, Rick and Michael,
This is everything I needed, so a massive thanks for that
Tony

 

[Peter_SSs]

You're welcome.