CODEWITHSUNDEEP
arraysexcelvbavariablesrange
Frank_O
Frank_O

Reputation: 37

Assign selected cells to variable

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

Upvotes: 0

Views: 1380

Answers (1)

FunThomas
FunThomas

Reputation: 29286

(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

Upvotes: 1

Related Questions