Serious sorting problems with cells containing references

[Paul_B]

Hi, I'm running into some serious bugs when I try to sort some
cells on a sheet. If all the cells contain simple text or
numbers, all is fine. But if any of the cells contains a
reference to another cell (outside of the sort range), the sort
is messed up badly. On some occasions the reference in question
has been deleted and the cell's previous contents restored.

The range in question is three columns wide and about 12 rows
long. It has a header row. Everything is very simple.

Thanks,
p.

 

[Bernie Deitrick]

Paul,

Select your cells with the references, and run the macro below. It will
convert all of the cell references to absolute references, which will not
update when they are sorted, as is happening now.

One assumption is that you use A1 style of referencing....

HTH,
Bernie
MS Excel MVP


Sub ConvertFormulasToAbsoluteReferences()
Dim mycell As Range
For Each mycell In Selection.SpecialCells(xlCellTypeFormulas)
mycell.Formula = Application.ConvertFormula(mycell.Formula, xlA1, xlA1,
True)
Next mycell
End Sub

 

[Paul_B]

Bernie,

I don't know what the A1 style of referencing is (as opposed to
range names, I suppose), but this macro worked beautifully. One
long line of your text wrapped, I discovered, but after
correcting that it worked instantly. One major problem solved,
and I think the macro will be useful for other purposes as well.

Thanks very much for your kind and speedy help.

p.

 

[Bernie Deitrick]

Paul,

Glad the hear that you got it to work.

A1 style referencing is when cell address are given as A1, B2, A1:B2, etc.

The other style if R1C1 style, where cell A1 is given as R1C1, B2 as R2C2
etc.

Bernie

 

[venkat]

hi

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com