Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA cut and paste bug?
The following code illustrates what looks like a bad bug to me:
Sub CutAndPasteBug() Dim rangeToMove As Range Dim destRange As Range Dim destWS As Worksheet Dim sourceWs As Worksheet Set sourceWs = Sheets("sheet1") Set destWS = Sheets("sheet2") Set rangeToMove = sourceWs.Range("A1") Set destRange = destWS.Range("F5") rangeToMove.Cut destWS.Paste destRange MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _ Chr(13) & Chr(13) & "It should be sheet2!!!!" End Sub The variable rangeToMove should end up referring to sheet2 F5, not sheet1 F5 after the cut is done. The cell address changes from A1 to F5, but the variable is still pointing to sheet1. The entry in sheet 1A1 is moved to sheet2 F5. I've tested this in Excel 97, 2003, and 2007. Am I correct this is a bug, or is it by design? Comments back appreciated! Bob Flanagan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA cut and paste bug?
That is by design. The only way to move an object is with the key word Set.
Cut is a method of the range and ultimately it is just a copy and paste followed by a clear contents. -- HTH... Jim Thomlinson "Bob Flanagan" wrote: The following code illustrates what looks like a bad bug to me: Sub CutAndPasteBug() Dim rangeToMove As Range Dim destRange As Range Dim destWS As Worksheet Dim sourceWs As Worksheet Set sourceWs = Sheets("sheet1") Set destWS = Sheets("sheet2") Set rangeToMove = sourceWs.Range("A1") Set destRange = destWS.Range("F5") rangeToMove.Cut destWS.Paste destRange MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _ Chr(13) & Chr(13) & "It should be sheet2!!!!" End Sub The variable rangeToMove should end up referring to sheet2 F5, not sheet1 F5 after the cut is done. The cell address changes from A1 to F5, but the variable is still pointing to sheet1. The entry in sheet 1A1 is moved to sheet2 F5. I've tested this in Excel 97, 2003, and 2007. Am I correct this is a bug, or is it by design? Comments back appreciated! Bob Flanagan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA cut and paste bug?
Hi Bob -
I'm always hesitant to brand any apparent problem as a bug until a lot of eyes review it, but I think you're on to something here because the range component of the object's address changes independently of its sheet component... I agree that with you that the independent nature of such important location information is significantly inconsistent. It'll be interesting to see additional input on this thread topic. A common strategy to avoid such a problem (and other cascading problems due to cutting/moving) is to copy-paste-delete. The fact that it's a common strategy might be due in part to developers getting burned by the problem you describe. If feedback in this thread doesn't resolve the issue, you can contact Microsoft with your information at: http://support.microsoft.com/contactus/?WS=communities This location contains links to "Send Questions", "Report a bug", and others. -- Jay "Bob Flanagan" wrote: The following code illustrates what looks like a bad bug to me: Sub CutAndPasteBug() Dim rangeToMove As Range Dim destRange As Range Dim destWS As Worksheet Dim sourceWs As Worksheet Set sourceWs = Sheets("sheet1") Set destWS = Sheets("sheet2") Set rangeToMove = sourceWs.Range("A1") Set destRange = destWS.Range("F5") rangeToMove.Cut destWS.Paste destRange MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _ Chr(13) & Chr(13) & "It should be sheet2!!!!" End Sub The variable rangeToMove should end up referring to sheet2 F5, not sheet1 F5 after the cut is done. The cell address changes from A1 to F5, but the variable is still pointing to sheet1. The entry in sheet 1A1 is moved to sheet2 F5. I've tested this in Excel 97, 2003, and 2007. Am I correct this is a bug, or is it by design? Comments back appreciated! Bob Flanagan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA cut and paste bug?
Hi Bob,
Just to add to Jim's comments, a Range object is always tied to its worksheet. It can move around the sheet with cut, insert/delete rows etc, but can't change its parent without re-adoption. Similar applies with other objects. I can't see the problem with your example as you already know destRange. But if say you are setting a global range object that might be moved elsewhere by user you could do something like this - Set sourceWs = Sheets("sheet1") Set destWS = Sheets("sheet2") Set rangeToMove = sourceWs.Range("A1") rangeToMove.Name = "abc" Set destRange = destWS.Range("F5") rangeToMove.Cut destWS.Paste destRange Set rangeToMove = Names("abc").RefersToRange Names("abc").Delete MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _ Chr(13) & Chr(13) & "It should be sheet2!!!! and indeed it is Sheet2" Regards, Peter T "Bob Flanagan" wrote in message . .. The following code illustrates what looks like a bad bug to me: Sub CutAndPasteBug() Dim rangeToMove As Range Dim destRange As Range Dim destWS As Worksheet Dim sourceWs As Worksheet Set sourceWs = Sheets("sheet1") Set destWS = Sheets("sheet2") Set rangeToMove = sourceWs.Range("A1") Set destRange = destWS.Range("F5") rangeToMove.Cut destWS.Paste destRange MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _ Chr(13) & Chr(13) & "It should be sheet2!!!!" End Sub The variable rangeToMove should end up referring to sheet2 F5, not sheet1 F5 after the cut is done. The cell address changes from A1 to F5, but the variable is still pointing to sheet1. The entry in sheet 1A1 is moved to sheet2 F5. I've tested this in Excel 97, 2003, and 2007. Am I correct this is a bug, or is it by design? Comments back appreciated! Bob Flanagan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Excel to Word : Paste specialPaste Link Excel Chart Obj doesn't | Charts and Charting in Excel | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) |