Automatic graph creation - rows and columns irregularity

[marky9074]

I have inherited some code that sucks in data into various sheets, then displays this by automatically creating graphs by VBA. This uses a predefined column increment in code (I have min, max, mean and SD fields - so every fourth starting from the first 'mean' item), and count defined by the variable 'NCols'.

Now, everything works but one type of data. This is defined as 'NComp'. So 'NCol' = 'NComp'.

For some reason, when the number of rows is less than the 'NComp' I get a 'Series Formula is too long' error. But, when I stop/reset the module and look at the sheet, the rows selected are still highlighted and are correct!

I have looked at this over and over again, and I cannot see any relationship between the number of rows and number of columns (defined in NCol), but when I test by entering a lower number to use for 'NComp'.. boom, code works again, albeit obviously now with a lower number of series. The attached file has everything stripped out bar the working sheet, and the data sheet. All of the code is removed, except the plot function, so hopefully it should be easy to follow

I am sure someone will put their finger straight on it, but I am pulling my hair out as to why these are mixed up!

[marky9074]

I should have mentioned that this has 22 data rows, and the number for 'NComp' is set at 21.. try the graph function and see it works, then delete a row and see it fail...

[JosephP]

try changing the routine that creates the chart to

Please Login or Register  to view this content.

to be honest I reckon that code could do with rewriting-I don't think the programmer knew too much about vba or the excel object model

[marky9074]

Works like a charm! Thanks a lot. Such a helpful site, with helpful people

Yes, code is old and cranky... I can offer beer tokens to whoever would like to clean it up... it's more of the same, but not rocket science..

[marky9074]

I have one question though, that I forgot to ask earlier... the data originally used to be stored in 'mean, min, max, SD', however the format of the input file has changed and is now 'min, max, mean, SD'. The routine picks up the series names from two cells above where the data part starts, but my headers are in the top left cell (of the four) and 'Centered Across Selection' so are now not picked up. I could move the headers to column '3' instead of '1' but you cannot 'Center Across Selection' to include cells to the left (only to the right), so it looks naff....

Is there any way I can get it to pick up the series name as an offset from the main column start?

[JosephP]

try just changing that code to

Please Login or Register  to view this content.

[marky9074]

Ahhhh that was an easy one... I'm started to get my head around what does what now... thanks again

[marky9074]

Obviously I need to apply the -2 correction to your original code selection though

[JosephP]

you're welcome :-)

please don't forget to mark the thread solved (click thread tools at the top)

[marky9074]

Odd, something I never noticed before... the 'frig for offset sheets' doesn't work... well, it works for the data, but the series labels continue on without jumping to the next multiple of 64... strange

Swapping back to the old version of createplot was no different, so that must have always been screwed...

Please Login or Register  to view this content.

[JosephP]

I didn't look at that part at all-what is it supposed to be doing?

[marky9074]

It's more or less the same sort of thing. However in this case the sheet is set up for multiple NSrc (Gun on the sheet). Basically when you open the file for the first time it asks you how many source and streamers there are (NStrm & NSrc) and unhides the appropriate sheets (the maximum number of streamers is set as 16)...

So the fixed increment for data items is 16*4 = 64 plus the offset from the start of the sheet 53... which works ok for the data but not the series label...

I've attached just the data sheet & pdf of the chart as is to show what I mean about the hidden sheets etc (now unhidden). I can strip it all, or upload the complete mess if it helps..

[JosephP]

have you tried stepping through the code to check if the offset part is actually being run?

[marky9074]

Aha! I just cut out that bit of code, and its the same... so no, I guess its not using the offset part.. some more digging methinks!

[JosephP]

this line

Please Login or Register  to view this content.

is case sensitive-perhaps that's the issue?

[marky9074]

I've looked and looked and can't make head nor tail of it... on face value it looks like it is ignoring the frig for offsets under CreatePlot, as this is the bit where the labels are defined (as I learnt earlier..!)

I changed the sheet to be just Offsets, with plot name Offsets, just to see if it was a thing with spaces in the name.. but still no joy.. it must be something to do with the sheet/plotname

[JosephP]

what steps do I need to take with that file? (may not get back to you for a while as I have client work that I really ought to be doing!)

[marky9074]

Sorry to keep dragging you away from real work! The other button brings up the second plot routine - select 'Source-RX Offsets' and it brings up the graph in question. On this one you can see that the data gets correctly picked up using the offset frig (16 streamers max 16 * 4 = 64), but the series labels pick up is continuous i.e. NCols = NSrc * NStrm (2 x 10)

Cheers,

Mark

[marky9074]

I've been cleaning the code up a bit in other places but have come across another problem. Some of the code copies a range from one sheet to another, but the destination sheet has hidden rows. I want it to ignore the hidden rows when pasting into the sheet. I have found loads of examples of copying 'without' hidden rows, from the source sheet, but none showing how to ignore hidden rows at the target...

[JosephP]

that's probably because that tends to be a sign of bad worksheet design! you'll probably be best off copying one row at a time and using end(xlup) to position the paste operation, assuming you have a column that is always populated for each row of data

[marky9074]

I know what you mean! I think that when the guy originally wrote the code he assumed that there would be equal number of data items per platform line, so in the depth example yesterday the sheet would be multiples of 30 (x4) with the pretense that you would never have more than 30 units on a platform line. Then he calls an un-hide routine to show only the first nth data fields, then resets the active column to the next multiple of 30, rinse and repeat... (the object of this is to have the formatted headers/cells with border, label name etc).

Unfortunately little thought has been given to what the input data is.. if it was separated by headers, I could search for the header, copy nth units, then skip to the next 30 column, search for the next header etc...

At present I have all the data in one block, so I need to copy each platform line (21 in this example) then skip 9 columns before copying the next item... all of the redundant columns are hidden, so if I could find a way to copy and ignore the hidden columns it would work..

[JosephP]

I'll have a look at the chart after I get a little shuteye-been a long day. this is kinda stretching the boundaries of a forum question for me though ;-)

[marky9074]

Thanks for your help! I keep getting dragged away for real work too! But I'm slowly getting there when I return to the dreaded spreadsheet!

[marky9074]

I had a brain fart earlier.. I can still use the fixed number of column to get the labels/formatting etc, but once the appropriate columns are unhidden I can just delete the rest (and then the block data import works well)... however I haven't found anything to delete 'All' hidden columns in a sheet yet....

[JosephP]

you've gotta loop-pseudocode:

Please Login or Register  to view this content.

[marky9074]

Perfect! Thanks again