Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell value swapping
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell value swapping
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell value swapping
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" wrote in message ... 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Swapping two word and placing them in another cell | Excel Worksheet Functions | |||
Swapping two words in a cell and placing them in another cell | Excel Worksheet Functions | |||
Swapping two words around in teh one cell | Excel Worksheet Functions | |||
Swapping two words in one cell | Excel Worksheet Functions | |||
swapping columns?? | Excel Discussion (Misc queries) |