Routine to change datacell range BS5:BU80 from relative to absolute values,
please ?

(example, cell BS5 content "=C5+C80+C155+C305"

Excel method works only on single cells (select cell, highlight content in
function bar, press function key f4 3 times) BUT it doesn't work on a
highlighted range, neither can I construct an effective macro via macro
wizard.
I have 4 such data blocks (900 cells), and a short deadline for final report
print (tonight) which are these data blocks, data sorted (and data sort
doesn't work with relative values).
Any suggestion appreciated !

LEN

Len,

Try something like the following:

Sub ConvertFormulas()
Dim Rng As Range
On Error GoTo EndProc:
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray = True Then
Rng.FormulaArray = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
Else
Rng.Formula = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
End If
Next Rng
EndProc:
End Sub

Change the range A1:A10 to the appropriate cell range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Len Dolby" wrote in message
...
Routine to change datacell range BS5:BU80 from relative to absolute
values,
please ?

(example, cell BS5 content "=C5+C80+C155+C305"

Excel method works only on single cells (select cell, highlight content in
function bar, press function key f4 3 times) BUT it doesn't work on a
highlighted range, neither can I construct an effective macro via macro
wizard.
I have 4 such data blocks (900 cells), and a short deadline for final
report
print (tonight) which are these data blocks, data sorted (and data sort
doesn't work with relative values).
Any suggestion appreciated !

LEN

This worked superbly. However, my datarange still will not sort.
It is now entirely populated by references to external data cells (and ALL
external cells are now also absolute refs, where appropriate)
I think sort failure may be because I did not include brackets in the source
cells i.e. =$a$5+$b$5, not =($a$5+$b$5) - seem to recall reading this as a
possibilty, NB I am a relative newbie ...

So - routine to add brackets, similar to below ?
And, the REVERSE of the "Absolute" routine (or, to remove the absolute
COLUMN, so $A$% becomes $A5 ? this so i can copy/paste corrected cells ?
"Chip Pearson" wrote in message
...
Len,

Try something like the following:

Sub ConvertFormulas()
Dim Rng As Range
On Error GoTo EndProc:
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray = True Then
Rng.FormulaArray = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
Else
Rng.Formula = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
End If
Next Rng
EndProc:
End Sub

Change the range A1:A10 to the appropriate cell range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Len Dolby" wrote in message
...
Routine to change datacell range BS5:BU80 from relative to absolute
values,
please ?

(example, cell BS5 content "=C5+C80+C155+C305"

Excel method works only on single cells (select cell, highlight content
in
function bar, press function key f4 3 times) BUT it doesn't work on a
highlighted range, neither can I construct an effective macro via macro
wizard.
I have 4 such data blocks (900 cells), and a short deadline for final
report
print (tonight) which are these data blocks, data sorted (and data sort
doesn't work with relative values).
Any suggestion appreciated !

LEN

The values you can supply to the 4th argument of the the convertformula
function a

? xlAbsolute
1
? xlAbsRowRelColumn
2
? xlRelRowAbsColumn
3
? xlRelative
4


Not sure why Chip chose to use True (which evaluated to 1 and would be
equivalent to xlAbsolute).

Replace true with one of the other constants.

Adding brackets will have no affect on how the sort is performed as they
will not change the value produced. The value produced determines what the
sort order is.

If the cell reference is to a value in the same row that is also sorted, it
should be row relative. If it refers to a cell outside the area to be
sorted, it should be row abosolute.

--
Regards,
Tom Ogilvy

"Len Dolby" wrote in message
...
This worked superbly. However, my datarange still will not sort.
It is now entirely populated by references to external data cells (and ALL
external cells are now also absolute refs, where appropriate)
I think sort failure may be because I did not include brackets in the
source
cells i.e. =$a$5+$b$5, not =($a$5+$b$5) - seem to recall reading this as
a
possibilty, NB I am a relative newbie ...

So - routine to add brackets, similar to below ?
And, the REVERSE of the "Absolute" routine (or, to remove the absolute
COLUMN, so $A$% becomes $A5 ? this so i can copy/paste corrected cells ?
"Chip Pearson" wrote in message
...
Len,

Try something like the following:

Sub ConvertFormulas()
Dim Rng As Range
On Error GoTo EndProc:
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray = True Then
Rng.FormulaArray = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
Else
Rng.Formula = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
End If
Next Rng
EndProc:
End Sub

Change the range A1:A10 to the appropriate cell range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Len Dolby" wrote in message
...
Routine to change datacell range BS5:BU80 from relative to absolute
values,
please ?

(example, cell BS5 content "=C5+C80+C155+C305"

Excel method works only on single cells (select cell, highlight
content
in
function bar, press function key f4 3 times) BUT it doesn't work on a
highlighted range, neither can I construct an effective macro via
macro
wizard.
I have 4 such data blocks (900 cells), and a short deadline for final
report
print (tonight) which are these data blocks, data sorted (and data
sort
doesn't work with relative values).
Any suggestion appreciated !

LEN

Tom, thanks ! Your suggested amendment to argument 4 works fine, I've used
it.
Am sending you the file as I can't get a correct datasort, despite doing
what you suggested
Please reply soonest, I really am up against a deadline - have to print and
then hand=deliver prior to a Club meeting
Sorry to claim priority I know you help a lot of folk !
Sincerely, Len


"Tom Ogilvy" wrote in message
...
The values you can supply to the 4th argument of the the convertformula
function a

? xlAbsolute
1
? xlAbsRowRelColumn
2
? xlRelRowAbsColumn
3
? xlRelative
4


Not sure why Chip chose to use True (which evaluated to 1 and would be
equivalent to xlAbsolute).

Replace true with one of the other constants.

Adding brackets will have no affect on how the sort is performed as they
will not change the value produced. The value produced determines what
the
sort order is.

If the cell reference is to a value in the same row that is also sorted,
it
should be row relative. If it refers to a cell outside the area to be
sorted, it should be row abosolute.

--
Regards,
Tom Ogilvy

"Len Dolby" wrote in message
...
This worked superbly. However, my datarange still will not sort.
It is now entirely populated by references to external data cells (and
ALL
external cells are now also absolute refs, where appropriate)
I think sort failure may be because I did not include brackets in the
source
cells i.e. =$a$5+$b$5, not =($a$5+$b$5) - seem to recall reading this
as
a
possibilty, NB I am a relative newbie ...

So - routine to add brackets, similar to below ?
And, the REVERSE of the "Absolute" routine (or, to remove the absolute
COLUMN, so $A$% becomes $A5 ? this so i can copy/paste corrected cells ?
"Chip Pearson" wrote in message
...
Len,

Try something like the following:

Sub ConvertFormulas()
Dim Rng As Range
On Error GoTo EndProc:
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray = True Then
Rng.FormulaArray = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
Else
Rng.Formula = Application.ConvertFormula(Rng.Formula, _
xlA1, xlA1, True)
End If
Next Rng
EndProc:
End Sub

Change the range A1:A10 to the appropriate cell range.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Len Dolby" wrote in message
...
Routine to change datacell range BS5:BU80 from relative to absolute
values,
please ?

(example, cell BS5 content "=C5+C80+C155+C305"

Excel method works only on single cells (select cell, highlight
content
in
function bar, press function key f4 3 times) BUT it doesn't work on
a
highlighted range, neither can I construct an effective macro via
macro
wizard.
I have 4 such data blocks (900 cells), and a short deadline for
final
report
print (tonight) which are these data blocks, data sorted (and data
sort
doesn't work with relative values).
Any suggestion appreciated !

LEN