Change "relative" to "absolute" (database)

  • Thread starter Thread starter Len Dolby
  • Start date Start date
L

Len Dolby

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 (e-mail address removed)
 
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 ?
 
The values you can supply to the 4th argument of the the convertformula
function are:

? 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.
 
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
 
Back
Top