Dump array onto worksheet

[krice1974]

Hi all. I searched help and I'm sure this has been answered hundreds of times here but I can't seem to make the given solutions apply. Anyway.

I'd like to "dump" an array onto a worksheet. It's called MyArray, it's varying in length, 7 "columns" wide, and I know the uppermost left cell I'd like to dump to.

Many thanks in advance...

Kevin

 

[krice1974]

GTO, I know that had to be the most garbled mess if you're not in my head (you're probably better off btw

It shows where I am with arrays. I have a mini stock market app that downloads all kinds of useful data, and I have big plans for it, but I've hit the proverbial wall with arrays, and need them to tie it all together.

My sanity is in your hands, my friend.

 

[krice1974]

If it's easier, you don't have to rewrite all my code... a layman's explanation of arrays specific to my purpose would be worlds of help. Again in a nutshell:

I want to copy data to an array, out of a oddly formatted web query.
I can get to continuous ranges with a loop, those are the data I wish to copy.
The data ranges vary in length but are all 7 columns wide.
After loading a range to an array, I want to continue the loop that looks for the next "block" of continuous data, coping/appending this to the same array.

Thanks again...

 

[GTO]

If it's easier, you don't have to rewrite all my code... a layman's explanation of arrays specific to my purpose would be worlds of help. Again in a nutshell:

I want to copy data to an array, out of a oddly formatted web query.
I can get to continuous ranges with a loop, those are the data I wish to copy.
The data ranges vary in length but are all 7 columns wide.
After loading a range to an array, I want to continue the loop that looks for the next "block" of continuous data, coping/appending this to the same array.

Thanks again...

It is certainly not the most garbled mess by a long shot, and the comments are helpful. Please be patient, as sinuses/head about to bust.

If you're still online, a couple of questions/comments to help get it thru my thick head...

I believe I understand that you want to load an array 7 cols by unk rows... and that there are discontiguous ranges of these "tables" - so you want to be able to find the next range and loop it into the array.*

Now to "draw the blonde guy a picture", please correct any of the above I have incorrect, and tell me if there's things we can count on or not. For example:

Lets say there's five ranges. Lets say the first one starts at A1 and ends at G122 (I realize the row may be 132, 147, or whatever, the next day).

Now, when we look for the next range, where are we looking? For instance, will it always be farther down (higher row numbers), or might there be a range that starts in L22 or some other place off to the side so-to-speak.

What is between the ranges? Can we count on truly empty cells (no weird unprintable characters)?

And I think yo already stated this, but to make sure - once we run into a blank cell (row) in one range, we can look for "company" to be in the upper/left cell of the next range, right?

Thank you, and sorry its taking me a bit to "see" your sheet.

Mark

*- ACK! I forgot. After making one big array, we're planting it some place on the other sheet...

 

[krice1974]

Mark, no reason to apologize when being so generous with your time. Sure. OK, so:

Now, when we look for the next range, where are we looking? For instance, will it always be farther down (higher row numbers), or might there be a range that starts in L22 or some other place off to the side so-to-speak.
The desired ranges will be in the same columns always (starting in column1), and we're looping down the worksheet, so they'll always be lower, but to a varying degree based on the other range sizes as you noted.

What is between the ranges? Can we count on truly empty cells (no weird unprintable characters)?
I wrote a bastardized version of this macro that copy/ pastes the ranges but is clumsy. So in my experience of using the loop to find the ranges, the cells are free of strange characters from what I can see. There are some blank cells and some not blank.

<!---------- Tacoda Tag end ----------><!-- ###### CenterContent start ###### --><!-- +++ Fragment Ad468x60Top begin +++ --><noscript></noscript><noscript></noscript><noscript></noscript><!-- Ad468x60Top begin --><!-- Ad468x60Top end -->
<!-- +++ Fragment Ad468x60Top end +++ --><!-- +++ Fragment Ad728x90Top begin +++ --><!-- Ad728x90Top begin --><!-- Ad728x90Top end -->
<!-- +++ Fragment Ad728x90Top end +++ --><!-- ###### BannerContainer start ###### --><!-- ###### topBanner start ###### --><!-- ###### topBanner end ###### --><!-- +++ Fragment NavigationTop begin --><!-- NavigationTop begin -->
<!-- NavigationTop end --><!-- +++ Fragment NavigationTop end --><!-- ###### BannerContainer end ###### --><!-- ##### wrapper start ##### --><!-- ###### lnavcontainer start ###### --><!-- +++ Fragment Search begin +++ --><!-- Search begin --><!-- Search end -->
<!-- +++ Fragment Search end +++ --><!-- +++ Fragment NavigationGoldIndex begin +++ --><!-- NavigationGoldIndex begin --><!-- NavigationGoldIndex end -->
<!-- +++ Fragment NavigationGoldIndex end +++ --><!-- ##### lcolAd start ##### --><!-- +++ Fragment AdsLeftColumn begin +++ --><!-- AdsLeftColumn begin --><!-- AdsLeftColumn end -->
<!-- +++ Fragment AdsLeftColumn end +++ --><!-- ##### lcolAd end ##### --><!-- ###### lnavcontainer end ###### --><!-- ###### ContentWrapper start ###### --><!-- ###### ContentColumn start ###### --><!-- +++ Fragment Ad405x15Top begin +++ --><!-- Ad405x15Top begin --><!-- Ad405x15Top end -->
<!-- +++ Fragment Ad405x15Top end +++--><!-- +++ Fragment PageHeader begin +++--><!-- PageHeader begin --><!-- PageHeader end -->
<!-- +++ Fragment PageHeader end +++--><!-- +++ Fragment Content start +++ --><!---------- Tacoda Tag end ----------><!-- ###### CenterContent start ###### --><!-- +++ Fragment Ad468x60Top begin +++ --><noscript></noscript><noscript></noscript><noscript></noscript><!-- Ad468x60Top begin --><!-- Ad468x60Top end -->
<!-- +++ Fragment Ad468x60Top end +++ --><!-- +++ Fragment Ad728x90Top begin +++ --><!-- Ad728x90Top begin --><!-- Ad728x90Top end -->
<!-- +++ Fragment Ad728x90Top end +++ --><!-- ###### BannerContainer start ###### --><!-- ###### topBanner start ###### --><!-- ###### topBanner end ###### --><!-- +++ Fragment NavigationTop begin --><!-- NavigationTop begin -->
<!-- NavigationTop end --><!-- +++ Fragment NavigationTop end --><!-- ###### BannerContainer end ###### --><!-- ##### wrapper start ##### --><!-- ###### lnavcontainer start ###### --><!-- +++ Fragment Search begin +++ --><!-- Search begin --><!-- Search end -->
<!-- +++ Fragment Search end +++ --><!-- +++ Fragment NavigationGoldIndex begin +++ --><!-- NavigationGoldIndex begin --><!-- NavigationGoldIndex end -->
<!-- +++ Fragment NavigationGoldIndex end +++ --><!-- ##### lcolAd start ##### --><!-- +++ Fragment AdsLeftColumn begin +++ --><!-- AdsLeftColumn begin --><!-- AdsLeftColumn end -->
<!-- +++ Fragment AdsLeftColumn end +++ --><!-- ##### lcolAd end ##### --><!-- ###### lnavcontainer end ###### --><!-- ###### ContentWrapper start ###### --><!-- ###### ContentColumn start ###### --><!-- +++ Fragment Ad405x15Top begin +++ --><!-- Ad405x15Top begin --><!-- Ad405x15Top end -->
<!-- +++ Fragment Ad405x15Top end +++--><!-- +++ Fragment PageHeader begin +++--><!-- PageHeader begin --><!-- PageHeader end -->
<!-- +++ Fragment PageHeader end +++--><!-- +++ Fragment Content start +++ --><!---------- Tacoda Tag end ----------><!-- ###### CenterContent start ###### --><!-- +++ Fragment Ad468x60Top begin +++ --><noscript></noscript><noscript></noscript><noscript></noscript><!-- Ad468x60Top begin --><!-- Ad468x60Top end -->
<!-- +++ Fragment Ad468x60Top end +++ --><!-- +++ Fragment Ad728x90Top begin +++ --><!-- Ad728x90Top begin --><!-- Ad728x90Top end -->
<!-- +++ Fragment Ad728x90Top end +++ --><!-- ###### BannerContainer start ###### --><!-- ###### topBanner start ###### --><!-- ###### topBanner end ###### --><!-- +++ Fragment NavigationTop begin --><!-- NavigationTop begin -->
<!-- NavigationTop end --><!-- +++ Fragment NavigationTop end --><!-- ###### BannerContainer end ###### --><!-- ##### wrapper start ##### --><!-- ###### lnavcontainer start ###### --><!-- +++ Fragment Search begin +++ --><!-- Search begin --><!-- Search end -->
<!-- +++ Fragment Search end +++ --><!-- +++ Fragment NavigationGoldIndex begin +++ --><!-- NavigationGoldIndex begin --><!-- NavigationGoldIndex end -->
<!-- +++ Fragment NavigationGoldIndex end +++ --><!-- ##### lcolAd start ##### --><!-- +++ Fragment AdsLeftColumn begin +++ --><!-- AdsLeftColumn begin --><!-- AdsLeftColumn end -->
<!-- +++ Fragment AdsLeftColumn end +++ --><!-- ##### lcolAd end ##### --><!-- ###### lnavcontainer end ###### --><!-- ###### ContentWrapper start ###### --><!-- ###### ContentColumn start ###### --><!-- +++ Fragment Ad405x15Top begin +++ --><!-- Ad405x15Top begin --><!-- Ad405x15Top end -->
<!-- +++ Fragment Ad405x15Top end +++--><!-- +++ Fragment PageHeader begin +++--><!-- PageHeader begin --><!-- PageHeader end -->
<!-- +++ Fragment PageHeader end +++--><!-- +++ Fragment Content start +++ --><!---------- Tacoda Tag end ----------><!-- ###### CenterContent start ###### --><!-- +++ Fragment Ad468x60Top begin +++ --><noscript></noscript><noscript></noscript><noscript></noscript><!-- Ad468x60Top begin --><!-- Ad468x60Top end -->
<!-- +++ Fragment Ad468x60Top end +++ --><!-- +++ Fragment Ad728x90Top begin +++ --><!-- Ad728x90Top begin --><!-- Ad728x90Top end -->
<!-- +++ Fragment Ad728x90Top end +++ --><!-- ###### BannerContainer start ###### --><!-- ###### topBanner start ###### --><!-- ###### topBanner end ###### --><!-- +++ Fragment NavigationTop begin --><!-- NavigationTop begin -->
<!-- NavigationTop end --><!-- +++ Fragment NavigationTop end --><!-- ###### BannerContainer end ###### --><!-- ##### wrapper start ##### --><!-- ###### lnavcontainer start ###### --><!-- +++ Fragment Search begin +++ --><!-- Search begin --><!-- Search end -->
<!-- +++ Fragment Search end +++ --><!-- +++ Fragment NavigationGoldIndex begin +++ --><!-- NavigationGoldIndex begin --><!-- NavigationGoldIndex end -->
<!-- +++ Fragment NavigationGoldIndex end +++ --><!-- ##### lcolAd start ##### --><!-- +++ Fragment AdsLeftColumn begin +++ --><!-- AdsLeftColumn begin --><!-- AdsLeftColumn end -->
<!-- +++ Fragment AdsLeftColumn end +++ --><!-- ##### lcolAd end ##### --><!-- ###### lnavcontainer end ###### --><!-- ###### ContentWrapper start ###### --><!-- ###### ContentColumn start ###### --><!-- +++ Fragment Ad405x15Top begin +++ --><!-- Ad405x15Top begin --><!-- Ad405x15Top end -->
<!-- +++ Fragment Ad405x15Top end +++--><!-- +++ Fragment PageHeader begin +++--><!-- PageHeader begin --><!-- PageHeader end -->
And I think yo already stated this, but to make sure - once we run into a blank cell (row) in one range, we can look for "company" to be in the upper/left cell of the next range, right?
Once running through a desired range, a blank cell is the end of that range. Typically four cells down (with both text and blanks in between) the word "company" is the header (right on top of) the next range. This is the left column we're doing all this work on.

Thank you, and sorry its taking me a bit to "see" your sheet.
No problemo. Thank you. I think I need this excel jeanie app.

 

[GTO]

If it's easier, you don't have to rewrite all my code... a layman's explanation of arrays specific to my purpose would be worlds of help. Again in a nutshell:

I want to copy data to an array, out of a oddly formatted web query.
I can get to continuous ranges with a loop, those are the data I wish to copy.
The data ranges vary in length but are all 7 columns wide.
After loading a range to an array, I want to continue the loop that looks for the next "block" of continuous data, coping/appending this to the same array.

Thanks again...

Hi again Krice,

Well I don't want to take you off path, but as I am understanding your attempts thus far, here are what appear to be initial "issues" to me.

I took your comment to mean that you'd rather learn rather than have it re-written straight out, so lets play with it a bit.

Sub ex()
    'Abbreviate worksheet name.
    Set UGQ = Worksheets("BriefingAnalystUpsDwnsQuery")
    Set UG = Worksheets("BriefingAnalystUpsDwns")
 
    '**Get final row of worksheet.//These refer to the same row//**
    UGQFinalRow = UGQ.Cells(Rows.Count, 1).End(xlUp).Row
    UGQFinalRowFixed = UGQ.Cells(Rows.Count, 1).End(xlUp).Row
 
 
    For i = 1 To UGQFinalRow 'Outer loop that loops through the entire worksheet looking for the word "company."
 
        'Once it finds "company", it checks for "upgrades, downgrades, and coverage initiated."
        If UGQ.Cells(i, 1).Value = "Company" Then
             '***Now lets say we found the first instance of "Company" in cell A20.  
             '***Since "Company" is in the upper/left corner of the range, why are 
              '***we going back up to Cell A17 ?        
            Select Case UGQ.Cells((i - 3), 1).Value
                Case "Upgrades"
                    RatingType = "Upgrades"
                Case "Downgrades"
                    RatingType = "Downgrades"
                Case "Coverage Initiated"
                    RatingType = "Initiated"
                Case "Coverage Reit/Price Tgt Changed*"
                    RatingType = "TargetChange"
            End Select

 

[krice1974]

Right on about the double assignment. Long day with this, gets hard to see the forest from the trees, if that applies...

We're looking up from the word "company" to find a value that gets added to its own column, kind of modifying the range in a sense. Here's a snippet of the worksheet:

"Upgrades" This is the value I want to keep to add that isn't in the range (yet) but that I put in my refined table.

blank space skip

"sort alphabetically" skip

"Company" data of interest immediately below
"China Petroleum"

So in my new refined table that I dump the query onto, the last column will say "Upgrades" for each record that was in that section just below it. As the loop goes to the next range, it gets "downgrades" and the range below it, then "coverage initiated" and so on...

 

[GTO]

Right on about the double assignment. Long day with this, gets hard to see the forest from the trees, if that applies...

We're looking up from the word "company" to find a value that gets added to its own column, kind of modifying the range in a sense. Here's a snippet of the worksheet:

"Upgrades" This is the value I want to keep to add that isn't in the range (yet) but that I put in my refined table.

blank space skip

"sort alphabetically" skip

"Company" data of interest immediately below
"China Petroleum"

So in my new refined table that I dump the query onto, the last column will say "Upgrades" for each record that was in that section just below it. As the loop goes to the next range, it gets "downgrades" and the range below it, then "coverage initiated" and so on...

Yikes! Could you please show us a picture of a couple of "before" ranges and an "after"?

Or - if you belong to 4shared.com or some other posting site, a copy of an example workbook.

I am sorry, but I just am not getting a clear picture. I am happy to be of what help I can be, but without a clear picture I think we are spinning our wheels a bit.

Hope you can do one of the above and I'm sure you'll get a solution

Mark

 

[krice1974]

I need to get that jeanie, I know it. It's too much to convey by description. It might be much shorter anyway to just give a quick syntax lowdown "quick and dirty" of the array processes I need, posted earlier?? *fingers crossed

declaring an array of a known width, unknown length, varying data types to fill it.

loop through a continuous range, filling the array.

resume filling it after other code is executed.

I ask this knowing there are generalizations and this is theoretical of course...

 

[krice1974]

OK, trying excel jeanie now.

Before:

Excel Workbook
	A	B	C	D	E
59	Ratings Systems
60	Corrections
61	Updated 12-Mar-09 14:04 ET
62	Upgrades
63
64	Sort alphabetically | Sort by brokers
65	Company	Ticker	Brokerage Firm	Ratings Change	Price Target
66	Men's Wearhouse	MW	Wedbush Morgan	Sell Hold	$10
67	Intel	INTC	AmTech Research	Neutral Buy	$13 $17
68	Men's Wearhouse	MW	Stifel Nicolaus	Hold Buy	$14
69	China Petroleum (Sinopec)	SNP	UBS	Neutral Buy
70	Somanetics	SMTS	Sun Trust Rbsn Humphrey	Reduce Neutral
71	Johnson Controls	JCI	Deutsche Securities	Hold Buy	$12
72	Hexcel	HXL	Deutsche Securities	Hold Buy	$12 $10
73	Abiomed	ABMD	Jefferies & Co	Underperform Hold	$8 $7
74
75	Downgrades
76
77	Sort alphabetically | Sort by brokers
78	Company	Ticker	Brokerage Firm	Ratings Change	Price Target
79	Bronco Drilling	BRNC	BMO Capital Markets	Market Perform Underperform
80	NASDAQ	NDAQ	Fox Pitt	Outperform In Line
81	Exelon	EXC	BMO Capital Markets	Outperform Market Perform
82	Genentech	DNA	Lazard Capital Mkts	Buy Hold
83	LDK Solar	LDK	Soleil	Buy Sell	$3
84	Metabolix	MBLX	Piper Jaffray	Buy Neutral
85	LDK Solar	LDK	Piper Jaffray	Neutral Sell
86	XOMA Limited	XOMA	Merriman Curhan Ford	Buy Neutral
87	Carrizo Oil & Gas	CRZO	Jefferies & Co	Buy Hold	$37 $9.50
88	Parallel Petroleum	PLLL	Jefferies & Co	Buy Hold	$12 $1
89
90	Coverage Initiated
91
92	Sort alphabetically | Sort by brokers
93	Company	Ticker	Brokerage Firm	Ratings Change	Price Target
94	Javelin Pharmaceutic	JAV	Wedbush Morgan	Buy	$6
95	MAKO Surgical	MAKO	Ladenburg Thalmann	Buy	$10
96	Cabela's	CAB	Feltl & Co.	Hold	$9
97	Thoratec	THOR	Wedbush Morgan	Buy	$39
98	Allos Therapeutics	ALTH	FTN Equity Capital	Neutral
99	Acorda Therapeutics	ACOR	FTN Equity Capital	Buy
100	United Therapeutics	UTHR	FTN Equity Capital	Neutral
101	Auxilium Pharma	AUXL	FTN Equity Capital	Buy
102	Biomarin Pharm	BMRN	FTN Equity Capital	Buy
103	Alexion Pharma	ALXN	FTN Equity Capital	Buy
104	Visa	V	Susquehanna Financial	Positive
105	Whitney Holding	WTNY	Stifel Nicolaus	Hold
106	WMS Industries	WMS	Brean Murray	Hold
107	Bally Technologies	BYI	Brean Murray	Buy	$24
108	EV3	EVVV	BMO Capital Markets	Market Perform
109	Progenics Pharm	PGNX	FTN Equity Capital	Neutral
110	AMAG Pharma	AMAG	FTN Equity Capital	Buy
111	Mechel Steel	MTL	JP Morgan	Neutral
112	Medicis	MRX	Piper Jaffray	Sell
113	Allergan	AGN	Piper Jaffray	Buy
114	Sequenom	SQNM	JMP Securities	Mkt Outperform	$25
115	MedAssets	MDAS	ThinkEquity	Buy	$17
116	Endo Pharm	ENDP	Piper Jaffray	Neutral
117	Altera	ALTR	Piper Jaffray	Buy
118	Charles Schwab	SCHW	Citigroup	Buy	$16
119	TD Ameritrade	AMTD	Citigroup	Hold	$13
120	E*TRADE	ETFC	Citigroup	Sell	$0.25
121	American Capital Agency	AGNC	Keefe Bruyette	Mkt Perform	$17.50
122	Zenith National Insurance	ZNT	Sun Trust Rbsn Humphrey	Buy	$28
123	Data Domain	DDUP	Merriman Curhan Ford	Neutral
124
125	Coverage Reit/Price Tgt Changed*
126
127	Sort alphabetically | Sort by brokers
128	Company	Ticker	Brokerage Firm	Ratings Change	Price Target
129	Hasbro	HAS	Wedbush Morgan	Hold	$28 $24
130	Bronco Drilling	BRNC	Jefferies & Co	Underperform	$4 $2
131	Apollo Group	APOL	Argus	Buy	$87
BriefingAnalystUpsDwnsQuery

After:

Excel Workbook
	A	B	C	D	E	F
156	Men's Wearhouse	MW	Wedbush Morgan	Sell Hold	$10	Upgrades
157	Intel	INTC	AmTech Research	Neutral Buy	$13 $17	Upgrades
158	Men's Wearhouse	MW	Stifel Nicolaus	Hold Buy	$14	Upgrades
159	China Petroleum (Sinopec)	SNP	UBS	Neutral Buy		Upgrades
160	Somanetics	SMTS	Sun Trust Rbsn Humphrey	Reduce Neutral		Upgrades
161	Johnson Controls	JCI	Deutsche Securities	Hold Buy	$12	Upgrades
162	Hexcel	HXL	Deutsche Securities	Hold Buy	$12 $10	Upgrades
163	Abiomed	ABMD	Jefferies & Co	Underperform Hold	$8 $7	Upgrades
164	Bronco Drilling	BRNC	BMO Capital Markets	Market Perform Underperform		Downgrades
165	NASDAQ	NDAQ	Fox Pitt	Outperform In Line		Downgrades
166	Exelon	EXC	BMO Capital Markets	Outperform Market Perform		Downgrades
167	Genentech	DNA	Lazard Capital Mkts	Buy Hold		Downgrades
168	LDK Solar	LDK	Soleil	Buy Sell	$3	Downgrades
169	Metabolix	MBLX	Piper Jaffray	Buy Neutral		Downgrades
170	LDK Solar	LDK	Piper Jaffray	Neutral Sell		Downgrades
171	XOMA Limited	XOMA	Merriman Curhan Ford	Buy Neutral		Downgrades
172	Carrizo Oil & Gas	CRZO	Jefferies & Co	Buy Hold	$37 $9.50	Downgrades
173	Parallel Petroleum	PLLL	Jefferies & Co	Buy Hold	$12 $1	Downgrades
174	Javelin Pharmaceutic	JAV	Wedbush Morgan	Buy	$6	Initiated
175	MAKO Surgical	MAKO	Ladenburg Thalmann	Buy	$10	Initiated
176	Cabela's	CAB	Feltl & Co.	Hold	$9	Initiated
177	Thoratec	THOR	Wedbush Morgan	Buy	$39	Initiated
178	Allos Therapeutics	ALTH	FTN Equity Capital	Neutral		Initiated
179	Acorda Therapeutics	ACOR	FTN Equity Capital	Buy		Initiated
180	United Therapeutics	UTHR	FTN Equity Capital	Neutral		Initiated
BriefingAnalystUpsDwnsQuery

(both are partial, not the entire range, but all relevant areas are represented)

Thanks so much.

 

[GTO]

REference the pic, thanks

It might be much shorter anyway to just give a quick syntax lowdown "quick and dirty" of the array processes I need, posted earlier?? *fingers crossed

declaring an array of a known width, unknown length, varying data types to fill it.

...

I ask this knowing there are generalizations and this is theoretical of course...

I probably should have responded to that earlier. I am certainly not a "expert" coder, and leastwise to match my abilities, I of course would have to have my head around something before attempting to explain it.

Very generically, its pretty easy to build an array dynamically, but one of the issues I thought I spotted in the current code was that at points it seems to be getting referred to as a single dimension array, and at other places a two dimensional array. I could be wrong, as stated, I keep getting pulled away.

In short however -

ReDim Preserve DataArray(ArrayLength)

will not work if its a two dimensional array. Neither will:

DataArray(i + j, 1) = UGQ.Cells(i + j, 2).Value 'Symbol load into array

as you are attempting to increase the upperbound of the first dimension, and you can only increase/resize the last dimension.

Thus, I was thinking that building it "backwards" or transposed might be a way to go. It's later where you are at, so let me see if I can come up with something understandable as well as reliable. Will try and post back later tonight (semi-doubtful) or tomorrow. While there's plenty of folks here with much more knowledge than yours truly and you might already have some great answers before I can get it done, I will post back if there's not already a better solution (than I am able to produce).

Mark