Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
I know I can quickly map a 2d range to an array with code such as
Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Dave,
If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Hi Bob,
I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
looping in an array is extremely fast.
Looping would be the only way to do what you want with the array. If you want to use the worksheet, you could insert a dummy column and do the concatenation in the dummy column, then pick up the results and delete the column. -- Regards, Tom Ogilvy Dave wrote in message u... Hi Bob, I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Please fix/set your computer clock. You are way ahead of everyone else.
-- Regards, Tom Ogilvy Dave wrote in message u... Hi Bob, I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
test
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
"Tom Ogilvy" wrote...
Please fix/set your computer clock. You are way ahead of everyone else. In my other response, the one that included only the word test in the body, I had set my system clock to a different time of day. However, the date/time stamp in the posting showed the correct date and time when I posted it. I believe the NNTP server through which one posts (even indirectly through browser interfaces) sets the message's date/time stamp, and a user's system clock's settings are irrelevant. If so, I'd bet the OP would have a hard time setting his ISP's NNTP server's clock. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
test <g
"Harlan Grove" wrote in message ... "Tom Ogilvy" wrote... Please fix/set your computer clock. You are way ahead of everyone else. In my other response, the one that included only the word test in the body, I had set my system clock to a different time of day. However, the date/time stamp in the posting showed the correct date and time when I posted it. I believe the NNTP server through which one posts (even indirectly through browser interfaces) sets the message's date/time stamp, and a user's system clock's settings are irrelevant. If so, I'd bet the OP would have a hard time setting his ISP's NNTP server's clock. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
I don't believe you are correct, Harlan ... please see the date on my "test"
post just made! Perhaps this property is server-dependent. -- Vasant "Harlan Grove" wrote in message ... "Tom Ogilvy" wrote... Please fix/set your computer clock. You are way ahead of everyone else. In my other response, the one that included only the word test in the body, I had set my system clock to a different time of day. However, the date/time stamp in the posting showed the correct date and time when I posted it. I believe the NNTP server through which one posts (even indirectly through browser interfaces) sets the message's date/time stamp, and a user's system clock's settings are irrelevant. If so, I'd bet the OP would have a hard time setting his ISP's NNTP server's clock. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Hi Tom,
I don't know what would have happened, the time does look late but I haven't changed my clock and I thought that I did post this latish Sunday evening I'm posting from Australia and we do have our clocks a little earlier for daylight saving but 17 hours in front of you does seem a little extreme. I'm posting this on Monday Dec 15 at 19.20. Cheers Dave "Tom Ogilvy" wrote in message ... Please fix/set your computer clock. You are way ahead of everyone else. -- Regards, Tom Ogilvy Dave wrote in message u... Hi Bob, I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Dave, check the second tab of the Date/Time dialog to make sure you are set
for the correct time zone. This is the more prevalent problem as most computer clocks seem to be factory-set to the US West Coast time zone by default. -- Vasant "Dave" wrote in message u... Hi Tom, I don't know what would have happened, the time does look late but I haven't changed my clock and I thought that I did post this latish Sunday evening I'm posting from Australia and we do have our clocks a little earlier for daylight saving but 17 hours in front of you does seem a little extreme. I'm posting this on Monday Dec 15 at 19.20. Cheers Dave "Tom Ogilvy" wrote in message ... Please fix/set your computer clock. You are way ahead of everyone else. -- Regards, Tom Ogilvy Dave wrote in message u... Hi Bob, I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Hello Dave,
As Tom wrote, looping in an array is not so slow. I totally agree with Tom's opinion. But If UBound(array2D) < 5462 Then you can use Application.Transpose something like this. array1D = Application.Transpose(array2D) It returnes 1-D array -- Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ "Dave" wrote in message u... Hi Bob, I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coverting a VBA array from 2-D to 1-D
Read the entire range into a variant with one operation, then remap the
elements into another array any way you wish. -- http://www.standards.com/; See Howard Kaikow's web site. "Dave" wrote in message u... Hi Bob, I want to run a routine on the usedrange so there could be up to 256 values per element I thought or maybe more accurately hoped that there may be some sort of array conversion I could do - I was concerned that a FOR loop would take a long time on say 200 columns by 10000 rows Is a loop the only way? Thanks Dave "Bob Phillips" wrote in message ... Dave, If that is all you want and you don't want loops, why not just use Dim myarray(1) myarray(0) = [A1] & [B1] & [C1] & [D1] myarray(1) = [A2] & [B2] & [C2] & [D2] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" wrote in message u... I know I can quickly map a 2d range to an array with code such as Sub MyArr() Dim myarray myarray = [A1:D2] End Sub Is there a quick way to convert the array into a single dimension without using a FOR loop? I want to end up with an array of two elements, the first containing a concatenated string of A1 to D1 the second of A2 to D2. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coverting to minutes | Excel Discussion (Misc queries) | |||
coverting h:mm to decimal | Excel Discussion (Misc queries) | |||
Coverting seconds into hr:min:sec | Excel Discussion (Misc queries) | |||
Coverting time | Excel Worksheet Functions | |||
Coverting array data into single column | Excel Worksheet Functions |