Switching cells
 

Is there a macro out there where I can select array 1 and array 2 and switch
their contents without having to copy/cut/past array 2 to array 3, array 1 to
array 2, and then array 3 to array 1?

Thanks,
Byron
--
Unsophisticated User

No. If you replace item 1 with item 2 (item = cell or array element) then
item 1 no longer exists unless you preserved it first.

--
Jim
"DrJBN" wrote in message
...
Is there a macro out there where I can select array 1 and array 2 and
switch
their contents without having to copy/cut/past array 2 to array 3, array 1
to
array 2, and then array 3 to array 1?

Thanks,
Byron
--
Unsophisticated User

Yes, I understand this, but I had hoped that a macro existed (i.e,. one
someone had written) that would take care of all the copying, cutting, and
pasting to and from the third array to preserve the list being overwritten.
Thanks for the reply.
--
Unsophisticated User


"Jim Rech" wrote:

No. If you replace item 1 with item 2 (item = cell or array element) then
item 1 no longer exists unless you preserved it first.

--
Jim
"DrJBN" wrote in message
...
Is there a macro out there where I can select array 1 and array 2 and
switch
their contents without having to copy/cut/past array 2 to array 3, array 1
to
array 2, and then array 3 to array 1?

Thanks,
Byron
--
Unsophisticated User

JBN

Code from the late and sorely missed Frank Kabel.

Hi
this would<be only possible with VBA (using an event procedure). Not
possible with formulas.

For your example put the following code in your worksheet module (not
in a standard module). To get into the worksheet module right-click on
the tab name and choose 'Code'

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A:B")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
if .value <"" then
select case .column
case 1
.offset(0,1).value = .value
case 2
.offset(0,-1).value = .value
end select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub



Gord Dibben Excel MVP

On Tue, 3 May 2005 11:32:05 -0700, "DrJBN"
wrote:

Yes, I understand this, but I had hoped that a macro existed (i.e,. one
someone had written) that would take care of all the copying, cutting, and
pasting to and from the third array to preserve the list being overwritten.
Thanks for the reply.

Thanks for the code!
--
Unsophisticated User


"Gord Dibben" wrote:

JBN

Code from the late and sorely missed Frank Kabel.

Hi
this would<be only possible with VBA (using an event procedure). Not
possible with formulas.

For your example put the following code in your worksheet module (not
in a standard module). To get into the worksheet module right-click on
the tab name and choose 'Code'

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A:B")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
if .value <"" then
select case .column
case 1
.offset(0,1).value = .value
case 2
.offset(0,-1).value = .value
end select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub



Gord Dibben Excel MVP

On Tue, 3 May 2005 11:32:05 -0700, "DrJBN"
wrote:

Yes, I understand this, but I had hoped that a macro existed (i.e,. one
someone had written) that would take care of all the copying, cutting, and
pasting to and from the third array to preserve the list being overwritten.
Thanks for the reply.

Thanks for the feedback.

Frank's legacy carries on.


Gord

On Fri, 6 May 2005 07:41:17 -0700, "DrJBN"
wrote:

Thanks for the code!