Looping through 8204 variable type (array of variants)

Question

I'm having some trouble looping through a variant array (8204 variable type). I'm am seeking input via an input box (type 8) and would like the user to be able to ctrl+ multiple disjointed ranges and cells. The problem that I am running into is that when I try and loop through those selected ranges it only picks up the first one.

Here's a working example of the issue:

Sub myarray()
    MyAnswer = Application.InputBox("Pick a description cell(s) in spreadsheet for the link" _
        & vbNewLine & "(Hold Ctrl to select multiple cells)", Type:=8)
    ' if its type 8204
    If VarType(MyAnswer) = 8204 Then
        MsgBox "Length of array:  " & UBound(MyAnswer)
        ' loop through each element in the array
        For Each vvalue In MyAnswer
            MsgBox vvalue
        Next
    End If
End Sub

in the prompt type the following or select some ranges using ctrl+:

$A$12:$A$13,$B$4:$C$4,$D$4

for some reason I can only pick up the first range $A$12:$A$13 when I would like to loop through all elements in all the ranges/cells.

Any help is much appreciated. Thanks!

Where is vvalue dimensioned? Perhaps it is a keyword and I am just unfamiliar... — Tanner
– Tanner, Commented Jun 26, 2014 at 19:57
if you set a break point on your If statement, you'll see that MyAnswer only has the values from the first range selected. You'll have to come up with another way (a user form perhaps?) to accomplish what you're trying to do. — sous2817
– sous2817, Commented Jun 26, 2014 at 20:07

Siphor · Accepted Answer · 2014-06-26 20:18:04Z

2

Application.InputBox returns a range object, because you are not using set it uses the default property .value, which returns only the values of the first area.

Sub myarray()
    Dim MyAnswer as Range
    Set MyAnswer = Application.InputBox("Pick a description cell(s) in spreadsheet for the link" _
        & vbNewLine & "(Hold Ctrl to select multiple cells)", Type:=8)
    ' if its type 8204
    If not MyAnswer is nothing Then
        dim cell as Range
        ' loop through each cell in the range
        For Each cell In MyAnswer
            MsgBox cell.value
        Next
    End If
End Sub

answered Jun 26, 2014 at 20:18

Siphor

2,5542 gold badges15 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Radical Edward Over a year ago

was just about to answer my own question after figuring this out. The only thing I had different was On Error GoTo ErrorHandler before set MyAnswer because if cancel is pushed it throws an error. Thanks!

Collectives™ on Stack Overflow

Looping through 8204 variable type (array of variants)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related