Stupid array question
doh I found out what the problem was, an array needs to have lower and
upper boudns defined (or just upper)
I was after something like this:
Sub Tes12t()
Dim asdf() As String, i As Integer, n As Integer
ReDim asdf(1 To 5)
n = 0
For i = 1 To 5
If Len(Cells(i, 1).Value) 0 Then
n = n + 1
asdf(n) = Cells(i, 1).Value
End If
Next i
ReDim Preserve asdf(1 To n)
Cells(2, 3).Value = Join(asdf, ", ") & "."
End Sub
where a1="apple", a3="banana"
at the end c2="apple, banana."
On Jul 23, 11:23*pm, brzak wrote:
Can someone please point out what is wrong with the following:
Sub Test()
* * Dim asdf() As Integer, i As Integer
* * For i = 0 To 5
* * * * asdf(i) = i
* * Next i
End Sub
doesn't work even if Option Base 0 were to be included, though
obviously isn't relevant to my problem
Whereas it likes:
Sub Test_()
* * Dim asdf(0 To 5) As Integer, i As Integer
* * For i = 0 To 5
* * * * asdf(i) = i
* * Next i
End Sub
what i'd actually like to do is loop through a named range, and assign
to the array only cells that meet a certain criteria, and join those
values to output to another cell i.e.
Sub Test()
* * 'Two worksheets on workbook, as in default
* * 'Sheet1 contains has a named range, "MyNamedRange", 3 by 1 in size
* * 'cell A1 has text "apple"
* * 'cell A2 is blank
* * 'cell A3 has text "banana"
* * Dim asdf() As String, i As Integer, arr_size As Integer
* * arr_size = 0
* * For i = 0 To 2 * *'the named is range is 5x1, say a1:a5
* * * * 'If
Len(Workbooks("Book1").Sheet1.Range("mynamedrange" ).Cells(i, 1).Value) 1 Then
* * * * If Len(Sheet1.Range("mynamedrange").Cells(i + 1).Value) 0
Then
* * * * * * asdf(arr_size) = Sheet1.Range("MyNamedRange").Cells(i +
1).Value
* * * * * * arr_size = arr_size + 1
* * * * End If
* * Next i
* * ' would like array to be ("apple", "banana", "") at this point
* * ' and at this point arr_size = 2
* * ReDim Preserve asdf(0 To arr_size - 1) 'resize
* * Worksheets("Sheet2").Cells(5, 5).Value = Join(asdf, ", ") & "."
End Sub
note this sub doesn't actually work, to run it, replace:
* * "Dim asdf() As String" with "Dim asdf(0 to 2) As String"
and comment the Redim Preserve line as array has been dimensioned.
This is really bugging me, and i know it's something stupid but just
can't see it!
Thanks in advance
|