Assign selected cells to variable

Question

I have a vba assignment where I want to assign cell values to a variable. I used selection.SpecialCells to find select the relevant cells. Thus I have selected a column containing 11 cells, but with spaces in between. How can I assign this to a variable?

Worksheets("Sheet1").Range(Cells(4, 3), Cells(1000, 3)).Select
Selection.SpecialCells(xlCellTypeConstants, 2).Select

Set IsinArray = Selection

What sort of value is being stored? And is it a single value you want stored in the variable at any given point, or do you want the variable to contain multiple values at once? some context of how this variable would be used would be appreciated. — Spencer Barnes, Aug 10 '21 at 09:55
You mean you want to convert set of numbers into string: "11, 5, 22, etc."? — Maciej Los, Aug 10 '21 at 09:57
The cells contain ISIN codes (two letters followed by numbers). fx US0010246396 — Frank_O, Aug 10 '21 at 10:21
I need to match the ISIN codes with the codes in another excel file and then copy values in another column where the ISIN code matches — Frank_O, Aug 10 '21 at 10:22

score 1 · Accepted Answer · answered Aug 10 '21 at 10:25

(1) Remove the Set - it is used only to assign objects, not values.
(2) Read (and understand) How to avoid using Select in Excel VBA.
(3) Qualify the Cells - you need to tell VBA from which worksheet you want the cells.
(4) The second parameter in SpecialCells specifies what kind of data you want to read. 2 (xlTextValues) says only strings, so numbers are filters out. If you want to read all cells that are not empty, simply omit this parameter.
(5) If your Range contains more than one cell, the values are stored in a 2-dimensional array. If your Range contains only one value, the result is a single value, not an array. If SpecialCells doesn't find anything (eg, all cells are empty), it will throw a runtime error.
(6) Use Option Explicit and declare all variables. As the result of the SpecialCells can be of different type, use a Variant.
(7) Split complex statements helps to find errors.

Dim r as Range
With Worksheets("Sheet1")
    Set r = .Range(.Cells(4, 3), .Cells(1000, 3))
    ' (Synonym to Set r = .Range("C4:C1000")
End With

Dim myVals
On Error Resume Next  ' Handle the possible error that no value was found
myVals = r.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If IsEmpty(myVals) Then
    Debug.Print "No values found."
ElseIf Not IsArray(myVals) Then
    Debug.Print "Only one value found."
Else
    Debug.Print UBound(myVals, 1) & " values found."
End If

Assign selected cells to variable

1 Answers1