whats wrong with my array?

  • Thread starter Thread starter joshashcraft
  • Start date Start date
J

joshashcraft

i have these two arrays both in one of my subs in my
worksheet, why won't they load the values?
Basically, i want it to cycle through the range below for
each, pull the value, and stick them in each of the arrays.
any help would be much appreciated!
thanks guys,
Josh


Dim acell As Variant
Dim mynames(0 To 49)
Dim ncount As Integer
For Each acell In Range("a3:a50")
For ncount = 0 To 49
mynames(ncount) = acell.Value
Next ncount
Next


Dim bcell As Variant
Dim mystatus(0 To 49)
Dim scount As Integer
For Each bcell In Range("b3:b50")
For scount = 0 To 49
mystatus(scount) = bcell.Value
Next scount
Next
 
You are assigning all the values of each cell to each element of the array,
so you end up with the value of the last cell in each element.



Sub showArray()
Dim acell As Variant
Dim mynames(0 To 47)
Dim ncount As Integer
ncount = 0
For Each acell In Range("a3:a50")
mynames(ncount) = acell.Value
ncount = ncount + 1
Next

For i = 0 To UBound(mynames)
If i Mod 10 = 0 And i <> 0 Then
sStr = sStr & mynames(i) & vbNewLine
Else
sStr = sStr & mynames(i) & ","
End If
Next
sStr = Left(sStr, Len(sStr) - 1)
MsgBox sStr
End Sub


or

Dim aCell as Range
Dim myNames as Variant
MyNames = Range("A3:A50").Value

Mynames no holds a two dimensional array

MyNames(1 to 48, 1 to 1

Sub showArray()
Dim aCell As Range
Dim myNames As Variant
myNames = Range("A3:A50").Value

For i = 1 To UBound(myNames)
If i Mod 10 = 0 Then
sStr = sStr & myNames(i, 1) & vbNewLine
Else
sStr = sStr & myNames(i, 1) & ","
End If
Next
sStr = Left(sStr, Len(sStr) - 1)
MsgBox sStr
End Sub
 
joshashcraft said:
i have these two arrays both in one of my subs in my
worksheet, why won't they load the values? [snip]
Click to expand...

The first (minor) problem is that ncount should go from 0 to 47 because
there are only 48 values in A3:A50.

The major problem is that you load each element of mynames with the
value from a3, then replace each with the value from a4, etc. so that
you end up with mynames having all its elements equal to the value from
A50. Use

Dim acell As Variant
Dim mynames(0 To 47)
Dim ncount As Integer
For Each acell In Range("a3:a50")
mynames(ncount) = acell.Value
ncount = ncount + 1
Next acell

Alan Beban
 
Back
Top