john-lau wrote on 05/07/2011 11:47 ET :
Hello,

I have one excel book , which have 30 sheets (28 sheets are named as
different
account manager's name),
1 sheet name as "summary" , last sheet name as "system
data"

I would like to use macro to change relative reference to absolute reference
(only column absolute!!) on sheet"summary".
May I ask how to modify the following code to achieve absolute reference
:column). Thanks

Sub CycleAbsRel()
Dim inRange as Range, oneCell As Range
Static absRelMode As Long
absRelMode = (absRelMode Mod 4) + 1
Set inRange = Selection.SpecialCells(xlCellTypeFormulas)
If Not (inRange Is Nothing) Then
For Each oneCell In inRange
With oneCell
.FormulaR1C1 = Application.ConvertFormula(.FormulaR[1]C1, xlR1C1, xlR1C1,
absRelMode, oneCell)
End With
Next oneCell
End If
End Sub

Dear sir,

thanks for your reply. May you teach me how to modify the macro, therefore, I
can apply it for specific/current sheet of the file. For example, there is a
workbook called DB performance, it has 4 sheets, I only want macro (absolute
reference) applied on specific sheet, such as Premier..

Secondly, for example, in the sheet "premier", Column A, there are two
sources of data (from two sheets, one is "David" and "system
report".
The formula like this
in the sheet "premier" A 3 , the formula is "david" A1+
"system report" A1+"david" B1+ "system report" B1.
After I use the macro, it shows # Value..... Is it my formula has problems?