VBA: Copy and Move Cells

[pm3dmz]

I have 3 user defined ranges, and I want to swap contents as follows:

Dim R As Range
Dim N As Range
Dim Temp As Range

...

R.Copy Destination:=Temp
N.Select
R.Cut Destination:=N
Temp.Copy Destination:=R

Everything works except for the last copy (Temp -> R). Nothing happens. Any idea why? Thanks!

 

[Anthony47]

Can you show how you set R, N and Temp? Or, better, the whole code.

Bye

 

[pm3dmz]

Can you show how you set R, N and Temp? Or, better, the whole code.

Bye

Thanks -- without inundating with code, here are the relevant details:

Set R = Selection
Set Temp = Range(Cells(LRow, LCol), Cells(LLRow, LLCol)) 'LRow and LCol are chosen beyond the last used cell in the spreadsheet​

Set N = Application.InputBox(Prompt:="Please Select Range to Replicate to", Title:="Range Select", Type:=8)​

Set N = N.Resize(R.Rows.Count, R.Columns.Count)​

The first copy and cut work fine. The last copy does not work.
I also notice that after "R.Cut Destination:=N", if I try "MsgBox(N.Address)" it fails (but works if I insert it before that line). Does cutting and pasting somehow destroy the range references?

I have also tried:

Set R2 = R
Set Temp2 = Temp

R.Copy Destination:=Temp
N.Select
R.Cut Destination:=N

MsgBox (Temp2.Address)
MsgBox (R2.Address)
Temp2.Copy Destination:=R2​

Again, the last copy does nothing, even though the addresses of the two ranges are reported correctly by MsgBox.
Either this is some kind of bug, or I have a fundamental misunderstanding...

 

[Anthony47]

The problem is with the Set R=etc etc and the next R.Cut; eventually R has been moved to N and thus Temp will not be copied to the original position but to N.

Suggestion: don't use Range objects, but strings to hold the range address; for example:

Dim N As Range
Dim sR As String, sTemp As String

sR = Selection.Address
sTemp = Range(Cells(LRow, LCol), Cells(LLRow, LLCol)).Addres  ' And of course you could use directly a string

Set N = Application.InputBox(Prompt:="Please Select Range to Replicate to", Title:="Range Select", Type:=8)
Range(sR).Copy Destination:=Range(sTemp)
Range(sR).Cut Destination:=N
Range(sTemp).Range("A1").Resize(Selection.Rows.Count, Selection.Columns.Count).Copy Destination:=Range(sR)

And, by the way, did you check if just copying Selection to N (rather than Cutting, and thus forgetting about R --> Temp, Temp --> R) you get the result you need?

Bye

 

[pm3dmz]

Yes, thanks -- saving and using the address strings solves the problem.
That said, the behavior of the cut command and how it affects range objects is interesting and unexpected (to me).
It seems that any range object that references the cells that were involved in the move (either source or destination) are affected.

And yes, cut is what I needed, as I wanted to maintain the original cell references in formulas.

Thanks again!

 

[Anthony47]

Set creates a reference to the real object, not a copy; that's why when the object (the range) is moved the reference also moves.

Bye

 

[Rick Rothstein]

I have 3 user defined ranges, and I want to swap contents as follows:

Dim R As Range
Dim N As Range
Dim Temp As Range

...

R.Copy Destination:=Temp
N.Select
R.Cut Destination:=N
Temp.Copy Destination:=R

Everything works except for the last copy (Temp -> R). Nothing happens. Any idea why? Thanks!

The purpose of your code is to swap the contents of range R and range N, correct? Here is a simple and quite fast method to do so...

  Dim R As Range
  Dim N As Range
  Dim Rarr As Variant
  Dim Narr As Variant
  
  ......
  
  Rarr = R.Value
  Narr = N.Value
  N = Rarr
  R = Narr

 

[Anthony47]

Hummm....

And yes, cut is what I needed, as I wanted to maintain the original cell references in formulas.

I think that the op needs the formulas

Bye

 

[Rick Rothstein]

Hummm....
I think that the op needs the formulas

I missed that. If that means the exact formulas as they appear in the cells (without their ranges changing due to the move), then this would work...

  Dim R As Range
  Dim N As Range
  Dim Rarr As Variant
  Dim Narr As Variant
  
  ......
  
  Rarr = R.Formula
  Narr = N.Formula
  N = Rarr
  R = Narr