Dave
Wed Jul 23 19:28:27 CDT 2008
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 <peter...@verizonXSPAM.net> 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