Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute cell reference macro | Excel Discussion (Misc queries) | |||
Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
Creating a formula using absolute referencing - I think!! | Excel Worksheet Functions | |||
absolute reference | Excel Worksheet Functions | |||
Absolute Reference | Excel Worksheet Functions |