PDA

View Full Version : Moving data in sequential order



Aussiebear
07-30-2007, 06:30 AM
I know this should be an Access project but its been requested to be completed as an Excel workbook, so please bear with me on this one.

I have a number of mill rolls which are identified by a stamp ( BCFL 001 to BCFL 030) which can be located in a number of different areas ( Compound, Setup, In Mills, At Shed & In Town).

Since a mill roll cannot be in more than one place at any one time, I was thinking of creating a form with the five locations (lists) showing the location of all the mill rolls at any time. The objective of the form is so that some of my worthy but highly computer challeneged workmates can simply select a mill roll and drag it to the new location. When this is done up pops a sub window which suggests a date for the transfer, which the user can then accept or place another date in.

There will be a set order for the lists, in so far as a roll must pass through a set path ( Compound to Setup to In Mills to At Shed to In Town and back to Compound).

Is it therefore possible for enforce data integrety but not allowing a mill roll to entered into more than one location, within Excel?

From the attachment ( which is only for reference purposes at this stage), you can see that of the five lists, two will have a maximum count value ( "Setup" - maximum of 2 at any one time, and "In Mills" - a maximum of 8). For a mill roll to be shifted from "Compound" to "Setup", the mill roll count for "Setup" cannot be greater than 1. Similarly moving a roll from "Setup" to "In Mills" means a mill roll count cannot be greater than 7. Is this also possible?

Ted

Aussiebear
08-01-2007, 12:36 AM
No Ideas?

Aussiebear
08-01-2007, 02:56 AM
Here's what i've got in mind. I've set up a form with 6 listboxes. (CompoundNew, SetUp, In Mills, CompoundOld, In Town and Graveyard. I've linked each listbox to their own individual range through RowSource. The reason being that by transferring a mill roll from one range to another, a user cannot mistakenly allocate a roll to two locations.

Between each list box is one or more command buttons depending on what option should be available to the user at the particular stage of the life cycle.

I've started to experiement with a Case Select type of code but now think that maybe this is not the way to go. Instead of creating original goofy command buttons, I now believe that I should be looking at the concept of having an button similar to those that allow you to add or remove a selected Item from a list that you're building. I have seen these in some types of software. Does anyone have any info about this function?

mdmackillop
08-06-2007, 01:56 PM
Hi Ted,
Just back from holiday and I see that you've been forgotten!
Here's a spreadsheet solution, unless you have a preference for a userform.
Regards
Malcolm

YellowLabPro
08-06-2007, 11:16 PM
Md-
That is slick!

Aussiebear
08-07-2007, 04:18 AM
Hi Ted,
Just back from holiday and I see that you've been forgotten!
Here's a spreadsheet solution, unless you have a preference for a userform.
Regards
Malcolm

Thank you Malcolm. As Doug has indicated its a terrific solution to the issue.

mdmackillop
08-07-2007, 05:16 AM
Hi Ted,
I didn't do anything about dates at this stage. If you need a hand incorporating these, let us know.
Regards
Malcolm

Aussiebear
08-08-2007, 03:14 AM
I'm going to expand the locations at this stage by adding a new storage location and a virtual "graveyard' for discarded rolls. I will continue to use the double click effect for moving rolls around, but will attempt a right click effect to remove rolls from active service.

After that I intend to set up a recording sheet which will record the dates when a roll was relocated, where from and where to. This will be a simple history sheet from which a pivot shhet will be used to reflect which rolls are being used, how often and then allow the determination of roll life left. This sheet will not need to be visible to the ordinary user.

mdmackillop
08-08-2007, 03:37 AM
All sounds doable, Ted. Let us know if you get stuck!

YellowLabPro
08-08-2007, 03:52 AM
Ted,
What are mill rolls and the stamp you refer to, ( BCFL 001 to BCFL 030)?
After you get it sorted out, mind posting up the workbook- it is very interesting and will be a great learning example.

Doug

Aussiebear
08-09-2007, 03:06 AM
The mill rolls that i'm referring to are 36" long by 18" diameter. Running in pairs with a set distance between them, grain passes through ( after being pre heated with steam for up to 40 minutes) and comes out looking something like rolled oats that you use to make poridge. We do this to gain the maximum energy benefit per grain, when feeding to cattle.

The Stamp is simply an identifying mark. Over the last 35 years different Workshop supervisors have applied different stamps, and any data recorded has at times referred to different Stamps but in reality it may have been the same mill roll.

18 months ago, I was asked to develop a spreadsheet to record, Mill roll data, including the life cycles of all the rolls. In some cases the data was reliable, and in others it was non existant. It seems that some Workshop supervisors didn't bother recording any data at all.

So I made a number of reccomendations to management regarding what needs to be recorded and in particular the need to implement a systematic identification of individual rolls. Management tend to mull over things, but eventually most of the recommendations have been implemented. Now they want a "simple" spreadsheet to move record rolls being moved around, and later on to develop a rather complex workbook regarding all life cycle data of the rolls for managment use only.

I suggested that we would have been better off in Access but they want it in Excel.

YellowLabPro
08-09-2007, 03:56 AM
Quite interesting Ted,
Keep us posted, I am interested to see how you solve along the way... and final solution.
Malcolm's solution is a nice one, the simplistic click-move makes for easy visual association to what is happening in the field.

Good Luck w/ this!

Aussiebear
08-11-2007, 08:36 PM
I have altered the locations to show if a roll is moved into a particular mill. Not sure whether I should have tried to code by using a Select Case within a Select Case or not.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Location As Boolean
Dim Locats, Lcn
Cancel = True

Locats = Array("Available", "Setup", "Mill1", "Mill2", "Mill3", "Mill4", "Shed", "Compound", "Town")
For Each Lcn In Locats
If Not Intersect(Target, Range(Lcn)) Is Nothing Then Exit For
Next
Select Case Lcn

Case "Available"
If Application.CountA(Range("Setup")) = 2 Then
MsgBox "Move rolls from Setup Jig first"
Exit Sub
Else
Cells(Rows.Count, "D").End(xlUp).Offset(1) = Target
End If
'Record Roll ID and date of roll movement on Sheet 1
'Data recorded should not be visible to user- only to admin


Case "Setup"
If Application.CountA(Array("Mill1", "Mill2", "Mill3", "Mill4")) = 8 Then
MsgBox "Remove rolls from Mills first"
Exit Sub
Else
' Choose which mill the rolls are transerred into
Cells(Rows.Count, "F3:F4").End(xlUp).Offset(1) = Target
If Application.CountA(Range(Mill1)) = 2 Then
Cells(Rows.Count, "F3:F4").End(xlUp).Offset(1) = Target
If Application.CountA(Range(Mill2)) = 2 Then
Cells(Rows.Count, "F6:F7").End(xlUp).Offset(1) = Target
If Application.CountA(Range(Mill3)) = 2 Then
Cells(Rows.Count, "F9:F10").End(xlUp).Offset(1) = Target
If Application.CountA(Range(Mill3)) = 2 Then
Cells(Rows.Count, "F9:F10").End(xlUp).Offset(1) = Target
End If
End If
End If
End If

Case "Mill1"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Mill2"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Mill3"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Mill4"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Shed"
Cells(Rows.Count, "J").End(xlUp).Offset(1) = Target

Case "Compound"
Cells(Rows.Count, "L").End(xlUp).Offset(1) = Target

Case "Town"
Cells(Rows.Count, "B").End(xlUp).Offset(1) = Target

End Select

Target.ClearContents
Consolidate Range(Lcn)
End Sub


Sub Consolidate(Source As Range)
Dim i As Long
For i = 1 To Source.Cells.Count - 1
If Source(i) = "" Then
Source(i) = Source(i + 1)
Source(i + 1).ClearContents
End If
Next

End Sub
Compiling this code bring up a message of Case without Select Case after the adapted section of selecting the mill. What should I have done?

mdmackillop
08-12-2007, 12:14 AM
This type of error is also shown if you are missing an EndIf statement, which you are. Add it before Case "Mill1".
And when you add your Option Explicit and debug your code (which has been done now. right Ted?), you'll see that you are missing some quotes in the CountA functions.
Since there are a few things for you to work out, here's an "assist" for the Mills allocation
Set Mills = Union(Range("G3:G4"), Range("G6:G7"), Range("G9:G10"), Range("G12:G13"))

Case "Setup"
If Application.CountA(Mills) = 8 Then
MsgBox "Remove rolls from Mills first"
Exit Sub
Else
' Choose which mill the rolls are transerred into
For Each Cel In Mills
If Cel = "" Then
Cel = Target
Target.ClearContents
Exit Sub
End If
Next
End If

Aussiebear
08-12-2007, 12:17 AM
Thanks MD

Aussiebear
08-12-2007, 12:28 AM
This does not cause any errors. Is it acceptable?

EDIT: I need a whipping..(Badly) be back shortly

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Location As Boolean
Dim Locats, Lcn
Dim Mills As Range
Dim cel As Variant
Cancel = True

Locats = Array("Available", "Setup", "Mill1", "Mill2", "Mill3", "Mill4", "Shed", "Compound", "Town")
For Each Lcn In Locats
If Not Intersect(Target, Range(Lcn)) Is Nothing Then Exit For
Next
Select Case Lcn

Case "Available"
If Application.CountA(Range("Setup")) = 2 Then
MsgBox "Move rolls from Setup Jig first"
Exit Sub
Else
Cells(Rows.Count, "D").End(xlUp).Offset(1) = Target
End If
'Record Roll ID and date of roll movement on Sheet 1
'Data recorded should not be visible to user- only to admin

Set Mills = Union(Range("G3:G4"), Range("G6:G7"), Range("G9:G10"), Range("G12:G13"))

Case "Setup"
If Application.CountA(Mills) = 8 Then
MsgBox "Remove rolls from Mills first"
Exit Sub
Else
' Choose which mill the rolls are transerred into
For Each cel In Mills
If cel = "" Then
cel = Target
Target.ClearContents
Exit Sub
End If
Next
End If

Case "Setup"
If Application.CountA(Array("Mill1", "Mill2", "Mill3", "Mill4")) = 8 Then
MsgBox "Remove rolls from Mills first"
Exit Sub
Else
' Choose which mill the rolls are transerred into
Cells(Rows.Count, "F3:F4").End(xlUp).Offset(1) = Target
If Application.CountA(Range("Mill1")) = 2 Then
Cells(Rows.Count, "F6:F7").End(xlUp).Offset(1) = Target
If Application.CountA(Range("Mill2")) = 2 Then
Cells(Rows.Count, "F9:F10").End(xlUp).Offset(1) = Target
If Application.CountA(Range("Mill3")) = 2 Then
Cells(Rows.Count, "F12:F13").End(xlUp).Offset(1) = Target
End If
End If
End If
End If

Case "Mill1"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Mill2"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Mill3"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Mill4"
Cells(Rows.Count, "H").End(xlUp).Offset(1) = Target

Case "Shed"
Cells(Rows.Count, "J").End(xlUp).Offset(1) = Target

Case "Compound"
Cells(Rows.Count, "L").End(xlUp).Offset(1) = Target

Case "Town"
Cells(Rows.Count, "B").End(xlUp).Offset(1) = Target

End Select

Target.ClearContents
Consolidate Range(Lcn)
End Sub


Sub Consolidate(Source As Range)
Dim i As Long
For i = 1 To Source.Cells.Count - 1
If Source(i) = "" Then
Source(i) = Source(i + 1)
Source(i + 1).ClearContents
End If
Next

End Sub

mdmackillop
08-12-2007, 12:33 AM
BTW, You can check more than one Select value at a time
Case "Mill1", "Mill2", "Mill3", "Mill4"
Cells(Rows.Count, "I").End(xlUp).Offset(1) = Target

mdmackillop
08-12-2007, 12:44 AM
You now have 2 Setup cases
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Location As Boolean
Dim Locats, Lcn, Mills As Range, cel As Range
Cancel = True

Locats = Array("Available", "Setup", "Mill1", "Mill2", "Mill3", "Mill4", "Shed", "Compound", "Town")
For Each Lcn In Locats
If Not Intersect(Target, Range(Lcn)) Is Nothing Then Exit For
Next

Set Mills = Union(Range("Mill1"), Range("Mill2"), Range("Mill3"), Range("Mill4"))

Select Case Lcn
Case "Available"
If Application.CountA(Range("Setup")) = 2 Then
MsgBox "Move rolls from Setup Jig first"
Exit Sub
Else
Cells(Rows.Count, "D").End(xlUp).Offset(1) = Target
End If
'Record Roll ID and date of roll movement on Sheet 1
'Data recorded should not be visible to user- only to admin


Case "Setup"
If Application.CountA(Mills) = 8 Then
MsgBox "Remove rolls from Mills first"
Exit Sub
Else
' Choose which mill the rolls are transerred into
Mills.Find("", After:=Range("G13")) = Target
Target.ClearContents
Exit Sub
End If

Case "Mill1", "Mill2", "Mill3", "Mill4"
Cells(Rows.Count, "I").End(xlUp).Offset(1) = Target

Case "Shed"
Cells(Rows.Count, "K").End(xlUp).Offset(1) = Target

Case "Compound"
Cells(Rows.Count, "M").End(xlUp).Offset(1) = Target

Case "Town"
Cells(Rows.Count, "B").End(xlUp).Offset(1) = Target

End Select

Target.ClearContents
Consolidate Range(Lcn)
End Sub

mdmackillop
08-12-2007, 01:25 AM
You can also record who moved the roll (if required) using
Environ("Username")

Aussiebear
08-12-2007, 01:47 AM
I've removed the second "Setup", which was the initial cause for the whipping call. I was in too much of a hurry.

MD, you used Union in the Set call. Why was that? Could it not have been an Array?

In using Environ ("Username"), would it be more appropriate to use it once in an "Update" function. This may mean having to write more than one line when recording the data, since any mill roll change operation consists of a pair of matched rolls being transferred from the setup jig, a pair being removed from one of the mills and the initial pair to be installed. On an odd occasion we might replace one roll (if a stub was broken).

mdmackillop
08-12-2007, 02:04 AM
From your code, it appeared you wanted to allocate the roll from Setup to the first available Mill cell. Your xlUp code is not easy to apply to four ranges in one column.
By creating Mills as a Range, I can either loop to get the first available cell, or use Find as in my edited posting.
Regarding recording, I would keep it simple. Once all is functioning, you might consider pairs.

Aussiebear
08-12-2007, 02:36 AM
Okay.

YellowLabPro
08-12-2007, 03:58 AM
Hello Ted and Malcolm,
Just catching up w/ where you guys are on this project.

I would like to ask some questions to the project if you don't mind, (learning purposes)?

Ted in post #13 you stated you had two Select Cases, I did not find but one. Then in Post #14 MD remarks that the EndIf could be the reason for the error: was the missing EndIf statement in fact the cause of the error rather than you having two Select Cases?

Post #16, Ted, you ask if your code is acceptable- by this are you looking for any methodology or syntactical items that might render unforeseen problems, immediate or long term? (I have these questions a lot when I start down a non-standard path of writing a procedure: meaning, a loop is a loop and I have seen the standard way, but when getting creative I second guess myself because I am not sure how it all works together yet).

Post#18, MD- good find. Ted, did you rename this case or eliminate this and take a different approach?

Not to muddy up Ted's thread- I am going to start a new post about Warning Messages remarked by MD here. Please see and contribute.

mdmackillop
08-12-2007, 04:53 AM
Ted in post #13 you stated you had two Select Cases, I did not find but one. Then in Post #14 MD remarks that the EndIf could be the reason for the error: was the missing EndIf statement in fact the cause of the error rather than you having two Select Cases? Ted mentioned the possibility, but used only one. The missing EndIf was the cause of the error.
Similarly this code suggests an error in the For-Next loop. BTW, Using Smart Indent and the like makes the error fairly easy to spot.
Option Explicit
Sub Test()
Dim cel As Range
For Each cel In Cells.UsedRange
If cel = "" Then
cel.Interior.ColorIndex = 6

Next
End Sub

Aussiebear
08-13-2007, 02:52 AM
Doug, I had made a few stuff ups, and MD has been kindly giving me a virtual rap over the knuckles, for being a boofhead. One of these days I'll actually get a line right.

YellowLabPro
08-13-2007, 03:49 AM
:rotlaugh:

mdmackillop
08-13-2007, 05:09 AM
Doug, I had made a few stuff ups, and MD has been kindly giving me a virtual rap over the knuckles, for being a boofhead. One of these days I'll actually get a line right.
Your "End Sub" line has always been perfect. We just need to improve the few preceding lines.:devil2:

YellowLabPro
08-13-2007, 05:57 AM
:rofl:

Aussiebear
08-13-2007, 06:49 PM
Yes that's true but unfortunately I cannot claim credit for that since its generated for me...:(

YellowLabPro
08-14-2007, 03:00 AM
:rotlaugh: