Change ref type
 

Is there a simple way to change a range of absolute references to
relative?

Change ref type
 

Mat,

Right click your sheet tab, view code and paste this in. Select the range of
data and run the code

Sub Missive()
For Each c In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, _
FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
End If
Next
End Sub


Mike

"Matt" wrote:

Is there a simple way to change a range of absolute references to
relative?

Change ref type
 

Hi
Try also Ctrl+H
Find what $
Repace with (leave blank)

Click options
Lookin should be "formulas"

Replace all OK


--
Pecoflyer

Cheers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=32528

Change ref type
 

Mike, I tried the missive code by selecting the range and running but
the formulas are still showing relative references.

the formulas look like this: ='Nov W3'!E38

Does it matter that the reference is to another sheet?

Change ref type
 

Code:
--------------------
='Nov W3'!E38
--------------------
Is a relative reference.
Is this not what you want?


--
Pecoflyer

Cheers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=32528

Change ref type
 

Shoot. I got it backwards. I mean relative to absolute ( ='Nov W3'!
E38 to ='Nov W3'!$E$38)