How do I activate the next cell in a selected range?

[Guest]

I am trying to memorize the values and addresses in a selected range. Does
anyone know the syntax for going to the next selected cell? I thought
something like selection.next.activate would work, but that just goes to the
next cell regardless if it is selected or not.

Thank you !!

 

[Guest]

selection.offset(1,0).activate if you want to stay in the same column,
Selection.offset(1,1) if you want to move also one column to your right.
use a variable to make the number dynamic.
Selection.offset(x,y)

 

[Guest]

Thank you for your response !! that one worked well for me on the first run
through my loop; however, it seems that vba is defining "selection" as the
top cell in the selection - so it loops fine the first time, but then when it
comes back to loop again it still sees the top selected cell as the one to
offset from and it re-activates the same second cell.

Is there a syntax you are aware of that would act just like the "Enter"
button acts when you have a selected range while in Excel(not running code)?
So I could circle through a loop until I have read the values of all the
cells in the selected range? Your previous response probably works well, but
my implementation wasn't correct . . .

Currently my code looks like this . . .

y = 0
CAddress = ActiveCell.Address

Selection.Offset(1, 0).Activate
Do Until ActiveCell.Address = CAddress
y = y + 1
ReDim Preserve CNum(y) As Long
ReDim Preserve CAdd(y) As String
CNum(y) = ActiveCell.Value
CAdd(y) = ActiveCell.Address
Selection.Offset(1, 0).Activate
Loop

Thanks again for your help !!

 

[Guest]

Try this:
Sub test()
Dim y As Variant
Dim cnum() As Integer
Dim Cadd() As String
y = 0
CAddress = ActiveCell.Address

Selection.Offset(1, 0).Activate
Do Until ActiveCell.Address = CAddress
y = y + 1
ReDim Preserve cnum(y) As Integer
ReDim Preserve Cadd(y) As String
cnum(y) = ActiveCell.Value
Cadd(y) = ActiveCell.Address
Selection.Offset(1, 0).Activate
Loop

End Sub







--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Guest]

This will correct the selection of the active cell, however, it will never
match the First cell (CAddress) because is going down and never coming back.
In other words you have CAddress = $A$1, and the with your loop you start
comparing it to the active cell address which is now $A$2, it will keep
selecting the following cell until it reaches $A$65536, but will never match
$A$1.
You need to define a better stopping point.

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Guest]

Thank you again for your response - that still didn't work for me. It works
the first time through the loop if the activecell in the selection is the top
cell and then enters the loop to never exit the loop.

Maybe my best approach might be to check all the cells on the sheet to see
if they are selected? That seems pretty inefficient, but maybe will work? I
have to build something into my code anyway to check other columns for
selected cells anyway because all my numbers will not necessarily be in one
column.

Thanks again for your help -

Jim

 

[Guest]

Hi Michael --

This works well if I don't have a selected range - the problem is that I may
have cell A1 selected, A14, A27, and B14, C34, etc - so when those are
selected I want to be able to loop through all the cells memorizing their
address and values - looping through them as if I had them already selected
in Excel and then hitting the enter button to go to the next selected cell.

I can see what you are saying though in your response -- I wasn't clear
enough on the idea that I have several selected cells that I want to look at
-- so I don't want to see any cells other than those that are in a selected
range (that could have some breaks in the range).

Thanks for your help --

Jim

 

[Ferris]

Hi Michael --

This works well if I don't have a selected range - the problem is that I may
have cell A1 selected, A14, A27, and B14, C34, etc - so when those are
selected I want to be able to loop through all the cells memorizing their
address and values - looping through them as if I had them already selected
in Excel and then hitting the enter button to go to the next selected cell.

I can see what you are saying though in your response -- I wasn't clear
enough on the idea that I have several selected cells that I want to look at
-- so I don't want to see any cells other than those that are in a selected
range (that could have some breaks in the range).

Thanks for your help --

Jim

You could use the following, it will loop through all of the cells in
your selection.

Sub test()
Dim rng As Range

Set rng = Selection

For Each c In rng
Debug.Print c.Address
Debug.Print c.Value
'or do something else
Next c

End Sub

 

[Guest]

Thank You !!! that works perfectly !! Thanks to Michael as well for helping
me to clarify my question.