cell value swapping

[pj]

I have a region of cells that need to meet certain
conditions to reach a goal. All the values of these cells
will remain the same within the region. However, depending
on the results of the conditions some of these values may
need to be swapped.

I would like to put a command button on the sheet such
that when two cells are selected (anywhere within the
region) then clicking the command button will find those
cells and swap the values in them.

Is this possible and how? TIA.
pj

 

[Tom Ogilvy]

Private CommandButton1_Click()
dim v as variant
if Typename(Selection) <> "Range" then exit sub
If selection.Count > 2 or _
Selection.Count < 2 then Exit sub
if Areas.count = 1 then
v = selection(1)
selection(1) = selection(2)
selection(2) = v
else
v = selection.Areas(1).Value
selection.Areas(1).Value = _
selection.Areas(2).Value
selection.Areas(2).Value = v
end if
end if
End Sub

 

[pj]

Wow, that was a really fast response. Thanks for the help.
pj

 

[Tom Ogilvy]

Maybe too fast. A couple of typos.

Also, you will get better results (in my opinion) if you change the
takefocusonclick property of the commandbutton to False. Here is the
revised code:

Private Sub CommandButton1_Click()
Dim v As Variant
If TypeName(Selection) <> "Range" Then Exit Sub
If Selection.Count > 2 Or _
Selection.Count < 2 Then Exit Sub
If Selection.Areas.Count = 1 Then
v = Selection(1)
Selection(1) = Selection(2)
Selection(2) = v
Else
v = Selection.Areas(1).Value
Selection.Areas(1).Value = _
Selection.Areas(2).Value
Selection.Areas(2).Value = v
End If

End Sub