NateW
10-10-2007, 10:29 AM
Hi, Folks.
First post here - thanks in advance for help. I'm working on a project, after not looking at VBA for a loooong time, so please bear with me.
I'm trying to populate one worksheet with data from another.
No problem there, except that one column in the data worksheet needs to reference a third worksheet for a "more accurate" value - specifically the main data sheet contains skus from an Oracle System, and the third worksheet contains a lookup table that references both the Oracle sku, and the WMS sku, and it's the WMS sku that I need, and is only found in the third worksheet containing the lookup table.
Here is the code I'm using:
Sub PopulateGatecontrol()
Dim GateRow, ScheduleRow As Integer
Application.ScreenUpdating = False
GateRow = 4
ScheduleRow = 2
Sheets("Gate Control").Activate
On Error Resume Next
Do Until Sheets("Truck Schedule").Cells(ScheduleRow, 1).Value = ""
Cells(GateRow, 16).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 13).Value 'activity
Cells(GateRow, 1).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 7).Value 'pool Number
Cells(GateRow, 2).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 6).Value 'Order Number
Cells(GateRow, 3).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 9).Value 'Order details
Cells(GateRow, 5).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 4).Value 'ship to
Cells(GateRow, 6).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 8).Value 'arrival time
Cells(GateRow, 9).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 5).Value 'carrier
Cells(GateRow, 18) = WorksheetFunction.VLookup(Sheets("Truck Schedule").Cells(ScheduleRow, 11), _
Sheets("Tables").Range("A16:Q709"), 17, False) ' Performs the Vlookup for WMS SKU's
'Cells(GateRow, 18).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 11).Value 'SKU to be put in Utility
Cells(GateRow, 4).Value = "OUT"
Cells(GateRow, 17).Value = Int(Now)
If Sheets("Truck Schedule").Cells(ScheduleRow, 9) Like "*Demand*" Or Sheets("Truck Schedule").Cells(ScheduleRow, 9) Like "*MT*" Then
Cells(GateRow, 4).Value = "IN"
End If
GateRow = GateRow + 1
ScheduleRow = ScheduleRow + 1
Loop
Call Sort_Gate
Application.ScreenUpdating = True
End Sub
Please note, there is one line of code commented out, which only references the value from the main data worksheet, and not the accurate vlookup value from the lookup table worksheet.
The problem I'm running into is that with this code, I'm missing rows. Just to make things more complicated, not all items have an SKU - basically, the data table contains blanks for the Oracle sku, so there is no data on the lookup table to reference. Also, while there may be an Oracle sku on the main data page, there may be no matching WMS sku on the lookup table.
So, I'm sure there is something in the error handling that is going wrong. I still need the data from the other columns in the main data sheet, even if there isn't complete (or any) sku info, but the entire row is missing.
Please help - thanks sooo much!!
First post here - thanks in advance for help. I'm working on a project, after not looking at VBA for a loooong time, so please bear with me.
I'm trying to populate one worksheet with data from another.
No problem there, except that one column in the data worksheet needs to reference a third worksheet for a "more accurate" value - specifically the main data sheet contains skus from an Oracle System, and the third worksheet contains a lookup table that references both the Oracle sku, and the WMS sku, and it's the WMS sku that I need, and is only found in the third worksheet containing the lookup table.
Here is the code I'm using:
Sub PopulateGatecontrol()
Dim GateRow, ScheduleRow As Integer
Application.ScreenUpdating = False
GateRow = 4
ScheduleRow = 2
Sheets("Gate Control").Activate
On Error Resume Next
Do Until Sheets("Truck Schedule").Cells(ScheduleRow, 1).Value = ""
Cells(GateRow, 16).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 13).Value 'activity
Cells(GateRow, 1).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 7).Value 'pool Number
Cells(GateRow, 2).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 6).Value 'Order Number
Cells(GateRow, 3).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 9).Value 'Order details
Cells(GateRow, 5).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 4).Value 'ship to
Cells(GateRow, 6).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 8).Value 'arrival time
Cells(GateRow, 9).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 5).Value 'carrier
Cells(GateRow, 18) = WorksheetFunction.VLookup(Sheets("Truck Schedule").Cells(ScheduleRow, 11), _
Sheets("Tables").Range("A16:Q709"), 17, False) ' Performs the Vlookup for WMS SKU's
'Cells(GateRow, 18).Value = Sheets("Truck Schedule").Cells(ScheduleRow, 11).Value 'SKU to be put in Utility
Cells(GateRow, 4).Value = "OUT"
Cells(GateRow, 17).Value = Int(Now)
If Sheets("Truck Schedule").Cells(ScheduleRow, 9) Like "*Demand*" Or Sheets("Truck Schedule").Cells(ScheduleRow, 9) Like "*MT*" Then
Cells(GateRow, 4).Value = "IN"
End If
GateRow = GateRow + 1
ScheduleRow = ScheduleRow + 1
Loop
Call Sort_Gate
Application.ScreenUpdating = True
End Sub
Please note, there is one line of code commented out, which only references the value from the main data worksheet, and not the accurate vlookup value from the lookup table worksheet.
The problem I'm running into is that with this code, I'm missing rows. Just to make things more complicated, not all items have an SKU - basically, the data table contains blanks for the Oracle sku, so there is no data on the lookup table to reference. Also, while there may be an Oracle sku on the main data page, there may be no matching WMS sku on the lookup table.
So, I'm sure there is something in the error handling that is going wrong. I still need the data from the other columns in the main data sheet, even if there isn't complete (or any) sku info, but the entire row is missing.
Please help - thanks sooo much!!