VBA: Create Array Using Selection

[zero269]

Hello,

I'm having some trouble figuring out how to create an array using values from a selection of cells.

One thing to note, is that I'm not storing the values for the selected cells themselves. Depending on who's selecting the cells, it could be any column, so I'm telling Excel to store the values in column A instead, based on the row number for the selected cell... using…

myArray(i) = Range("A" & cell.Row)

I'm getting a Type Mismatch error for the Array portion, thus preventing me from even storing a single value at this point.
I tried storing the Quiz number into a variable (v2) thinking Long values were the issue, but that resulted in the same error.

'Create Array from Selection
Sub WIP_Selection_Array()

  'Select visible cells only if selection is > 1
  If Selection.Cells.Count > 1 Then
    Selection.SpecialCells(xlCellTypeVisible).Select
  Else
  End If
  
  'Declarations
  Dim myArray As Variant
  Dim cell As Range
  Dim i As Long: i = 1
  Dim Quiz As Long
  
  'LOOP Selection
  For Each cell In Selection
  
    'v1
    myArray(i) = Range("A" & cell.Row)

    'v2
'    Quiz = Range("A" & cell.Row)
'    myArray(i) = Quiz

    i = i + 1 'index counter
  
  Next cell

End Sub

For example, If I select the four cells in column Q that show "on hold", then my array would be populated with the four associated Quiz numbers in column A.

VBA Testing.xlsm
	A	B		D					I	J	K	L			O	P	Q
1	Quiz	Title		Author					F/NF	AR Points	Book Level	Word Count			Due Date	Pickup Date	Book Status
2	143836	20,000 Leagues Under the Sea		Hutchinson, Emily					F	2.0	4.2	12,225				30-Sep	on hold
3	154	A Bear Called Paddington		Bond, Michael					F	4.0	4.7	24,560
4	518852	A Christmas Carol		Hutchison, Patricia					F	1.0	2.8	6,147				30-Sep	on hold
5	41535	A Christmas Carol		Hutchinson, Emily					F	2.0	4.1	12,162				30-Sep	on hold
6	41536	A Tale of Two Cities		Lorimer, Janet					F	2.0	3.8	12,152				30-Sep	on hold
Books

Any help would be greatly appreciated…

 

[jolivanes]

Sub Try_So_Maybe()
Dim ws As Worksheet
Dim colArr
Dim c As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")    '<---- Change required
    For Each c In Selection
        colArr = colArr & "|" & ws.Cells(c.Row, 1).Value
    Next c
colArr = Split(Mid(colArr, 2), "|")
ws.Cells(ws.Rows.Count, 20).End(xlUp).Offset(1).Resize(UBound(colArr) + 1).Value = Application.Transpose(colArr)    '<---- Change required
End Sub

 

[Peter_SSs]

For example, If I select the four cells in column Q that show "on hold", then my array would be populated with the four associated Quiz numbers in column A.

Try this then

Sub Fill_Array()
  Dim myArray As Variant
  
  myArray = Evaluate("let(v,textsplit(textjoin(""|"",," & Intersect(Selection.EntireRow, Columns("A")).Address & "),""|""),iferror(--v,v))")
End Sub

Here are my results for the sample data and selection scenario you gave.