Switching cells

D

DrJBN

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
 
J

Jim Rech

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.
 
D

DrJBN

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.
 
G

Gord Dibben

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
 
D

DrJBN

Thanks for the code!
--
Unsophisticated User


Gord Dibben said:
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
Click to expand...
 
Top