Input/output values into an array

Question

EDIT: Updated question using some of the suggestions below. This produces weird output though.

Dim ProviderArray() As Variant

Sub GetProviderNumbers()
Dim InputRange As Range
Dim WorkRange As Range


Set InputRange = Range("ProviderList")

Set WorkRange = Application.Intersect(InputRange, ActiveSheet.UsedRange)

SizeOfArray = Application.WorksheetFunction.CountA(WorkRange)

ReDim ProviderArray(0 To SizeOfArray)

ProviderArray = WorkRange.Value

For r = 1 To UBound(ProviderArray, 1)
For C = 1 To UBound(ProviderArray, 2)
    Debug.Print r, C, ProviderArray(r, C)
Next C
Next r

End Sub

1 1 5555 2 1 4444654 3 1 654654 4 1 654654654 5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 1
15 1
16 1
17 1
18 1
19 1

Could someone explain why this output?

Tim Williams · Accepted Answer · 2013-09-06 17:57:54Z

2

You can only use the one-line approach if you put the range into a 2-D array: you only have a 1-D array.

You could do this:

Dim ProviderArray()
Set WorkRange = .Intersect(InputRange, ActiveSheet.UsedRange)

'This makes ProviderArray a 2-D array, dimension 1 = # rows,
'   dimension2 = #cols.  Both dimensions are 1-based.  
ProviderArray = WorkRange.value 

for r=1 to ubound(ProviderArray,1)
for c=1 to ubound(ProviderArray,2)
    debug.print r,c,ProviderArray(r,c)
next c
next r

edited Sep 6, 2013 at 17:57

answered Sep 6, 2013 at 17:37

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

5 Comments

tigeravatar Over a year ago

Shouldn't the for c=1 to ... line use ubound(ProviderArray,2) instead of ,1?

user1741874 Over a year ago

Trying using this but don't understand my output.

Tim Williams Over a year ago

It just prints each value in the array to the immediate window: row#, col#, value

user1741874 Over a year ago

I changed debug.print r,c,ProviderArray(r,c) to debug.print ProviderArray(r) and it tells me it's subscript out of range. Why is that? I'm trying to mimic the following (Assume C++ programming language) for(x = 0; x < arrayLen; x++){ cout << array[x]}

user1741874 Over a year ago

I'm going to go ahead and accept the answer it worked for the most part; that being said my excel document [that only has this macro and a database table] just got corrupted and doesn't do anything now. Oh well.

TheMacroGuru · Accepted Answer · 2013-09-06 17:08:36Z

1

Maybe something a bit simpler like:

Private Sub GetProviderNumbers()

    Dim InputRange() As Variant
    InputRange = Range("ProviderList")

    For Each i In InputRange
        Debug.Print i
    Next

End Sub

answered Sep 6, 2013 at 17:08

TheMacroGuru

1342 bronze badges

Comments

Gary's Student · Accepted Answer · 2013-09-06 17:33:55Z

0

This captures a two-dimensional range and stores the values in a global two-dimensional array:

Dim ProviderArray() As String
Sub MAIN()
Range("B2:C11").Name = "ProviderList"
Call GetProviderNumbers
End Sub
Sub GetProviderNumbers()
    ary = Range("Providerlist")
    ll = LBound(ary, 1)
    lm = LBound(ary, 2)
    ul = UBound(ary, 1)
    um = UBound(ary, 2)
    ReDim ProviderArray(ll To ul, lm To um)
    For i = ll To ul
        For j = lm To um
            ProviderArray(i, j) = ary(i, j)
        Next
    Next
End Sub

answered Sep 6, 2013 at 17:33

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Input/output values into an array

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related