cell value swapping

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

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

--
Regards,
Tom Ogilvy

"pj" <gullivant@yahoo.com> wrote in message
news:221201c4fcbd$8218fa30$a601280a@phx.gbl...
> 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

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

>-----Original Message-----
>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
>
>--
>Regards,
>Tom Ogilvy
>
>"pj" <gullivant@yahoo.com> wrote in message
>news:221201c4fcbd$8218fa30$a601280a@phx.gbl...
>> 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
>
>
>.
>

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

--
Regards,
Tom Ogilvy

"pj" <gullivant@yahoo.com> wrote in message
news:0f2501c4fcbf$dad5bc10$a501280a@phx.gbl...
> Wow, that was a really fast response. Thanks for the help.
> pj
>
> >-----Original Message-----
> >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
> >
> >--
> >Regards,
> >Tom Ogilvy
> >
> >"pj" <gullivant@yahoo.com> wrote in message
> >news:221201c4fcbd$8218fa30$a601280a@phx.gbl...
> >> 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
> >
> >
> >.
> >