A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

absolute reference fill



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 06:03 PM posted to microsoft.public.excel.worksheet.functions
jchick0909
external usenet poster
 
Posts: 11
Default absolute reference fill

i have 235 lines each linking to a cell in another tab. I need to make each
cell an absolute reference so i can copy them down again below and the same
cell is referenced.

How to I insert Absolute Reference in multiple cells. (i've tried macro's
but i failed).

Thanks in Advance
  #2  
Old June 2nd, 2010, 06:14 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default absolute reference fill

What failed when you tried macros?

Try one of these macros.

Select a range of cells then run the macro.

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

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 2 Jun 2010 10:03:05 -0700, jchick0909
wrote:

i have 235 lines each linking to a cell in another tab. I need to make each
cell an absolute reference so i can copy them down again below and the same
cell is referenced.

How to I insert Absolute Reference in multiple cells. (i've tried macro's
but i failed).

Thanks in Advance


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.