Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
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...) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote:
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
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" wrote in message ft.com... 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...) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
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 : On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote: 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 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
Hi
[...] (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 Hi I could reproduce this behaviour with Excel 97. You could also see the difference after unmerging the cells. Frank |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
I can reproduce in Excel 2003 SP1 and 2000 SP3 under Windows 2000 and
Excel XP SP2 under Windows Me. Ron, are you sure you followed the directions correctly? It seems odd that MS would correct in an older version and not in the latest version. Jerry Ron Rosenfeld wrote: On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote: 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 |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
I received a Merge Warning using Excel 2003 sp-1 from Office Pro on Windows
XP Pro sp-2. Error Checking did not catch it, though. tj "Ron Rosenfeld" wrote: On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote: 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 |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
That wouldn't necessarily be correcting an older version, it might just be a
'feature' introduced into a later version. Bob "Jerry W. Lewis" wrote in message ... I can reproduce in Excel 2003 SP1 and 2000 SP3 under Windows 2000 and Excel XP SP2 under Windows Me. Ron, are you sure you followed the directions correctly? It seems odd that MS would correct in an older version and not in the latest version. Jerry Ron Rosenfeld wrote: On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote: 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 |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
On Sun, 21 Nov 2004 04:19:21 -0800, "keepITcool" wrote:
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) I think they were clear. With your SUB and my setup (Windows XP Pro SP2; Excel 2002 SP3) after running the SUB, I get the merge warning. After clicking OK to clear the warning, A1:B1 has a dotted line around the merged cells and I get a message in the lower bar "Select destination and press ENTER or choose Paste". Prior to doing anything, there is a 1 in A1:B1, and a 3 in C:D1, and an 8 in E1. After hitting ENTER, or Paste and then <esc, the dotted line clears, and there are 1's in the merged cells and a 2 in E1. However, if I hit <esc, then there remains a 1 in A1:B1; a 3 in C1:D1 and an 8 in E1. --ron |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
On Sun, 21 Nov 2004 13:41:11 GMT, "Jerry W. Lewis"
wrote: I can reproduce in Excel 2003 SP1 and 2000 SP3 under Windows 2000 and Excel XP SP2 under Windows Me. Ron, are you sure you followed the directions correctly? It seems odd that MS would correct in an older version and not in the latest version. Jerry Yes, but also see what happens when running the SUB. (See a prior post). --ron |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
proves my point, doesn't it?
-- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron Rosenfeld wrote : However, if I hit <esc, then there remains a 1 in A1:B1; a 3 in C1:D1 and an 8 in E1. |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
On Sun, 21 Nov 2004 13:41:11 GMT, "Jerry W. Lewis"
wrote: I can reproduce in Excel 2003 SP1 and 2000 SP3 under Windows 2000 and Excel XP SP2 under Windows Me. Ron, are you sure you followed the directions correctly? It seems odd that MS would correct in an older version and not in the latest version. Jerry OK, I see what the difference was. After Paste Special Formats, I hit <enter or <paste per the bottom line (same as in the SUB). In that case the result is normal. However, if I hit <esc or if I use the Format Painter for a single operation, then I get the same error as K. --ron |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
the warning message is from the actual merge method. and you SHOULD get it. the point is the PASTEVALUE merges cells without the warning and without deleting the values in the 'non first' merged cells. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam tjtjjtjt wrote : I received a Merge Warning using Excel 2003 sp-1 from Office Pro on Windows XP Pro sp-2. Error Checking did not catch it, though. tj "Ron Rosenfeld" wrote: On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote: 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 |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
On Sun, 21 Nov 2004 04:19:21 -0800, "keepITcool" wrote:
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) See my later response to Jerry. With your initial operation, and with the SUB, after completing your steps, if I then hit <esc (and not <enter or <paste), then I can reproduce your feature. Also, using the format painter in a single operation will reproduce the problem. If you use it for a multiple operation (i.e. double click on the format painter), then you have the opportunity to hit <enter and get the merge. HOWEVER, and this would seem to be ANOTHER bug, if you do the PASTE FORMAT on a merged cell, and if you hit <enter or <paste when done, C1:D1 do merge, but the VALUE from A1 gets copied over into C1. --ron |
#15
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Bug? Multiple values in merged cells
On Sun, 21 Nov 2004 02:42:29 -0800, "keepITcool" wrote:
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...) There are some MSKB articles referencing XL97 and 2000 with regard to problems with Paste Special and Format Painter and Merged cells. It sounds as if they have not fully developed this "feature" :-). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
Counting values across merged cells. | Excel Discussion (Misc queries) | |||
Seperate Merged Cells with multiple rows? | Excel Discussion (Misc queries) | |||
paste special | values should work with merged cells | Excel Discussion (Misc queries) | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |