Creating an Absolute Reference Macro
 

hey i was wondering if anyone could help me with this problem, wheneve
i use a macro to delete or insert a row on a sheet it changes all th
cell references on my other sheet when i don't want it too, i know tha
i can use absolute referencing, but i would prefer a macro to do it fo
me cause i have around 5,000 which need to be given absolut
references

any tips would be greatly appreciate

--
Message posted from http://www.ExcelForum.com

Creating an Absolute Reference Macro
 

Something like this ..................?
'--------------------------------------
Sub CONVERT_FORMULAS()
For Each c In ActiveSheet.UsedRange.Cells
If Left(c.Formula, 1) = "=" Then
c.Formula = Application.ConvertFormula( _
Formula:=c.Formula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlAbsolute)
End If
Next
End Sub
'--------------------------------------------

--
Message posted from http://www.ExcelForum.com

Creating an Absolute Reference Macro
 

Snail

This macro run on a selected range will turn all formula cell references to
Absolute.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Gord Dibben Excel MVP

On Thu, 26 Feb 2004 01:47:01 -0600, Renegade Snail
wrote:

hey i was wondering if anyone could help me with this problem, whenever
i use a macro to delete or insert a row on a sheet it changes all the
cell references on my other sheet when i don't want it too, i know that
i can use absolute referencing, but i would prefer a macro to do it for
me cause i have around 5,000 which need to be given absolute
references

any tips would be greatly appreciated


---
Message posted from http://www.ExcelForum.com/

Creating an Absolute Reference Macro
 

thanks gord and brian, ill try them as soon as i get home from uni and
then post if they were succesful or not

Thanks once again
Snail


---
Message posted from http://www.ExcelForum.com/

Creating an Absolute Reference Macro
 

Hey that macro formula didn't work with the formula that i had in th
cell so ill post what is contain within the cell and hopefully tha
might give you a better insight....cheers Snail

=IF(LEFT('Master Roster'!$C$13,2)="RN",VLOOKUP('Maste
Roster'!$E$13,RN_shiftcode_table,3,FALSE),IF(LEFT( 'Maste
Roster'!$C$13,2)="EN",VLOOKUP('Maste
Roster'!$E$13,EN_shiftcode_table,3,FALSE),IF(LEFT( 'Maste
Roster'!$C$13,2)="PC",VLOOKUP('Maste
Roster'!$E$13,PCW_shiftcode_table,3,FALSE),IF(LEFT ('Maste
Roster'!$C$13,1)="D",VLOOKUP('Maste
Roster'!$E$13,DON_shiftcode_table,3,FALSE),IF(LEFT ('Maste
Roster'!$C$13,2)="TH",VLOOKUP('Maste
Roster'!$E$13,TH_shiftcode_table,3,FALSE),"USC"))) )

--
Message posted from http://www.ExcelForum.com

Creating an Absolute Reference Macro
 

My code suggestion and Gord's are practically identical.
Your sample formula already contains Absolute references.

Perhaps you could let us know exactly what you are trying to do.

Using named ranges should automatically compensate for rows bein
deleted there.

If you delete a cell that is referred to elsewhere, you will naturall
get a #REF! error in the cell that depends on it ("it is a feature, no
a bug")

--
Message posted from http://www.ExcelForum.com