upobDaPlaya
asked on
What is the proper way to use a single array
If I have a single array is the below correct ? Do you use a redim in a single array..What is considered best practice for dimming a single array variable ?
Dim strNames () as String
Dim intR
LastRow = Sheets("Roster").Range("A" & Rows.Count).end(xlUp).row
ReDim strNames(LastRow)
For intR = 10 to LastRow
strNames(intR) = Range("B" & intR)
.......................... .......... ......
.......................... .......... ........
Dim strNames () as String
Dim intR
LastRow = Sheets("Roster").Range("A"
ReDim strNames(LastRow)
For intR = 10 to LastRow
strNames(intR) = Range("B" & intR)
..........................
..........................
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
In your example you get in your array the values of B10 through B&lastRow
That's what my code does
Regards
In your example you get in your array the values of B10 through B&lastRow
That's what my code does
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rgonzo1971, if i do not loop per your suggestion, then to grab the first value then it would just be varnames(b10) ?
Whhen you do this:
LastRow = Sheets("Roster").Range("A" & Rows.Count).end(xlUp).row
ReDim strNames(LastRow)
then LastRow contains the row reference of the last row in Column A. Say this value is 20 therefore by doing
Redim strNames(LastRow)
it will re dimension the Array strNames to 20
Hope clarifies.
gowflow
LastRow = Sheets("Roster").Range("A"
ReDim strNames(LastRow)
then LastRow contains the row reference of the last row in Column A. Say this value is 20 therefore by doing
Redim strNames(LastRow)
it will re dimension the Array strNames to 20
Hope clarifies.
gowflow
ASKER
But if I instead use the below, how do I retrieve the first value in B10 ? Do just do varnames(b10) = ...............
Dim varNames() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
varNames = Range("B10:B" & lastrow).Value
Dim varNames() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
varNames = Range("B10:B" & lastrow).Value
ok here it is:
I corrected your lastrow to refer to Col B as if you have nothing in Col A and all the data in Col B lastrow will show 1.
Then I have a loop to show you all the data that you affected in your array it will print in your immediate window
Now to answer your question as you are affecting 1 dimentional array to varNames then from
varNames(x,y)
the x part will always be the variable part
and y = 1
So to answer specifically your question the first value you need to get from your array in this specific case B10 will be the first item therefore will be
varNames(1,1)
Hope this answers your querry.
gowflow
I corrected your lastrow to refer to Col B as if you have nothing in Col A and all the data in Col B lastrow will show 1.
Then I have a loop to show you all the data that you affected in your array it will print in your immediate window
Sub test()
Dim varNames() As Variant
lastrow = Range("B" & Rows.Count).End(xlUp).Row
varNames = Range("B10:B" & lastrow).Value
For I = LBound(varNames) To UBound(varNames)
Debug.Print varNames(I, 1)
Next I
End Sub
Now to answer your question as you are affecting 1 dimentional array to varNames then from
varNames(x,y)
the x part will always be the variable part
and y = 1
So to answer specifically your question the first value you need to get from your array in this specific case B10 will be the first item therefore will be
varNames(1,1)
Hope this answers your querry.
gowflow
Further to above and to be more specific you can do array manipulations with Ranges this way. Now this is the reverse to affect a Range with data from an array. If we take the previous example after you load your array say you had data in from B10 to B20 in varNames then you can get the following:
'---> Will get as much as the array contains
'ex this will only get 1 item the first in your case B10 and put it in D1
Range("D1") = varNames
'ex this will get 3 items from the first to the third B10 to B12 and put in D1 to D3
Range("D1:D3") = varNames
'ex this will get all the items
'but if the array is less than 100 then the rest of the items will show as #N/A
Range("D1:D100") = varNames
gowflow
'---> Will get as much as the array contains
'ex this will only get 1 item the first in your case B10 and put it in D1
Range("D1") = varNames
'ex this will get 3 items from the first to the third B10 to B12 and put in D1 to D3
Range("D1:D3") = varNames
'ex this will get all the items
'but if the array is less than 100 then the rest of the items will show as #N/A
Range("D1:D100") = varNames
gowflow
ASKER
goflow..very helpful..thanks for he well explained mechanics of the array..final question..whiy do we need to declare the array as a varinat..
Dim varNames
or
Dim varNames as Variant is the same
you mean why not declare it
Dim varNames() as string ?
you could do that but then you cannot affect the Range to varNames
to affect it then you need to have it as variant.
gowflow
or
Dim varNames as Variant is the same
you mean why not declare it
Dim varNames() as string ?
you could do that but then you cannot affect the Range to varNames
to affect it then you need to have it as variant.
gowflow
ASKER
Thanks, great post..I hope I was fair with the points...I wish I could give both of you 500 :)
ASKER