PDA

View Full Version : Solved: Thinking outside the Square



Aussiebear
05-09-2007, 08:14 AM
Those of you, who have been following my erratic postings would remember the Grain Sampling & Residue Testing workbook concept that I've been working on. ( Yes Yes I know, its a bit like watching "Days of our Lives" - absolutely riveting stuff, I hear you say).

Some of you, might even recall me saying that the Company has decided to "borrow" ( Steal is such a harsh word isnt it), the concept and transpose it into a database. Boo, hiss, Thumbs down etc for the bad guys!!!:motz2:

I'd like to put one last effort into the workbook and was wondering if the following is possible.

On the first sheet of the example attached is copy of the worksheet "Whiteboard" which belongs to the workbook "Grain Sampling & Residue Testing". A the end of each day, I manually transpose onto a piece of paper all those non sampled Vendors who need to have samples sent away for analysis. Examples are those vendors in Buckets 1, 3, 4, 10, 11, 19 etc.
I then close this workbook and open another workbook SD.xlt, which has the single unnamed sheet which is shown in the attached workbook as "Sample Dispatch". I then manually enter in the details, grouping graintypes and test types and where possible no less than 3 vendors in any one group, as per the Company's "Thou shall not disobey this rule" request.

On a side note, the Company I work for, uses highly motivational techniques, like "daily whippings" & "No. 9 Boots", if you breech any of these "Thou shall not disobey this rule" guidelines.... but I digress somewhat.

So here comes the outside the square bit...

Is it possible therefore to have a filtered list transferrable from one workbook to another? If not, is it then possible for me to have the Sample dispatch worksheet attached to the initial workbook, so that when I have completed the sheet I can save the sheet as a seperate workbook as SD ( sequential number).xls?

Bob Phillips
05-09-2007, 08:59 AM
Of course it is ... so get on with it.

Bob Phillips
05-09-2007, 09:00 AM
Oops ... I was forgetting that I don't work for your company. Isn't it just a simple matter of running a macro that reads the Whiteboard sheet, looks for the N's and copies the details into Sample Dispatch sheet?

Paul_Hossler
05-09-2007, 09:24 AM
Q: what is the selection criteria? Doesn't seem to be just "N" in Col E --buckets 10, 11 have "Y"

Q: what would the SD sheet look like with data filled in from Whiteboard? Don't understand the "No less than 3 ..."

Aussiebear
05-09-2007, 12:18 PM
Bob, ROFL.... Macro..... How? For the first question or the Second?


Paul, Please find attached what the Sample Dispatch would look like. The vendors requiring a sample dispatch are as you suggested, those who have a "Y" in any test which is unaccompanied by an "S" value. Those who have a "Y" with any other value have already been tested and the value represents a result.

johnske
05-09-2007, 02:03 PM
Of course it is ... so get on with it.
Sun - get on with it :whip
Mon - get on with it :whip
Tues - get on with it :whip
Wed - get on with it :whip
Thur - get on with it :whip
Fri - get on with it :whip
Sat - get on with it:whip and... for good measure :whip
:)

Paul_Hossler
05-09-2007, 02:37 PM
Aussiebear, Here's at least a start.

The SD template sheet is in a hidden sheet that is copied to make a new WB
The required data is copied from Whiteboard to the new WB
Named ranges in Whiteboard WB keep track of Dispatch number


Paul



Option Explicit
Const gsTitle As String = "Make an Sample Dispatch Sheet"
Const gsVer As String = "ver 1.00, 2007/05/09"
Dim aOutputRows As Variant

Sub MakeSD()
Dim wbWhiteboard As Workbook, wbSD As Workbook
Dim wsWhiteboard As Worksheet, wsSD As Worksheet, wsSDH As Worksheet
Dim rWhiteboard As Range, rRow As Range
Dim rCompNum As Range, rDispatchNum As Range
Dim iWhiteBoard As Long, iSD As Long

'ask user
If MsgBox("Do you really want to make a sample dispatch sheet?", _
vbQuestion + vbOKCancel, gsTitle & " (" & gsVer & ")") = vbCancel Then
Exit Sub
End If

'setup
Application.ScreenUpdating = False

Set wbWhiteboard = ThisWorkbook
Set wsWhiteboard = wbWhiteboard.Worksheets("WhiteBoard")
Set wsSDH = wbWhiteboard.Worksheets("Sample Dispatch")

'unhide SD and copy to make new WB
With wsSDH
.Visible = xlSheetVisible
.Copy
.Visible = xlSheetHidden

Set rCompNum = [CompNum]
Set rDispatchNum = [DispatchNum]

Set wbSD = ActiveWorkbook
Set wsSD = ActiveSheet

wsSD.Cells(3, 3).Value = Format(Now, "mm/dd/yyyy")
wsSD.Cells(1, 1).Select
End With

'not elegant, but faster
aOutputRows = Array( _
7, 8, 9, 10, 11, _
14, 15, 16, 17, 18, _
21, 22, 23, 24, 25, _
28, 29, 30, 31, 32, _
35, 36, 37, 38, 39, _
42, 43, 44, 45, 46, _
49, 50, 51, 52, 53)

'look for data to move
Set rWhiteboard = wsWhiteboard.Cells(1, 1).CurrentRegion
Set rWhiteboard = rWhiteboard.Cells(2, 1).Resize(rWhiteboard.Rows.Count - 1, rWhiteboard.Columns.Count)

iSD = 0
For Each rRow In rWhiteboard.EntireRow.Rows
With rRow
If .Cells(1, 5).Value = "N" And .Cells(1, 11).Value = "" Then
wsSD.Cells(aOutputRows(iSD), 3).Value = .Cells(1, 2).Value
wsSD.Cells(aOutputRows(iSD), 4).Value = .Cells(1, 3).Value
wsSD.Cells(aOutputRows(iSD), 5).Value = .Cells(1, 4).Value
wsSD.Cells(aOutputRows(iSD), 6).Value = .Cells(1, 1).Value

'see if this is a starter row
If iSD Mod 5 = 0 Then
'update the Whiteboard stored value
wsWhiteboard.[DispatchNum].Value = wsWhiteboard.[DispatchNum].Value + 1
wsSD.Cells(aOutputRows(iSD), 2).Value = wsWhiteboard.[DispatchNum].Value
End If

iSD = iSD + 1

If iSD = 35 Then
Call MsgBox("SORRY!!! You can only do 35", vbCritical + vbOKOnly, _
gsTitle & " (" & gsVer & ")")
Exit Sub
End If
End If
End With
Next
End Sub

Bob Phillips
05-09-2007, 03:59 PM
Bob, ROFL.... Macro..... How? For the first question or the Second?
It would be for the firt.

It would be a simple loop that tests the identifier column, and when it is the target value, just copy the details across to the Dispatch sheet, next free item.

Aussiebear
05-10-2007, 01:41 AM
Sun - get on with it :whip
Mon - get on with it :whip
Tues - get on with it :whip
Wed - get on with it :whip
Thur - get on with it :whip
Fri - get on with it :whip
Sat - get on with it:whip and... for good measure :whip
:)

Stone the flaming crows... Just where abouts in Brisbane do you work John?

Not near Dinmore I hope!

Aussiebear
05-10-2007, 02:39 AM
Aussiebear, Here's at least a start.

The SD template sheet is in a hidden sheet that is copied to make a new WB
The required data is copied from Whiteboard to the new WB
Named ranges in Whiteboard WB keep track of Dispatch number


Excellant.






'look for data to move
Set rWhiteboard = wsWhiteboard.Cells(1, 1).CurrentRegion
Set rWhiteboard = rWhiteboard.Cells(2, 1).Resize(rWhiteboard.Rows.Count - 1, rWhiteboard.Columns.Count)

iSD = 0
For Each rRow In rWhiteboard.EntireRow.Rows
With rRow
If .Cells(1, 5).Value = "N" And .Cells(1, 11).Value = "" Then
wsSD.Cells(aOutputRows(iSD), 3).Value = .Cells(1, 2).Value
wsSD.Cells(aOutputRows(iSD), 4).Value = .Cells(1, 3).Value
wsSD.Cells(aOutputRows(iSD), 5).Value = .Cells(1, 4).Value
wsSD.Cells(aOutputRows(iSD), 6).Value = .Cells(1, 1).Value


Here's where it needs to be different. The actual determing factor of whether a vendor needs to be sampled becomes a little more complex.
Rather than using the "N" value we need to be looking at any "Y" which is accompanied by a "" in the next cell of the same row. Whilst the code works for those requiring a first test, there will be times when a Vendor needs to be sampled for 1st (Col K), 250 tons (Col M), 500 tons(Col O), 1000(Col Q), 1500 tons (Col S) and 2000tons (Col U).

Further more there is a requirement to keep same grain types together ( All Sorghum, All Barley, All Wheat, All Corn and All Triticale), as well as keeping similar tests in similar groups.

This is why last night at 1.30, when all good employees should have been in bed, snoring their heads off, I had this "vision".

Of a filtered list which appeared as a floating form on top of the Sample Dispatch sheet. I was then able to pick and choose off the list by dragging the preferred row of the filtered data onto the Sample Dispatch sheet in a preferred position. By being able to do this "Whizz Bang piece of magic" I could overcome all the rules set by the Company, when determing who, what and when, had their grain sampled.

See, I did drop a hint that this was going to be somewhat "Outside the Square".

johnske
05-10-2007, 02:44 AM
...On a side note, the Company I work for, uses highly motivational techniques, like "daily whippings" ...Brisbane? :dunno You need to drive north from there for a day or so to get to paradise... :devil2:

Aussiebear
05-10-2007, 03:20 AM
Firt??? Is that American for First?

Paul_Hossler
05-10-2007, 09:24 AM
Boy, you're lucky this is a boring meeting :bug:

Added VBA in 3 different places -- sort by grain type, go across, sort by bucket number

BTW, some of your data is a little messy -- "Y " -- so I had to add vba to cover that also Trim( ...) usually


'sort by Grain type
wsWhiteboard.Cells(1, 1).CurrentRegion.Sort Key1:="Grain Type", _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


For iWhiteBoard = 10 To 20 Step 2

If Trim(.Cells(1, iWhiteBoard).Value) = "Y" And Trim(.Cells(1, iWhiteBoard + 1).Value) = "" Then



'sort by Bucket Number
wsWhiteboard.Cells(1, 1).CurrentRegion.Sort Key1:="Bucket Number", _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Aussiebear
05-10-2007, 12:29 PM
G'day Paul,

When I tried your code, it still puts more than one grain type in a composite group. I am not allowed to mix grains or test types.

There should be 4 groups on the dispatch. There are two Barleys on 1st test (Buckets 10 & 11). There are ten Sorghums on 1st tests ( Buckets 1, 3, 19, 41, 65, 66, 73, 81, 116 & 130 - Which will account for two groups) and finally there is 1 Sorghum on a 250 test (Bucket 4).

The other issue is the Vnedor Dec number is missing its true length.

It needs to read (for 1st tests ) the vendor dec number plus "-01", and for 250 tons tests, vendor dec number plus "-250" and so on for each of the required tests.

Paul_Hossler
05-10-2007, 01:59 PM
Didn't understand some of the things you were looking for

I'll work in it soon

I'm thinking "6 pack of Fosters":beerchug:

(Just kidding)

Aussiebear
05-11-2007, 01:19 AM
I'm thinking "6 pack of Fosters":beerchug:

(Just kidding)

Fosters?? That's another name for sheep dip.

Bob Phillips
05-11-2007, 02:36 AM
We call it urine over here in the land of real beer!

Aussiebear
05-11-2007, 02:57 AM
Tell me Bob, which beer shares a common thread with urine....


English beer cause its body warm. So you leave our "sheep dip" alone. :devil2:

Paul_Hossler
05-11-2007, 08:01 AM
Actually, I'm sorry I brought the subject of beer up -- :p

Here's a updated version

Basically the same, added some sorts, and -01, -250's etc.

Broke the groupings by grain

Formatting of the output sheet is barebones

Been an interesting little project, let me know what you think

Paul

johnske
05-11-2007, 08:15 AM
Of course you'd know why Queenslanders named their beer XXXX (Fourex) don't you? - Cos they didn't know how to spell "beer"

Bob Phillips
05-11-2007, 08:18 AM
And I thought it was because they couldn't give a XXXX!

johnske
05-11-2007, 08:25 AM
Maybe that's right, at first I thort I was getting more patience as I grew older, but it turns out I don't give a XXXX

Aussiebear
05-11-2007, 01:41 PM
Well when you are full, who cares about spelling......

Aussiebear
05-11-2007, 01:58 PM
Here's a updated version

Basically the same, added some sorts, and -01, -250's etc.

Broke the groupings by grain

Formatting of the output sheet is barebones

Been an interesting little project, let me know what you think

Paul

Paul, its getting quite close. The sample dispatch shows 3 groups, one being for barley, one being for a 500 test, and eleven in the third group.

Of the 11 in one group, one of these is a 250 ton test, which needs to be on its own and the remaining need to be split into two groups. Can you recall the Sample dispatch form I had uploaded to the forum? There is a maximum of 5 vendors in any one group (Company rule)

Paul_Hossler
05-11-2007, 04:46 PM
So ...

Each Test (01, 250, 500, ...) is in a seperate group?
Each Grain (Corn, wheat, Fosters, ...) is in a seperate group?
Each group contains a max of 5 lines?

So corn-01 and corn-500 are in seperate groups?
So corn-01 and wheat-01 are in seperate groups?

Aussiebear
05-11-2007, 08:48 PM
They sure are.

Edit: You're a sneaky one....... No Fosters allowed!!!

Aussiebear
05-12-2007, 06:32 PM
Could it be that we aren't using the array to place the data?

Paul_Hossler
05-12-2007, 07:04 PM
:thumb Try ver 4:thumb

Paul

Aussiebear
05-12-2007, 07:18 PM
Excellant Paul, thank you for your patience.

Will the code remember the last used Comp# and Sample #?

Paul_Hossler
05-13-2007, 08:06 AM
Dispatch is stored on the hidden sheet and incremented by 1 each time for each of the output groupings, and restarts from the last used value

What would you like the Comp Num to do? Now it's just repeated each time from it's original value, also stored on the hidden sheet, but never changed. I guess would be that you'd want the CompNum to increment once per output sheets, but let me know.

Paul

Aussiebear
05-13-2007, 12:10 PM
Yes it needs to increment by 1 per use. I'll set the numbers off the last saved SD when this is working correctly.

Bob Phillips
05-15-2007, 08:28 AM
Ted,

I am not sure this works fully as there was only one grain type and one task, so the only break criteria was the five items per dispatch.

I have uploaded it to http://cjoint.com/?fprCx5Jnmz

Aussiebear
05-15-2007, 12:30 PM
Bob, Your version has the correct layout on the Sample Dispatch sheet within the the workbook, but the layout in the new workbook is not. I need to be able to save the Sample Dispatch sheet as a new wb

Bob Phillips
05-15-2007, 02:47 PM
Another http://cjoint.com/?fpxVmPyDGE

Paul_Hossler
05-15-2007, 04:28 PM
:dunno Did you get the email?

Just in case, here they are again

1. To unhide the Dispatch Template in order to reset the Comp Num and Dispatch Num, try using the 'Worksheet' side of Excel

2. >>>>>Paul, I keep getting an error message that I can't use this against a protected sheet. When I try to unprotect the sheet by using Me.Unprotect (pw), I'm then told its an inappropriate use of the Me. Can you try your code on the live workbook please?


Some VBA doesn't work on a Protected sheet, including .CurrentRegion

.Protect and .Unprotect are methods of the Worksheet object (for this purpose) so you need to Reference the correct Worksheet object, not "Me" which is used AFAIK in a Class Module or Form (sort of like a CM)

Look in the Sub_Forsters module I added to your WB:

'look for data to move
Call wsWhiteboard.Unprotect ( pw)
Set rWhiteboard = wsWhiteboard.Cells(1, 1).CurrentRegion
Call wsWhiteboard.Protect(pw)
Set rWhiteboard = rWhiteboard.Cells(2, 1).Resize(rWhiteboard.Rows.Count - 1, rWhiteboard.Columns.Count)



Let me know


BTW, it looks like the formatting I did was the easiest part of your project, what with the Forms, etc.


Let me know,


Paul

Aussiebear
05-16-2007, 03:50 AM
Thanks Paul, yes I did. I normally hold a Skype session with Bob where we talk about Excel and VBA. Last night I used your code as an example for discussion and Bob has come up with a slight alternative, which made it easier for me to understand what the code is trying to do.

In another thread, just recently a number of people suggested to me that I should consider creating a mor emodular form of coding ( creating smaller sections of code as subs etc) and this was also discussed last night.

There is one question which I need to ask of you, and it is this;

In your code you used the line Application.ScreenUpdating = False early on, yet I was unable to find the line Application.ScreenUpdating = True. Is it not necessary to do so?

Ted

Paul_Hossler
05-16-2007, 05:20 AM
The way I understand it, ScreenUpdating turns back on when the sub exits, at least the main sub.

Many things could be made more modular, but there's a trade off. I usually will break something off if 1) I'm going to re-use the code with different parameters, or 2) the main process consists of many different sub-processes and I want to control high level flow

Paul

PHH

mdmackillop
05-16-2007, 12:09 PM
In your code you used the line Application.ScreenUpdating = False early on, yet I was unable to find the line Application.ScreenUpdating = True. Is it not necessary to do so?

Ted
Good practice not to rely on defaults. If you get into bad habits you might leave EnableEvents = False or other such which don't reset to true.

Bob Phillips
05-16-2007, 12:16 PM
As I said to Ted, it is wise not to rely on the system, it may work in this release, but not the next.

Paul_Hossler
05-16-2007, 02:51 PM
True, I've gotten bad results by turning off events, and having a run time error which kicks me out before I could turn them back on. However, ScreenUpdating being turned on the system has never failed.

But, yes, it'd be a good habit to follow

Aussiebear
05-17-2007, 02:23 AM
Some of the books I've bought and read( well partially read) suggest the use of opening and closing tags, and that they should be closed on a first in last out basis. Can I therefore assume the "Good" coding always follows this principle?

Bob Phillips
05-17-2007, 02:26 AM
Not necessarily. Sometimes the code dictates that you need to do it in another order.

As with all thing (I seem to be repeating myself in my old age), it is horses for courses, the objectives should drive the code.