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

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

You need to set the bounds of the array, so in the first
procedu
Redim asdf(0 To 5) As Integer


RBS


"brzak" wrote in message
...
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

Option Explicit
Sub Test()

Dim myArr() As Variant
Dim iCtr As Long
Dim myRng As Range
Dim myCell As Range

Set myRng = Sheet1.Range("mynamedrange")
ReDim myArr(1 To myRng.Cells.Count)

iCtr = 0
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'skip it
Else
iCtr = iCtr + 1
myArr(iCtr) = myCell.Value
End If
Next myCell

If iCtr = 0 Then
'nothing found!
Else
ReDim Preserve myArr(1 To iCtr)
Worksheets("Sheet2").Cells(5, 5).Value = Join(myArr, ", ") & "."
End If

End Sub



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

--

Dave Peterson

Thanks Dave (and to you RB but i got there just before you :)

A much more more elegant representation of what my attempt, thanks.

I try to avoid putting to much into memory, so if i am able to read
only the cell values, i assume it would eb wuicker than to hold the
entire array in memory and then assign to an array - or is the read
from the worksheet slower?

It may not be noticeable on smaller workbooks, but if you were to take
it to an extreme where tehre were many large arrays, all of which
required manipulation and storage in an array, how would you say it is
advisable to store them as:

-simply as a range, which will contain all the properties of that
range (i would guess not as too much unused info)
-an array read from a range variable (again - range is in memory?)
-populate array directly from the worksheet (possible drawback
would be a lot of reading from teh worksheet which may be slow for all
i know)

Cheers

Brz



On Jul 24, 12:03*am, Dave Peterson wrote:
Option Explicit
Sub Test()

* * Dim myArr() As Variant
* * Dim iCtr As Long
* * Dim myRng As Range
* * Dim myCell As Range

* * Set myRng = Sheet1.Range("mynamedrange")
* * ReDim myArr(1 To myRng.Cells.Count)

* * iCtr = 0
* * For Each myCell In myRng.Cells
* * * * If myCell.Value = "" Then
* * * * * * 'skip it
* * * * Else
* * * * * * iCtr = iCtr + 1
* * * * * * myArr(iCtr) = myCell.Value
* * * * End If
* * Next myCell

* * If iCtr = 0 Then
* * * * 'nothing found!
* * Else
* * * * ReDim Preserve myArr(1 To iCtr)
* * * * Worksheets("Sheet2").Cells(5, 5).Value = Join(myArr, ", ") & "."
* * End If

End Sub



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

--

Dave Peterson

Usually it'll be quicker to pick up all the values in one step:

This should work if the range is a single area:

dim myArr as Variant
dim rCtr as long
dim cCtr as long
myarr = Sheet1.Range("mynamedrange").value

for rctr = lbound(myarr,1) to ubound(myarr,1)
for cctr = lbound(myarr,2) to ubound(myarr,2)
msgbox myarr(rctr,cctr) & vblf & rctr & "-" & cctr
next cctr
next rctr

==========
If it's just one column, you still end up with a 2 dimensional array, (x rows by
1 column).

dim myArr as Variant
dim rCtr as long
myarr = Sheet1.Range("mynamedrange").value

for rctr = lbound(myarr,1) to ubound(myarr,1)
msgbox myarr(rctr,1) & vblf & rctr
next rctr

========
And notice that myArr is just an array of values. It doesn't include any of the
range properties.


brzak wrote:

Thanks Dave (and to you RB but i got there just before you :)

A much more more elegant representation of what my attempt, thanks.

I try to avoid putting to much into memory, so if i am able to read
only the cell values, i assume it would eb wuicker than to hold the
entire array in memory and then assign to an array - or is the read
from the worksheet slower?

It may not be noticeable on smaller workbooks, but if you were to take
it to an extreme where tehre were many large arrays, all of which
required manipulation and storage in an array, how would you say it is
advisable to store them as:

-simply as a range, which will contain all the properties of that
range (i would guess not as too much unused info)
-an array read from a range variable (again - range is in memory?)
-populate array directly from the worksheet (possible drawback
would be a lot of reading from teh worksheet which may be slow for all
i know)

Cheers

Brz

On Jul 24, 12:03 am, Dave Peterson wrote:
Option Explicit
Sub Test()

Dim myArr() As Variant
Dim iCtr As Long
Dim myRng As Range
Dim myCell As Range

Set myRng = Sheet1.Range("mynamedrange")
ReDim myArr(1 To myRng.Cells.Count)

iCtr = 0
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'skip it
Else
iCtr = iCtr + 1
myArr(iCtr) = myCell.Value
End If
Next myCell

If iCtr = 0 Then
'nothing found!
Else
ReDim Preserve myArr(1 To iCtr)
Worksheets("Sheet2").Cells(5, 5).Value = Join(myArr, ", ") & "."
End If

End Sub



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

--

Dave Peterson

--

Dave Peterson