I'd rather go with
>>>for i = 0 to .rows.count-1
>>>>for j = 0 to .columns.count-1
>>>>>MyArray(i, j) = ...
Regards,
Sam Mathai Chacko
On Tue, Jan 31, 2012 at 12:23 AM, bpascal123 <bpascal...@gmail.com> wrote:
> thanks
> ...the base 1 array is a mistake, i usually work with base 0, so it
> would then be :
>
> >>>redim MyArray(.Rows.count-1, .Colymns.Count-1)
> then...
>
> >>>for i = 1 to .rows.count
> >>>>for j = 1 to .columns.count
> >>>>>MyArray(i-1, j-1) = ...
>
> I think it's better like that
>
> On Jan 30, 4:59 pm, Sam Mathai Chacko <samde...@gmail.com> wrote:
> > First of all, your first method has very less likelihood of working. In
> > other words, you cannot pass a range of values once you have defined the
> > dimensions of the array.
> >
> > If you had defined the variable as a variant, it would have worked.
> >
> > So basically,
> >
> > Dim MyArray as Variant
> >
> > With Sheet1.Range("A1:C5")
> > MyArray = Sheet1.Range("A1:C5").Value2
> > End With
> >
> > would have worked.
> >
> > Secondly,
> >
> > You cannot use a i -1 within your array if you already initialize it
> with 1
> > to .Rows.Count, or 1 to .Columns.COunt
> >
> > By default the array base is 0, but since you started with 0, your loop
> > argument should also start with 1, and not 0
> >
> > So
> >
> > With Sheet1.Range("A1:C5")
> > ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
> > For i = 1 To .Rows.Count
> > For j = 1 To .Columns.Count
> > MyArray(i, j) = .Cells(i, j).Value
> > Next j
> > Next i
> > End With
> >
> > End Sub
> >
> > Finally, your question about Clng. Using the conversion wouldn't really
> > make a difference if the values were either some numeric value, or blank.
> > Also, there's no point converting it to a long value if the value is a
> text
> > entry. In that case it would anyway throw an error if you use Clng.
> >
> > Regards,
> > Sam Mathai Chacko
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > On Mon, Jan 30, 2012 at 7:00 PM, bpascal123 <bpascal...@gmail.com>
> wrote:
> > > Hi,
> >
> > > Is there a difference between these 2 methods of assignation :
> >
> > > Range method :
> >
> > > Dim MyArray() as long, rCnt As Long, cCnt As Integer
> >
> > > With Sheet1.Range("A1:C5")
> > > ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
> > > MyArray = Sheet1.Range("A1:C5")
> > > End With
> >
> > > Loop method :
> >
> > > with Sheet1.Range("A1:C5")
> > > redim MyArray(1 to .Rows.Count, 1 To .Columns.Count)
> > > for i = 1 to .Rows.Count
> > > for j = 1 to .Columns.Count
> > > MyArray(i-1,j-1) = .Cells(i,j) ''' I'm not even sure if
> > > it's .Cells(i,j) is the right syntax...the question is not really
> > > about the syntax
> > > next j
> > > next i
> >
> > > Are the 2 methods equivalent?
> >
> > > If the range has numbers stored as text along number stored as number,
> > > is everything going to be cast or converted as Long in MyArray. To me
> > > it would be an implicit casting since it relies only on the fact that
> > > MyArray is declared as Long...
> >
> > > Wouldn't it be wiser to use Clong or Cint in the allocation line?
> >
> > > Pascal
> >
> > > --
> > > FORUM RULES (986+ members already BANNED for violation)
> >
> > > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > > will not get quick attention or may not be answered.
> >
> > > 2) Don't post a question in the thread of another member.
> >
> > > 3) Don't post questions regarding breaking or bypassing any security
> > > measure.
> >
> > > 4) Acknowledge the responses you receive, good or bad.
> >
> > > 5) Cross-promotion of, or links to, forums competitive to this forum
> in
> > > signatures are prohibited.
> >
> > > NOTE : Don't ever post personal or confidential data in a workbook.
> Forum
> > > owners and members are not responsible for any loss.
> >
> > >
> ---------------------------------------------------------------------------
> ---------------------------
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > --
> > Sam Mathai Chacko
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
Sam Mathai Chacko
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
