Solved: Problem with macro containing Vlookup - error handling??

[NateW]

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!!

[lucas]

comment the:

On Error Resume Next

and see what the errors are

[YellowLabPro]

Nate,
Exactly what error are you receiving?

[NateW]

The first error I get when I comment that is:

Run Time Error 9:
Subscript Out Of Range

[NateW]

I get no errors when I run the program with that not commented - it omits a number of rows, though.

[YellowLabPro]

Nate,
That is telling your range reference is not valid. It could be a sheet name for example that is not referenced correctly.
What you will experience if you run into a record not being there is the error number 1004.
This is a very good explanation of what I am referring to;
http://www.cpearson.com/excel/CallingWorksheetFunctionsFromVBA.htm

Also, this is one I just did earlier this morning dealing w/ this very issue;

On Error Resume Next
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, find and fix"
Exit Sub

http://vbaexpress.com/forum/showthread.php?t=15474

[NateW]

Hi, Yellow...

Thanks for the help thus far! I've been banging my head against this code for three days now...new job, yadda...

I put in the code that you gave me, and the macro runs without incident....I'm going to check the integrity of the data brought across, but this might be it...thanks so much!! I will post the outcome in a few...

[NateW]

IT WORKS!! Thanks so much Yellow! After a good three days of losing my mind, it always comes down to 6 little lines of codes, doesn't it?

Thanks again...cheers!

[YellowLabPro]

Great! and you are very welcome.
This is a first for me today, I got my first solve....
The guys here have been helping me for quite some time now, it is great to give it back.

[NateW]

haha...well, I will likely be back, so I'll give you plenty of opportunities to give back.... When I took this job, they asked if I knew excel... I was like, sure, formulas, adding, subtracting, that kind of stuff...hehe... Didn't know they wanted a VBA programmer!!

Thanks again, bud! Here's to you getting your first solve, and to me getting home to my wife on time tonight! :)