It's really going to depend on what you're doing. Initially, it seems that you're wasting effort.
You're: 1) defining a range, 2) copying the range values into an array, 3) processing the array. why not simply process the range values? at the same time, it may require more cpu cycles to compare a range().value to some value than it does to compare an array element sbu(x) = ?? . so, looping through tens of thousands of records MIGHT be CPU - time consuming. I may play with it and see what I can determine. thanks for the puzzle!!! Paul ________________________________ From: molinari <dh0...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Thu, June 3, 2010 12:07:32 PM Subject: Re: $$Excel-Macros$$ reading excel worksheet into an array? Thanks. All that I'm using the array for is to go through a loop, and for each value in the array, to do particular things to particular tabs (which are determined solely by the array values). I found out what was causing me problems: my xlLastCol function was not working, and returning a last column of "0". Hence, the array was running into out of range problems. After fixing the xlLastCol function, what I did was this: Sheets(SBUarraySheet).Select y = xlLastRow ' y = numbers of rows x = xlLastCol ' x = number of cols Dim SBU() As Variant SBU = Range(Cells(2, 2), Cells(y, x)).Value This works, but sets the array base to 1 (I put Option Base 1 at the beginning of the module just to remind myself of that). It seems really simple, easy to follow, and nice. I tested it with my macro, and it works perfeclty (tested the values using Msg, then ran the macro, and it worked just the same as the array specified manually in the VBA code). http://www.codeforexcelandoutlook.com/blog/2008/09/reading-worksheet-values-into-arrays/ Any reason not to use that method? On Jun 3, 10:17 am, Paul Schreiner <schreiner_p...@att.net> wrote: > in order to give you a recommendation that has any value, > I need to know what you plan to do with the array. > > Your example indicates that you want to create an array > and populate it with the contents? of a range consisting of all cells with > values. > Of course, when more data is added, the array size changes... > > ok, that's possible, but not the way you want it to. > > the way you change an array size is using ReDim: > '------------------------------------------------------------------------- > Option Explicit > Dim ClaimProvider() > Sub ResizeArray() > Dim rCnt, cCnt > Dim R, C > ReDim ClaimProvider(1, 1) > Sheets("Data").Select > rCnt = ActiveCell.SpecialCells(xlLastCell).Row > cCnt = ActiveCell.SpecialCells(xlLastCell).Column > > ReDim ClaimProvider(rCnt, cCnt) > For R = 2 To rCnt > If (R Mod 100 = 0) Then Application.StatusBar = "Loaded " & R & " of > " & rCnt > For C = 1 To cCnt > ClaimProvider(R, C) = Sheets("Data").Cells(R, C).Value > Next C > Next R > Application.StatusBar = False > MsgBox R & " rows loaded" > End Sub > '------------------------------------------------------------------------- > Now, if what you're really trying to do is to define a dynamic RANGE, then > use: > '------------------------------------------------------------------------- > Option Explicit > Sub DefineRange() > Dim rCnt, cCnt, ClaimProviderRange As Range > Sheets("Data").Select > rCnt = ActiveCell.SpecialCells(xlLastCell).Row > cCnt = ActiveCell.SpecialCells(xlLastCell).Column > > Set ClaimProviderRange = Sheets("Data").Range(Cells(1, 1), Cells(rCnt, > cCnt)) > MsgBox ClaimProviderRange.Rows.Count > End Sub > '------------------------------------------------------------------------- > In this case, the variable ClaimProviderRange works the same as any Range > object. > > ClaimProviderRange(1,5).value gives you the value of cell E1 > > what you're DOING with the array or range will determine the best method for > defining it. > > hope this helps. > > Paul > > > > ________________________________ > From: molinari <dh0...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Wed, June 2, 2010 3:59:56 PM > Subject: $$Excel-Macros$$ reading excel worksheet into an array? > > Hi all, I want to create an array in VBA such that typical end-users > can modify what goes into the array without knowing code (or looking > at it). Hence, I want the array to read the contents of a worksheet, > excluding the first column and first row (which are labels). > > i.e., I want to read the following into the array, but not including > the first row or the first column > # ; Tab (0) ; Code (1); Chart (2) > 0 ; Tab1 ; A ; "A Chart" > 1 ; Tab2 ; P ; "P Chart" > > and the # of rows and columns may increase, as we get more > categories. > > How can I accomplish this? > > I've tried the following, but run into errors: > > Sheets("My Sheet").Select > Dim y As Variant > Dim x As Variant > y = xlLastRow > x = xlLastCol > Dim ClaimProvider() As Variant > ClaimProvider = Range(Cells(2, 2), Cells(y, x)) > > This isn't working, and gives me errors. (xlLastRow and xlLastCol are > custom functions that find the last row and last column on a worksheet) > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links > :http://twitter.com/exceldailytip > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 7000 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group athttp://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
