PDA

View Full Version : Solved: Structure Suggestions?



NateW
10-23-2007, 10:42 AM
Hi, Folks.

I have been given a task, and I have no idea where to begin.

What I am working on is a gate control tool. The Gate Control Sheet keeps track of various data, and is populated by another data source, an Oracle batch output. No problem so far.

The problem that I run into is that this gate control tool gets updated daily. Some items from the previous day are moved or rescheduled, but while some fields on the gate control are populated by the data file from Oracle (ie load number, source, etc.,) some fields require real time user input...time in, time out, etc. I need to ensure that data the users has been put in is still there when I do the refresh. Currently a refresh deletes all previous data.

I assume I can do a logic check to see if data is there, and there is a load number that is unique, so I'm sure I can reference it somehow, but this one is way beyond what I can even begin to plan in my head.

So, I'm looking for suggestions to get me started...the code I can figure out, I'm looking for structure here. Any help would be HUGELY appreciated.

Thanks!!

N.

NateW
10-23-2007, 10:48 AM
Furthermore, not all user fields will contain information....just to complicate things further

Bob Phillips
10-23-2007, 11:40 AM
Nate,

I think that is way too complex to absorb and understand as presented. I get the feeling it is pretty straight-forward, but I am not sure.

Can you post an example workbook with example results.

NateW
10-23-2007, 11:53 AM
I hear ya - this is a head scratched. The attached file is the gate control template. Every column that is filled in green will be poplulated by the data file from Oracle. The columns in white are the ones that the users populated in real time, i.e., as trucks come and go.

As revisions get made to the various schedules, this gate control will be repopulated based on the new information. An example of this is that load 12345 will be arriving at 5:00pm instead of 4:00pm. Note that the load number stays the same, as well as the majority of other information.

What happens now is that as these revisions are run, it is essentially taking the data file from Oracle and repopulating the whole gate control, and everything that was there before is being written over. What that means is that the user populated fields are being erased, so when the old data is repopulated from Oracle, the corresponding info is gone. It needs to be there .

To further explain, to give a better sense of time line, the revisions are being done on loads in and out that have not yet happened... So, rows corresponding to trucks that have come and gone will not change, only the ones that are still coming in the future.

I have thought of two solutions, but no good way to implement either. One is to treat the white columns with the user input as containers, referenced by their load number, and store that information temporarily when the refresh runs, matching the data back into the fields by referencing the order number. The other method would be to possibly use the date as an indicater of what to write over, and what not to. The problem is that the data file from Oracle will contain all values, past, present and future, each time it is imported.

And, if either of those methods will work, I'm not entirely sure about how the code should be written in either case.

How am I doing? Any clearer? This is brutal! :P

Bob Phillips
10-23-2007, 12:12 PM
Let me try phrasing this in my words, and you confirm if I understand or not.

- the spreadsheet will be populated (somehow) from an Oracle database
- the user can then update the truck times (manually)
- the spreadsheet will then be completely repopulated from the Oracle database, but you want to preserve that user input and repopulate it (by truck number not row number as things might have changed).

Is that it?

BTW, which colun is the truck number?

NateW
10-23-2007, 12:24 PM
Yes, that's pretty much exactly it. The key reference I will use is actually the Trip #, to use the proper tuerm, which is the first column. That is the primary key in this situation.

NateW
10-23-2007, 12:26 PM
Sorry, one more thing - it is ALL the columns in white that need to be preserved - although I don't think that makes much of a difference, if I can figure out how to preserve one, I can do the rest...but maybe it does, hence why I'm throwing this in there. :)

NateW
10-23-2007, 12:30 PM
For more information, here is the code that is currently populating the Gate Control:



Sub PopulateGatecontrol()

''Populates the Gate Control from the Schedule sheet. Could be integrated to the top part, however tight schedule means im just going to place it here
Dim x, y As Integer
Sheets("Gate Control").Range("A4:Z1000").ClearContents
x = 2
Do While Sheets("Schedule").Cells(x, 8).Value <> ""

'Sheets("Gate Control").Cells(x + 2, 1).Value = Sheets("Schedule").Cells(x, 8).Value

Sheets("Gate Control").Cells(x + 2, 1).Value = WorksheetFunction.VLookup(Sheets("Schedule").Cells(x, 8).Value, _
Sheets("EDM_LOADS").Range("A2:H1000"), 8, False) ' Performs the Vlookup for WMS SKU's

On Error Resume Next 'Does error handling for Vlookup
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, call Nathan Wendel"
Exit Sub
End If


Sheets("Gate Control").Cells(x + 2, 2).Value = Sheets("Schedule").Cells(x, 9).Value
Sheets("Gate Control").Cells(x + 2, 3).Value = Sheets("Schedule").Cells(x, 11).Value
Sheets("Gate Control").Cells(x + 2, 4).Value = "OUT"
Sheets("Gate Control").Cells(x + 2, 5).Value = Sheets("Schedule").Cells(x, 4).Value
Sheets("Gate Control").Cells(x + 2, 6).Value = Sheets("Schedule").Cells(x, 10).Value
If Sheets("Schedule").Cells(x, 5).Value = "" Then
Sheets("Gate Control").Cells(x + 2, 9).Value = "Unassigned"
Else
Sheets("Gate Control").Cells(x + 2, 9).Value = Sheets("Schedule").Cells(x, 5).Value
End If
Sheets("Gate Control").Cells(x + 2, 17).Value = Sheets("Schedule").Cells(x, 10)
Sheets("Gate Control").Cells(x + 2, 17).NumberFormat = "m/d/yy" ' populates Shipment Date


x = x + 1
Loop

Bob Phillips
10-23-2007, 12:44 PM
I am a bit confused by that code, I thought it came from a database and I din't see a sheet EDM_LOADS, but here is what I was going to suggest.

Have two macros, one that simply copies the current sheet before re-populating. The other would just go through the newly populated worksheet, and lookup the Trip # in the saved sheet, and copy over the selected columns where a match is found. Then delete the saved sheet.

Sound feasible? Can you undertake that or do you need help?

NateW
10-23-2007, 12:58 PM
Yeah, I know the code is a little confusing - there are 14 other tabs on this workbook that handle various tasks, for the sake of space I just put the main one that I was talking about. The data from the database gets put on one of those pages (EDM_LOADS), and then gets combined with some other information, the final result ending up the Gate Control page I have attached.

But, I think your solution will work great. I think I can handle the implementation, I'm just wondering what the best method would be to do it - the first thing that came to mind is create a new worksheet for the purpose of copying, do a vlookup to between the trip number and the previous user-enterred data to bring the data across, then delete the worksheet that was created to hold the old data.

Does that make sense?

Bob Phillips
10-23-2007, 01:15 PM
Yeah I think you are just re-iterating what I said, so I will agree.

I would do a VLOOKUP though. You only want the row number so do



With Worksheets("Gate Control")
LastRow = .cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To LastRow
RowNum =0
On Error resume Next
RowNum = Application.Match(.Cells(i, "A").Value, Worksheets("Data Copy").Columns(1),0)
On Error Goto 0
If RowNum <> 0 Then
.Cells(i, "G").Value = Worksheets("Data Copy").Cells(RowNum, "G").Value
'etc for other white columns
End If
Next i
Application.DisplayAlerts = False
Worksheets("Data Copy").Delete
Application.DisplaAlerts = True
End With

NateW
10-23-2007, 01:20 PM
Yeah, that's kind of what I was thinking - thanks. Do I have to do a seperate vlookup for each column I want to bring across, or can I do it all in one statement?

What I'd want would be columns 7, 8, 10, 11, 12, 13, 14, and 15, all of which would be lookup by trip number in column 1.

Also, just to save some research time, can you please tell me the statement to create and delete the temporary worksheet that will be created? I figure I'd create the worksheet at the beginning of the loop, then copy then original information there, then run the loop containing the vlookup, then delete the worksheet with the old data...

Bob Phillips
10-23-2007, 01:34 PM
No, by using the Match as I showed, you will have the row number, then just use the Cells property with each column letter (I gave one example in the code above, but just add further statements for other columns).

To create a worksheet



Worksheets.Add.Name = "Data Copy"


I showed hoew to delete it in the previous post.

NateW
10-23-2007, 01:38 PM
Thanks for your help and patience. I will try to run with this - I think it makes sense now...I just couldn't see the forest for the trees. I will put this as solved for now, though I know I'll probably be back. Thanks again!