Macro for absolute reference

[john-lau]

Hello

I have one excel book , which have 30 sheets (28 sheets are named as differen
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 referenc
(only column absolute!!) on sheet"summary"
May I ask how to modify the following code to achieve absolute referenc
:column). Thank

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

 

[Gord Dibben]

Start with these...............

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

 

[john-lau]

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

Hello

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

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

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

Dear sir

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

Secondly, for example, in the sheet "premier", Column A, there are tw
sources of data (from two sheets, one is "David" and "syste
report"
The formula like thi
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?

 

[Gord Dibben]

First...................revised macro.

Sub AbsoluteCol()
Dim wkbk As Workbook
Dim wksh As Worksheet
Set wkbk = Workbooks("DB Performance.xls")
Set wksh = wkbk.Sheets("Premier")
Dim cell As Range
For Each cell In wksh.UsedRange
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Second...................your formula does not look like any kind of valid
formula.

Are you summing the cells by using the + sign?

Please copy and paste the actual formula you have in A3

I assumed summing and tested with this formula in A3

=SUM(david!A1:B1)+SUM('system report'!A1:B1)

Ran the macro and returned this in A3

=SUM(david!$A1:$B1)+SUM('system report'!$A1:$B1)

No problems.


Gord

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?

 

[Tyler]

Hi Gord,

Would you explain to me the following piece of this code? I gather that it increments which rel/abs option is applied (right?). But i dont really understand the mechanics of how or why it does so.

"absRelMode = (absRelMode Mod 4) + 1"

Thanks,
Tyler

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

 

[joeu2004]

Would you explain to me the following piece of this code? [....]
"absRelMode = (absRelMode Mod 4) + 1"

It assigns the values 1, 2, 3 and 4 to absRelMode cyclically each time
CycleAbsRel is executed. Note that absRelMode is initially zero when VBA is
reset.

I do not have the context of the thread you are responding to.

However:

With oneCell
.FormulaR1C1 = Application.ConvertFormula(.FormulaR[1]C1, xlR1C1, xlR1C1,
absRelMode, oneCell)

First, I think there is a typo: the 1st parameter should be .FormulaR1C1.

Second, I'm not sure this will work in XL2007 and later.

According to offline help in XL2003, the 4th parameter is an
xlReferenceStyle type, which can take 4 values: xlAbsolute (1),
xlAbsRowRelColumn (2), xlRelRowAbsColumn (3) and xlRelative (4). Thus the
reason for absRelMode to take have the values 1 through 4.

(Arguably, it is not "good form" to assume those constant values for the
named constants. It would be "better form" to use absRelMode to index into
an array of the named constants.)

However, according to offline help in XL2007 and later, xlReferenceStyle is
defined to have only 2 values: xlA1 (1) and xlR1C1 (-4150).

(There you can see why we should not assume specific constant values for
named constants.)

I suspect the XL2007/later offline help documentation is incorrect, since
those named constants do not make sense for that parameter. But I am not
bothering to test it.