Anchoring multiple cells at once

[Bengo]

Would anyone know how to "anchor" several cells simultaneously without going into each cell and pressing F4?

Many thanks.

 

[Bengo]

Thanks Joe, bit beyond me I think at the minute but I'll get there! In the meantime the add in is working perfectly! Thanks!

 

[Jon von der Heyden]

Or with VBA you can copy the formula of cells in a particular range to an array and then write the array back to the paste destination:

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo1()
    [COLOR=blue]Dim[/COLOR] rngCopy [COLOR=blue]As[/COLOR] Range, rngDestination [COLOR=blue]As[/COLOR] Range
    [COLOR=blue]Dim[/COLOR] arrFormulas()
 
    [COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] Catch
        [COLOR=blue]Set[/COLOR] rngCopy = Application.InputBox( _
                                    prompt:="Copy range:", _
                                    Title:="Copy", _
                                    [COLOR=blue]Type[/COLOR]:=8)
        [COLOR=blue]Set[/COLOR] rngDestination = Application.InputBox( _
                                    prompt:="Paste cell:", _
                                    Title:="Paste", _
                                    [COLOR=blue]Type[/COLOR]:=8)
    [COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] 0
 
    arrFormulas = rngCopy.Formula
    rngDestination.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count) = arrFormulas
Catch:
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

EDIT: And if you want to paste formats too then you can bolt on a bit (before Catch: )

rngCopy.Copy
rngDestination.PasteSpecial xlFormats

 

[Bengo]

Hi Jon,

despite this being a ridiculously long time ago I wanted to say thanks for your workings above!! Believe it or not I actually never saw this reply, my email didn't come through... it's only when looking at past posts that i saw this!

Anyway better late than never I suppose

Thanks again

 

[Jon von der Heyden]

It's been a while . You're welcome! But now I just can't help posting a better answer.

There is a VBA method called ConvertFormula that can be used to toggle A1/R1C1 notation. We can exploit this method to convert ranges to absolute, e.g:

ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute, ActiveCell)

So using this method one could loop a range of cells and rewrite the formula in each cell, converting references to absolute in the process.

 

[Bengo]

Fantastic!!!

definately going to try that one! I have some work coming up which I'll try it with later on thanks!!

If you have a chance to look at my latest thread via my profile you seem like you could be the one to opint me in the right direction....! if you have the time of course!!

Many many thanks again!

 

[tcowap]

It's been a while . You're welcome! But now I just can't help posting a better answer.

There is a VBA method called ConvertFormula that can be used to toggle A1/R1C1 notation. We can exploit this method to convert ranges to absolute, e.g:

ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute, ActiveCell)

So using this method one could loop a range of cells and rewrite the formula in each cell, converting references to absolute in the process.

First I have to say-this is amazing! Thank you!

I have one problem-it works on most of my formulas but in others it results in the cell being emptied and just saying #value, do you know why this could be? Here's an example of the formulas:

It works on this formula:

=SUM('Consolidated P&L'!$R$40:$R$66,'Consolidated P&L'!$R$68:$R$76)/1000-('2011'!$N$75/1000)

but not on this formula:

=SUM('Consolidated P&L (2)'!R36:R38,'Consolidated P&L (2)'!R41:R67,'Consolidated P&L (2)'!R69:R77)/1000-('2010'!N76/1000)

Any ideas why? Could it be something to do with the number at the end of the sheet name?

Thanks,

Tom