Expert: Tom Ogilvy - 11/9/2009

Question
QUESTION:
I know that "tab" "enter" etc change the active cell in a non contiguous range in the spreadsheet .

How do I get a macro to change the active cell in a non contiguous range or selection?

Thanks for any suggestions.
Flip

ANSWER: Flip,

just activate the cell you want to be the activeCell.  As long as it is in the current selection, the selection will remain the same and the activecell will be changed.  Here is a demonstration from the immediate window:

set r = Range("A1:B10,D1:E10,G1:H10")
r.select
? selection.address
$A$1:$B$10,$D$1:$E$10,$G$1:$H$10
? activecell.Address
$A$1
Range("G1").Activate
? selection.Address
$A$1:$B$10,$D$1:$E$10,$G$1:$H$10
? activecell.Address
$G$1


so in this case, a non-contigous range was selected of Columns A:B, D:E, G:H from rows 1 to 10 and by default, the top left corner was the activecell  (cell A1).

the command

Range("G1").Activate

changed the activecell to cell G1.  

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom,
Thanks for the quick reply.
I should be a bit more specific:
The purpose of the macro is to switch the contents of 2 non contiguous cells that will be selected by the person running the macro.  The possible combination of cells is >20,000 so I will have to pick up the cells from the selection.

An example ... Given that:
a1 = tom
b6 = roy
both a1 and b6 are selected and b6 was selected 2nd

Then the following puts tom in var1 and roy in var2 and writes the contents of var1 to the active cell which is the second cell of the selection.
  var1 = Application.Selection
  var2 = ActiveCell
  ActiveCell.Value = var1

What I can't figure out now is how to tell it to change the active cell back to the other cell in the selection so that I can write the contents of var2 to it.

Any thoughts on this?

Thanks again  
ptroiano

Answer
ptroiano


There is no reason to select the cells or change the activecell

v = Range("A1").Value
Range("A1").Value = Range("B6").Value
Range("B6").Value = v

so I never select either cell

If you want the user to select two cells and then run the macro to switch the contents

Sub AAA()
Dim r1 As Range, r2 As Range, cell As Range, v As Variant
If Selection.Cells.Count <> 2 Then Exit Sub
Set r1 = ActiveCell
For Each cell In Selection
 If cell.Address <> r1.Address Then
    Set r2 = cell
    Exit For
 End If
Next
' Now swap the values
v = r1.Value
r1.Value = r2.Value
r2.Value = v

End Sub

that would pay attention to what is the current active cell, but it really doesn't make any difference which one is the activecell if your are going to swap the values

Sub AABB()
If Selection.Cells.Count <> 2 Then Exit Sub
If Selection.Areas.Count = 2 Then
 v = Selection.Areas(1)
 Selection.Areas(1).Value = Selection.Areas(2).Value
 Selection.Areas(2).Value = v
ElseIf Selection.Areas.Count = 1 Then
 v = Selection(1).Value
 Selection(1).Value = Selection(2).Value
 Selection(2).Value = v
End If

End Sub

the last two macros have been tested and worked fine for swapping values in two cells whether they are contiguous or not contiguous.  

--
Regards,
Tom Ogilvy