Convert two dimensional row to column data to a hard-coded array

Hello Forum,

I'm struggling to design a reasonable way that will turn two dimensional data into a hard-coded two dimensional array. The data is a long list that looks like the following (each comma here is a new column and each carriage return is a new row):

33.03349 , [M+H]+ , CH3OH , Methanol, Acetonitrile / solvent
42.03383, [M+H]+, CH3CN, ACN, Acetonitrile / solvent
.....

The idea is to take the data in the sheet, convert it in some reasonable way (concatenation maybe...) and then enter the data into the array similar to the following:

pcon(0,0) = 33.03349
pcon(0,1) = [M+H]+
pcon(0,2) = CH3OH
pcon(0,3) = Methanol
pcon(0,4) = Acetonitrile / solvent
pcon(1,0) = 42.03383
pcon(1,1) = [M+H]+
pcon(1,2) = CH3CN
pcon(1,3) = ACN
pcon(1,4) = Acetonitrile / solvent
....

Is there an intelligent way to iterate this or loop through it?

I started down this road:

Please Login or Register  to view this content.

Thanks for the response. That is an elegant way to move the data within a sheet to an array - thanks.

I was hoping to "hard-code" the data such that all the data is contained within the code and not generated by a sheet range reference. My objective is to have a template file containing all blank sheets and a back-end reference library.

Here's an idea

Please Login or Register  to view this content.

This looks like a good way to separate it out. I attempted to paste one section that had 750 records and I was notified with the "Too many line continuations error message" which seems to happen ~ 30 records. I used the following line to concatenate the first and then the rest of excel data rows:

Formula:

Please Login or Register  to view this content.

=

I then entered a segment of this data into VBA:

Please Login or Register  to view this content.

Perhaps the solution is to create a hidden sheet with the information and use the first recommended array?

Thank you for all of your recommendations. I agree that sifting through the code to apply an update would be unnecessarily complicated. Thumbs up on both code recommendations as they are directly applicable.

The hidden sheet with a dynamicly referenced array method will be the road to take here; errors combined with complex modifications makes the hard-coded approach a poor choice.

Cheers.

I was notified with the "Too many line continuations error message" which seems to happen ~ 30 records.

You can separate and continue like this.

Please Login or Register  to view this content.