0

I'm trying to obtain the text/value of two selected cells (not necessarily adjacent) in an Excel sheet.

How can I reference the cells in the selection via some sort of "indexing"?

xxx = selection.cells(1).value
yyyy = selection.cells(2).value

doesn't do it, since the second refers to the cell exactly below that is referenced by the first.

I have this code to iterate the selected cells, but I don't like it, as I cannot reference the cells by address or index. usr1 and usr2 are to have the names of the two "users" selected in the sheet.

Dim usr1, usr2 As String
Dim usrs(1) As String
Dim myCell As Range
Dim uIndex As Integer

Worksheets("Tests").Activate
uIndex = 0
For Each myCell In Selection
    usrs(uIndex) = myCell.Value
    uIndex = uIndex + 1
Next

usr1 = usrs(0)
usr2 = usrs(1)

How can I reference the different selected cells directly?

Community
  • 1
  • 1
Pablo Ortiz
  • 11
  • 3
  • Why can't you reference your cells with `myCell`? you can `myCell.Address` and there you get the address – Damian Oct 09 '19 at 17:04
  • 2
    Looks like you have to refer to `Areas` rather than `Cells`, if your selections consist of single-cell non-contiguous areas. But basing things on selected cells is not advisable. – SJR Oct 09 '19 at 17:05
  • Side note: `Dim usr1, usr2 As String` should be `Dim usr1 as String, usr2 As String` otherwise `usr1` will not be a string – cybernetic.nomad Oct 09 '19 at 17:07
  • Did you try the Cell() Object ? Or to ask the Celll object ? With the Cell Object you can act on the sheets and tables like on a array. – Thomas Ludewig Oct 09 '19 at 17:21
  • @ThomasLudewig there is no `Cell` object in the Excel object model. – Mathieu Guindon Oct 09 '19 at 17:25
  • 2
    Either try: `xxx = Selection.Areas(1).Value` or `xxx = Range(Split(Selection.Address, ",")(0)).Value`. But as @SJR said. Coding based on selections is non-advisable. – JvdV Oct 09 '19 at 17:36
  • One thing I don't understand is that `Selection.count` returns 2 which seems inconsistent. Any idea why @MathieuGuindon / @JvdV? – SJR Oct 09 '19 at 18:12
  • @SJR it's because of the disjointed selection areas. Everything works as expected if you use `Areas`; remember that `Cells` takes no parameters, it just returns a `Range`, and the arguments go against `Range.[_Default]`. So if you select `C16:C18` you can still get a cell out of `Selection.Cells(1, 2)`, even if that cell isn't in the selection. – Mathieu Guindon Oct 09 '19 at 18:21
  • @MathieuGuindon - I just thought that if it recognised that Selection held 2 cells (and not 1) it would be able to retrieve their values (in layman's terms). I'd have understood if the "disjointed selection areas" meant it would detect both cells. – SJR Oct 09 '19 at 18:29
  • Ok we do not have Sheet and Cell objects in excel.. You are a Excel hero i gues.. I just wonder why things like this work all the time. https://stackoverflow.com/questions/57730093/how-do-i-copy-a-worksheet-from-one-workbook-to-another/57732535#57732535 – Thomas Ludewig Oct 09 '19 at 18:33
  • Mathieu we can slice hairs about that.If he get it working with this kind of reference who cares. :) – Thomas Ludewig Oct 09 '19 at 20:00
  • Thank you All for your comprehensive and very valuable input!! Regarding the not advisable to base things on selected cells issue, other than the likelihood of non-desired cells being selected when the code is called, which requires additional logic to prevent, for which other reasons isn't it advisable? – Pablo Ortiz Oct 09 '19 at 22:12
  • As if that wasn't reason enough? An errant mouse click or key press could make your program do who knows what! That is an unacceptable risk. Speed also plays a big factor, you can work with many ranges but only one selection, and more. I can see it now... You're so happy you have this thing working it will save you so much time and then you think you'll take advantage of the opportunity and multitask while it's running. You open a second workbook and.... Oops. Now your macro is changing the wrong workbook. Hope it doesnt save it because undo wont work – ProfoundlyOblivious Oct 09 '19 at 22:33

0 Answers0