How do you isolate each cell in a selected range?

rizzo93

Active Member
Joined
Jan 22, 2015
Messages
250
I want a user to ctrl-click cells on one worksheet, and from that selection I will find corresponding information on another worksheet.

I know how to set the range to what has been selected and I know how to get the count of what is selected. But what I don't know is how to go through those selected items individually so that I can get the information I ultimately want to get.

So I found this code. However, as it goes through the loop, it simply goes to the cell directly below it and not the next cell in the selected range.

How do I identify the specific cells within the selected range?

Btw, the code between the IF and Else is not what I want; it's just something that was included in the original code.

Code:
Sub DistroList()
Dim Distro As String
Dim pmSelect As Integer
Dim i As Integer

areaCount = Selection.Areas.Count
If areaCount <= 1 Then
 MsgBox "The selection contains " & _
 Selection.Rows.Count & " rows."
Else
 i = 1
 For Each a In Selection.Areas
 MsgBox Cells(i, 4).Value
 i = i + 1
 Next a
End If
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this:

Code:
Dim r As Range

For Each r In Selection
Debug.Print r.Address
Next
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Depending what cells are selected, maybe
Code:
   Dim Rng As Range, Cl As Range
   For Each Rng In Selection.Areas
      For Each Cl In Rng
         MsgBox Cl.Address
      Next Cl
   Next Rng
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,325
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top