Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
whats wrong with my array?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
whats wrong with my array?
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 -- Regards, Tom Ogilvy "joshashcraft" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
whats wrong with my array?
joshashcraft wrote:
i have these two arrays both in one of my subs in my worksheet, why won't they load the values? [snip] 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Whats wrong with this array formula | Excel Worksheet Functions | |||
Whats wrong with this function? | Excel Worksheet Functions | |||
whats wrong with this? | Excel Discussion (Misc queries) | |||
Whats wrong with this? | Excel Discussion (Misc queries) | |||
Whats Wrong with this?? | Excel Worksheet Functions |