Requesting help regarding counting of cells. 2

[ladyck3]

I must start by saying, I retired 7 years ago and what they say is true "If you don't use it, you lose it."
That said, I do remember basics but never did know much about VBA and fancy tricks. All I learned regarding the major nuances with Excel, I learned from this GLORIOUS resource. I crochet, this is my new retirement business, so to speak....

I have a graph of a picture that I personally created black (well I used dark blue) and blank cells to create a silhouette of a Peacock. Now its 125 cells by 178 rows. To work from this HUGE grid is taxing having to count as I go, 3 stitches blue, 1 white, 7 blue, 3 white, or consider it Color A, B, and if more colors, C, D,..... (7A, 1B, 7A, 3B) etc. for each stitch and each row.
So, I'm told that some of my designer friends uses macros in Excel to export a written instructions as exampled in bold.

Is it possible and if so, how, to achive this task in Excel. <crossing fingers>
Thank you extremely and in advance...

ladyck3
aka Laurie

P.S. If it would help to include a snip of the chart/spreadsheet, I'm willing to share a partial segment.

Thank you so very much in advance..

ladyck3
aka: Laurie

 

[SkipVought]

What would help is one actual row of 125 columns. In other words a workbook with a sheet that has ONE REPRESENTATIVE ROW OF DATA from your 125 columns and 178 rows.

Are we just talking about only two colors?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ladyck3]

HI SKIP!!!

I was wondering if you were still around, GREAT to see you... you've been such a help in the past.

You asked if 2 colors only, yes for this chart, but in the future, may be more...
More than 2 sounds like too much work... no worries.

I am attaching a spreadsheet with segments removed, just to get a sense of how detailed it can be.
I am Left Handed so would start from loser left and work right, this would be done one row at a time so each row is cut and the next row is started new.. always from the left.

However, if I were to share this with a Right Handed person, they would read from right and work to the left, each row. SO if possible to select or right, I think that's the most I would need, or request.

THIS .. is a Pipe dream here and sound like TOO MUCH WORK, but I thought I'd put it out there.
Just in case....
Another option, although not sure if needed at this point, would be to read continuously.
Example: Left Handed
Start lower left, work right, turn second row would be left from right but as if the chart was turned over for the second row, then turn chart again for 3rd row, etc...

Right the same, again starting from the right.

See attached spreadsheet...

Thank you so very much in advance..

ladyck3
aka: Laurie

 

[ladyck3]

OOPS, also I'm using Excel 2010

Thank you so very much in advance..

ladyck3
aka: Laurie

 

[SkipVought]

Yes, still pluggin' away.

I'm confused. Which sheet represents what's actually in a ROW?
Peacock_Chart_125w-176h Looks like the one. But its got extra stuff--
2 rows above & below
2 columns left & right

Is that how all your charts are?

I've coded an example I mocked up. That has no extraneous stuff. It will take a few modifications to start from lower left or upper right.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ladyck3]

Skip,
What I sent was the entire chart but I removed sections so its not the entire chart. I thought that maybe you may need different scenarios to test whatever theory you uncovered.

The bottom section is the start and that's great. Again, what was sent was merely showing some of the complicated sections so you know what I'm dealing with...
I know you asked for 1 row but I wanted to insure you had enough information to work with.
If you come up with a formula that works with one row, it will probably be able to be copied to the entire sheet?? I have no clue. Lost in Excel Land... its like starting over. ;(


Thank you so very much in advance..

ladyck3
aka: Laurie

 

[SkipVought]

What about the extra 2 rows & columns above, below, left & right in the first sheet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ladyck3]

I have a blank column at the beginning/left so that its not flush with the edge, its not part of the chart.
Would you like the full chart? I hate to share because of all of my work on it but I will if it will help. the blank rows are simply removed data. Oh I'll include the entire chart no extra rows/columns

Thanks for all you are doing!


Thank you so very much in advance..

ladyck3
aka: Laurie

 

[Deniall]

An interesting little problem, and quite unusual (to me at least).
My take is that the problem is best attacked by writing a user-defined function.
I have had a rough go at it.[&nbsp;] See attached.
Note that I have restricted myself to the "left to right" aspect, but extending it to handle "right to left" as well should not be difficult.

Note also that you need to copy the UDF down for the extend of the pattern because it deals with one row only.[&nbsp;] And that I have not done this copying beyond the first few rows.

 

[Deniall]

I just went to test that my upload worked OK, and discovered (again?) that my anti-malware software (Zone Alarm) does not allow me to download macro-enabled spreadsheets.[&nbsp;] I'll upload a zipped version instead, and see how that goes.

 

[Deniall]

That zipped file downloaded OK.

 

[ladyck3]

Deniall,

Interesting, your formula has tweaked my old memory a bit, I even think I understand what you've done. Like I said, if you don't use it, you'll lose it and its been 7 years and my memory has declined but I looked at the formula, and the red and green made sense as well. Cool. I'm not sure how to change it to right to left but this is a start. (for me, anyway)

I know its not busines stuff but sometimes its just fun to play with something different. I appeciate your time




Thank you so very much!

ladyck3
aka: Laurie

 

[SkipVought]

Here's my take.
4 sheets
1) Your peacock chart
2) Test
3) Stitches dashboard--Choose a Chart/Choose RH or LH/ Run procedure
4) Factors--Tables and such

add charts as desired. they will be listed on the Stitches dashboard.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ladyck3]

Thanks Skip...

I will take a look at this and more than likely, I'll have TONS of questions. Thanks for ALL of your time with this... I appreciate it...

Will investigate soon and get back to you tomorrow one way or the other.

Thank you so very much!

ladyck3
aka: Laurie

 

[Deniall]

If you've got a bit of (fading) knowledge of VBA and UDFs, the code is pretty simple to extend for right-to-left processing.[&nbsp;] See new attachment, in which you specify the direction you want in cell N3.

 

[ladyck3]

(OK, so I didn't wait until tomorrow morning, well it is tomorrow morning now, its 1:00am cdt.)

Oh SKIP!!! IT WORKS FANTASTICALLY!!!

I don't understand something....and it could have just gotten lost the translation of my rambling.
I'm not sure why the row numbers change for RH vs LH.
Row 1, is bottom left for the Left Hander, crocheting from stitch 1 to 125
Row 1, is bottom right for the right hander should start at stith 125 and work down to Stitch 1.
Whether Left or Right, we all work from bottom to top.

Designers create charts for the right hander and us lefties have to try and transpose in our heads, or
they say just 'hold it up to a mirror'. the written instructions for a right hander read like a normal paragraph or sentence but for a left hander we have to read from the end of the sentence each word from right to left.

Then for me, dyslexia sinks in and I just get frustrated and put it down and don't go back.

Now read that last statement backwards...
back go don't and down it put and frustrated get just I and in sinks dyslexia ,me for then.

See what I mean? now try following your stithes and keep the count accurate when reading it all backwards.

Your left handed chart for me is PERFECTION!!!!

Hey I don't care, (about the RH of it, not really). If you feel like playing with it that's fine but I don't expect you to do that, I'm pleased as punch with the leftiness of it
I'm not a designer, and have no desire to be a designer. I just want charts that work for me.

I don't quite understand the factors tab, where the numbers came from
Is the 6968388 the number of blue squares?
Empty 16777215 white or empty squares? so the Peacock is the blue the rest is backgroud? Or are these some sort of color codes?

I suppose if I wanted to do a different chart in the future, save this file as another name and then just do the chart as is needed?

If there are more than 2 colors, (which IN The FUUUUUUUUUTURE) could happen, is this something I can edit? That might require macro edit and I have no clue how to do that and don't even know how to look at the code much less try to change it and blow it up. When it comes to that and VBA (or is this the same)
I'm a dummy..I'm CLUELESS...

This is a true work of art!! I hope you had fun with it Something different for a change?
YOU are APPRECIATED!!!
I'm done rambling, my mind is racing and you'll never follow all my racing thoughts...

Goodness I heart you for this

THANK YOU THANK YOU THANK YOU THANK YOU!!



Thank you so very much!

ladyck3
aka: Laurie

 

[ladyck3]

Deniall,

I just saw your second file and will look at this as well.
I am documenting the information from you and from Skip.
I looked at your first file and it was pretty cool, with the option to add/change colors.
I'm keeping all of this information.

Its all valuable to me. I discount nothing
Time is valuable and I appreciate all of the assistance I've gotten thus far.

I'm going to play now

Thank you so very much!

ladyck3
aka: Laurie

 

[SkipVought]

Well the reason I made the RH to start at the top was totally my assumption based on noticing in the first chart you sent that each row was numbered forwards and backwards. That's an EZ fix.

The crazy numbers are Color codes based on the [tt]Cell.Interior.Color[/tt]. Since this workbook is designed to contain a plethora of charts, I'd like to make a way to choose more than two colors. I'll have to check Deniall's code to learn what he's doing.

Right now my laptop is broken. I do almost all my Tek-Tips on my iPad. I can do some simple things in Excel from my iPad, but coding is not one. So I must hoist myself out of my easy chair in my sunroom with my coffee cup, pack a lunch and journey, like Abraham of old, not 3 days to the mountains of Moriah, but 30 steps to my office PC, in order to code and such. So I'll get a round tuit. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ladyck3]

Well Skip,
I don't mean to tax you so. This trip, can I make you a sandwich or two? Make sure to pack an extra pair of undies and don't forget your toothbrush and tooth paste... I hope your lay-overs are not too time consuming. You flying "Stairs-Is-Us" Airlines? Or are you "skipping" there. (see what I did there) hehehehe
You're a trip, Skip <----and there too

No worries, seriously, I'm sure there are other's that need you more but I have been in appreciation of you for YEARS! You are always there for me, I was so STUNNED and O-V-E-R-J-O-Y-E-D to see you here. After your retirement I thought you'd fade into the background and POOF be gone. I'm just TICKLED PINK!!!

THANKS... for being here

 

[SkipVought]

Boy o boy. Hadn't thot that not only must I prepare for and then take this 30 step trip, but you claim that I am the trip, which might be referred to as recursive in programmer's terms. And thinking about recursive code might give you an intellectual hernia! ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein