Creating an Absolute Reference Macro

  • Thread starter Thread starter Renegade Snail
  • Start date Start date
R

Renegade Snail

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
 
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
'--------------------------------------------
 
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
 
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
 
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"))))
 
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")
 
Hi all,

Like the code below that creates absolute references, is there code I could use to remove absolute references from a range of cells?

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
​
 
Back
Top