Coverting a VBA array from 2-D to 1-D

[Vasant Nanavati]

test <g>

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.

 

[Dave Peterson]

test

(I left the time correct, but changed the timezone to Bombay.)

I posted at 6:08 PM CST.

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

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

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)

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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

 

[Tom Ogilvy]

Please fix/set your computer clock. You are way ahead of everyone else.

--
Regards,
Tom Ogilvy

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

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)

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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

 

[Harlan Grove]

test

 

[Harlan Grove]

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.

 

[Vasant Nanavati]

I don't believe you are correct, Harlan ... please see the date on my "test"
post just made! Perhaps this property is server-dependent.

 

[Tom Ogilvy]

And Now I am back.

--
Regards,
Tom Ogilvy

It seems pretty well established that the user's system clock determines at
least the initial settings for the email. The NNTP server may get involved,
but I believe that depends on the NNTP server. Maybe NewsRanger, makes a
correction. When I set the date to Aug 2053, SuperNews rejected it as
malformed date header.

 

[Dave Peterson]

And posted this with my correct timezone and correct time.

(6:16 pm CST)

So maybe the OP has the wrong Timezone.

 

[Doug Glancy]

Tom,

It's like you were never gone <g>.

Doug

And Now I am back.

--
Regards,
Tom Ogilvy

it.

 

[Tom Ogilvy]

I put up two posts with a year of 2053. I can't see them from SuperNews,
but I can from the Microsoft Server.

Just proves that observation is not always reliable.

 

[Tom Ogilvy]

These are parts of the headers:
----------------
Aug 15, 2053
----------------

From: "Tom Ogilvy" <[email protected]>
Subject: Re: Coverting a VBA array from 2-D to 1-D
Date: Thu, 14 Aug 2053 19:05:03 -0400
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.excel.programming

---------------------
Dec 15 2053
----------------------

From: "Tom Ogilvy" <[email protected]>
Subject: Re: Coverting a VBA array from 2-D to 1-D
Date: Mon, 15 Dec 2053 07:10:00 -0500
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
Message-ID: <[email protected]>
Newsgroups: microsoft.public.excel.programming

 

[Harlan Grove]

Another test - system date set to June 1, 1984.

 

[Harlan Grove]

It appears Newsranger does do the sensible thing and ignores whatever
date/time my system puts on what it sends to the NNTP server and uses its
own system time.

Still, it does open the possibility that the OP can't adjust the date/time
stamp on his posts.

 

[Colo]

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/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

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

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)

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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

 

[Howard Kaikow]

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.

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

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)

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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

 

[Dave]

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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

 

[Dave]

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

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)

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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

 

[Vasant Nanavati]

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

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

Please fix/set your computer clock. You are way ahead of everyone else.

--
Regards,
Tom Ogilvy

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


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)

I know I can quickly map a 2d range to an array with code such as

Sub MyArr()
Dim myarray
myarray = [A12]
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