Bug? Multiple values in merged cells

[keepITcool]

I found that merged cells can contain multiple values.

Steps to reproduce:

Type 1,2,3,4 in a1:d1
type sum(a1:d1) in e1

Select a1:b1 and merge
Warning : MultipleData, overwrite?
Say yes to merge

Select the merged a1:b1 cells
Copy
Select c1
PasteSpecial Formats

No warning.. no overwrite.

c1:d1 are now merged
BUT d1 still contains a value...
and the SUM of a1:d1 = 8 !!

Also happens with FormatPainter etc
Behaviour observed in xl97,xlXP and xl2003

Error checking will find no fault in the sheet...
and you can spend ages to find out WHY your cross
sums dont match!

(although now that i found this behaviour I'll might
have some use for it...)

 

[Ron Rosenfeld]

I found that merged cells can contain multiple values.

Steps to reproduce:

Type 1,2,3,4 in a1:d1
type sum(a1:d1) in e1

Select a1:b1 and merge
Warning : MultipleData, overwrite?
Say yes to merge

Select the merged a1:b1 cells
Copy
Select c1
PasteSpecial Formats

No warning.. no overwrite.

c1:d1 are now merged
BUT d1 still contains a value...
and the SUM of a1:d1 = 8 !!

Also happens with FormatPainter etc
Behaviour observed in xl97,xlXP and xl2003

Error checking will find no fault in the sheet...
and you can spend ages to find out WHY your cross
sums dont match!

(although now that i found this behaviour I'll might
have some use for it...)

I just went through the steps you outlined above.

E1 initially showed '10'

After going through the steps, E1 now shows a '2'.

In other words, I could not reproduce your problem on my system:

Windows XP Pro SP2; Excel 2002 SP3


--ron

 

[Peter T]

Yes, and merge cells can also contain multiple formats.

Sub test1()
With [a1:b2]
..MergeCells = True
'.Merge
For i = 1 To 4
..Cells(i).Value = i
..Cells(i).Interior.ColorIndex = i + 2
Next
''look first then manually or prog' unmerge
''four colour formats recovered ?
'.MergeCells = False
'.UnMerge
End With
End Sub

Above applies formats after merging. But I've also had situation where Merge
is done after formats have been applied, formats of the "other" cells
retained but hidden. At the moment I can't seem to recreate but I'm sure it
can occur. For my purposes this scenario can be problematic when polling
formatted cells.

There can also be a difference between
rge.Merge vs rge.MergeCells = False

Regards,
Peter

 

[keepITcool]

maybe my steps were not clear?

Sub ReproBug()

With Range("a1:e1")
.Clear
.Value = Array(1, 2, 3, 4, "=sum(a1:d1)")
With .Cells(1).Resize(, 2)
.Merge
.Copy
End With
.Cells(3).PasteSpecial xlFormats
End With

End Sub

gives me : 1 merge warning and 8 in e1
(xl97,xlXP,xl2003)



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron Rosenfeld wrote :