Improving a production workbook through vba to find and index operator information.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

It's OK TMS, you can point user in a proper direction.

TMS,

I apologize for not stripping down one of our production reports so that i could post it on here for everyone to view. As far as a structure i did in fact give that. In B4 is an operators name... in Q4 is how long the equipment ran... in R4 is how well the line produced... in R4 is the quality value of the product. I was trying to give enough to get a picture of the sheet without posting one. I figured refering to cells and giving them names would sufficient. Obviously I was wrong.

I cannot post a copy of our sheet... sorry it is proprietary. I have been asked to make improvements.

What i gather is that is not the place for me to ask my questions, I will go elsewhere. Thank you for the timely response.

PRD

It's a good place to ask question but solution greatly depends on your input.
And you are not willing to share input data.

Nobody ask you to put real data.
Just to mantain formatting important to understand a problem.

For example, to put xxxxx instead of names and 12345 instead of price.

Without some kind of example I doubt anyone can give you answer.

Originally Posted by zbor

It's OK TMS, you can point user in a proper direction.

It was hardly a point in the proper direction. It was an immediate reaction that screamed "You have nothing and want someone else to do it, go away". When what I was actually trying to accomplish was not reveal proprietary information while initiating a conversation. All well...

PRD

Originally Posted by zbor

It's a good place to ask question but solution greatly depends on your input.
And you are not willing to share input data.

Nobody ask you to put real data.
Just to mantain formatting important to understand a problem.

For example, to put xxxxx instead of names and 12345 instead of price.

Without some kind of example I doubt anyone can give you answer.

I'm not unwilling to put together an example. But I will not deal with someone assuming I havent a clue. Ive tried some things and I cannot get it to work.

I may have been a little sensitive. I had worked all night... and I was very concerned with not sharing info I couldnt.

So when I say quarters I mean that every day for each shift we fill out a worksheet. So the very lean example I have is just one day and a few names and numbers.

I would like the last page to spool the date frome each sheet, that is the only unique identifier. Then add the name of each operator with the 3 values from the date.

So 01-01-16 would look like

01-01-16 John S. 1st value 2nd value 3rd value.

But it need to add a row for each instance there is a name in one of the cells identified in column B.

I can make it do it with a long list = values and make it ugly, I thought VBA would be cleaner and work better in the background.

Thanks,
PRD

Yes... We're good now. Like I said I was very concerned with what data I put on the internet when I first posted.

The code doesn't look all that tough, but I kept losing the battle trying to retrieve the data and report a separate instance for each line with data. Either way I'd like to be better at coding, it really makes automating workbooks easy.

Thank you for the help, I'll play with the replication and the clearing of the old data first.

Originally Posted by TMS

You're welcome. We good now?



The code isn't that sophisticated. All it does is loop down column B taking every third entry from row 4 until it runs out of data. Whatever is in those rows, and only those rows, gets copied ... 'cos that is what you said way back. In your sample file, there are only three entries, but it would cope with more.

Also note that I replicated worksheets and loop through them too. That may not be what you want but easier to take it out than back fit it.

Silly question... Since i inherited this workbook and project i was unaware there was hidden information. How do I stop the array at row 39?

PRD

Originally Posted by TMS

Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Oh my gosh... it was right in front of me. Ive messed with everything but that. I had the offset all messed up for a minute there.

Originally Posted by PRD6

Oh my gosh... it was right in front of me. Ive messed with everything but that. I had the offset all messed up for a minute there.

I found what is breaking most of this. Occasionally there is an instance where a row has been inserted since a 3rd peodunt was run. I may have to adjust the structure of the worksheet for this to operate properly. Just make every instance capable of 3 products, then the offset is 4 instead of 3.

Like I stated, I inherited this and now I am trying to remove the redundant reporting that we do.

So what I've done is stripped one of these reports to the bare minimum. Removed anything important that I cannot share, and all the unnecessary ancillary data that is not needed. The structure is there and I've added some notes on the spreadsheet itself. I'll be honest, I could not figure out how to do this with VBA so I resorted to writing vlookup formulas. What I ended up with was a mess and only a slight time saver. The code you shared before worked... until I tried it on some copies of archived reports. That's when it hit me that occasionally a production line will run 3 products instead of 2, that messes up the offset for loop. Also it is entirely possible that two operators will share a production line, in which case we give them both credit for the numbers.

When this worksheet was written/created this was never a thought, that's why I mentioned the structure itself. The layout doesn't boad well to extracting the info, at least in my opinion. Maybe you can see something I can't, and I'll learn something along the way.

Thanks again,
PRD

As far as splitting the lines goes both operators would get whatever the production line did. No clever math, just two operstors sharing the wealth of the run.

You should see this thing before i stripped it down. A ton of calculation going on. And then we do a sheet for each workday/shift per quarter. So by the time we're ready to switch quarters it takes 2 minutes to process the whole workbook. So I guess if the code read so that anyone in the operator column recieved credit for what the line did it would work.

This is why I wish I knew more, in fact my book will arrive this week.

At least now you can see how I got stumped. This report was made in a way to post on a board, not capture data. Now that it is aesthetically pleasing I should probably keep it that way.

PRD

Originally Posted by TMS

Sorry; forgot to reply about the book. If you've ordered anything by John Walkenbach, you won't go far wrong.

Yes, out of all the choices for books I chose one written by Mr. Walkenbach.

Thanks for looking at that again. I'll give it a look when I am at work tonight.

PRD

Originally Posted by TMS

OK, try this:

Please Login or Register  to view this content.

Quick update. I got a chance to work with this the other night. When i plugged all the info in and added the VBA to the existing workbook i had an error in the code.

The error occurs in:

format column A as *Text* to take Date (initially)
shOS.Range("A" & lNR).Resize(j, 1).NumberFormat = "@"

I didnt get a chance to really dig in to it. Everything works on the few pages of examples, but it will not work in the existing workbook. I will ve able to look at some more in a day or two when i get back to work.

PRD

Something about this line is breaking this code.

shOS.Range("A" & 1NR) .Resize (j, 1) .NumberFormat = "@"

Any idea what? I've tried checking the dates across the workbook, the formatting across the workbook. I'm lost at this point.

Perry

Originally Posted by TMS

What does "breaking the code" mean? What error do you get? What sheet is being processed? What is the value of lNR? What is the value of j?

Hover the cursor over sh.Name. Is that sheet the same layout as the others? Chances are it's not lNR as that's the next row to post data on the Summary sheet ... but useful to check. Most probably j is zero ( 0 ) and that could happen if you have gone through the loop and not found any valid entries.

That line of code is just establishing the formatting before it drops values in further down.

Sorry, when I say "breaks the code" it goes back to a coworker and I that always messed with odd programming stuffs. Just our glib for the error message and the yellow highlighted section.

There is something about about having all this data together. It all works until i put the code into the workbook. Sample worksheets that I put together work. I'll keep looking. I work again tonight.

Thanks,
PRD

Originally Posted by TMS

As I said, check which sheet is being processed, and the values of the variables. I suspect there may be hidden sheets, or sheets that are laid out differently. Hence, you may get j=0. You could put a check in for that in an If block. Or just use On Error Resume Next.

But you need to work out what's causing it before trying to handle it.

There is so much hidden in these workbooks... years of copying and pasting, hiding and modifying. Im hoping to get permission to rewrite these production reports. For the very reason of extracting the data we use to evaluate. I can make it report out pretty, but the data needs to be arranged properly.

PRD

I agree, I can't share the info I have... rules rules rules. I really appreciate your assistance on this, if I can figure out a way to share what I have I will. But I think I'm stuck with what I have.

PRD

Old news I know but just in case you were curious...

Every sheet was creating an error by having blank cells where it was looking for info. On top of that the way the sheets are structured for the math to work on the bottom you have to clear out the rows that aren't being used, something I will fix. So blank spaces basically created all this havoc.

Thank you again for all your help and now I'll get to work making these sheets work correctly.

Thanks,
PRD