Vlookup returning reference values - but only one??

[NateW]

Hi, Folks.

I have a weird situation, hoping you can help me out. I have a vlookup that is working for the most part, but it one instance it is returning the reference.

Here is the code for the lookup:



On Error Resume Next 'Does error handling for Vlookup
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, call Nathan"
Exit Sub
End If
Sheets("Gate Control").Cells(varRow, 9).Value = _
WorksheetFunction.VLookup(Sheets("Gate Control") _
.Cells(varRow, 9).Value, Sheets("Carrier_Lookup_Table") _
.Range("C3:D1000"), 2, False) ' Performs the Vlookup for WMS SKU's
If Sheets("Gate Control").Cells(varRow, 9) = "" Then
Sheets("Gate Control").Cells(varRow, 9) = "NO CARRIER CROSSREFERENCE - UPDATE LOOKUP"
Sheets("Gate Control").Cells(varRow, 9).Interior.ColorIndex = 3
Sheets("Gate Control").Cells(varRow, 9).Font.ColorIndex = 2
End If


As I say, it returns one value from (Sheets("Gate Control").Cells(varRow, 9).Value. There is no corresponding value in the lookup table, which should return a "" value, if my understanding of the vlookup function is correct. You'll note that I have conditional handling of that situation, and there are other reference values that have no corresponding value in the lookup table that returns the "NO CARRIER CROSSREFERENCE" message.

Why would only one not do this, while the others work right? Not sure if it makes a difference, but the reference value is "Zion Transport" - not sure if the alphabetical position makes a difference or not here.

Pleae help...thanks!!!

[NateW]

By the way, I get no errors when this runs...only the weird values.

[figment]

i am not sure but it looks like your passing the wrong format to the vlookup function

try this:


Sheets("Gate Control").Cells(varRow, 9).Value = WorksheetFunction.VLookup(Sheets("Gate Control").Cells(varRow, 9).Value,"Carrier_Lookup_Table!C3:D1000", 2, False)

omit the space in "Carrier_Lookup_Table!C3: D1000" i put it there to get rid of the smily

[NateW]

Figment, nope, that didn't work at all.. All kinds of errors...

[figment]

ok i did some testing and this should work, or atleast i got a vlookup to work in my excel, using this format although i am not sure why what you had didn't work.



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 to fix - 905-696-1149"
Exit Sub
End If
Dim a, b
a = Sheets("Gate Control").Cells(varRow, 9).Value
b = Sheets("Carrier_Lookup_Table").Range("C3:D1000")
Sheets("Gate Control").Cells(varRow, 9).Value = _
WorksheetFunction.VLookup(a, b, 2, False) ' Performs the Vlookup for WMS SKU's
If Sheets("Gate Control").Cells(varRow, 9) = "" Then
Sheets("Gate Control").Cells(varRow, 9) = "NO CARRIER CROSSREFERENCE - UPDATE LOOKUP"
Sheets("Gate Control").Cells(varRow, 9).Interior.ColorIndex = 3
Sheets("Gate Control").Cells(varRow, 9).Font.ColorIndex = 2
End If

[NateW]

Hi, Figment...

Thanks for your help - this time it ran fine, with no errors, and no need to change anything, but that one value still comes up!! I swear, there's a gremlin in here somewhere!

The one that comes up is: Zion Transport

This is no where on the sheet that the vlookup uses to find values, which is why this is SO weird. Back to square one!! Any other suggestions?

[NateW]

And the vlookup DID work previously...it's just this mystery value that pops up out of nowhere....

[figment]

Not much i can do about Gremlins other then advise that you dont feed them after midnight. although if could post a sample sheet with the problem i could go through it and see if i can find the Gremlins

[NateW]

Hi, Folks.

Sorry for the multiple posts, but I can't seem to get the answer I need here by trying to keep it simple, so here's the whole issue, from the top.

What I'm specifically trying to do is create a Vlookup where I take a short form of the carrier name entered at the site, and find replace it with the proper carrier name as we use in our database. The comparison table that I am using is incomplete - there will be carriers from the site that won't be found on the lookup table.

There will also be carrier names from the site that are not entered, represented as #n/a.

For the purpose of this conversation, let's call the carrier shortform from the site "A", and the matching database carrier name "B".

What I want to have happen is when the vlookup runs, and can't find A on the lookup table, a message will be displayed in the respective cell. Also, when there is no A in the data from the site, resulting in the "#n/a", I want a different message to populate the respective cell.

Currently the #n/a message is working, but the one for the vlookup is not. What happens instead is that value A is populated in the cell instead.

I think this is because when I populate the worksheet, I am filling it first from all the site data, and then trying to use the vlookup to change that value after the fact. So, when the vlookup runs, with the error handling, it is simply not changing the cell value...

I think this is simply a matter of inserting a small bit of logic code somewhere here, I just can't figure out where/how to do it so that it shows the message i need it to.

Here is the code I'm using to populate the carrier information:



Sub subImportCarrier()
Dim x, varRow As Integer
Dim varWorkbook, varSheet As String
varWorkbook = ActiveWorkbook.Name
' fmSelectSheet.Show
'varSheet = ActiveSheet.Name
ThisWorkbook.Activate
varRow = 4
Do Until Sheets("Gate Control").Cells(varRow, 3).Value = ""
x = 2
Do Until Workbooks(varWorkbook).Sheets(1).Cells(x, 1).Value = ""
If Workbooks(varWorkbook).Sheets(1).Cells(x, 8).Value = _
Sheets("Gate Control").Cells(varRow, 3).Value Then
Sheets("Gate Control").Cells(varRow, 9).Value = _
Workbooks(varWorkbook).Sheets(1).Cells(x, 7).Value
End If
x = x + 1
Loop
varRow = varRow + 1
Loop
Call properCarrierName
End Sub


Here is the code for the vlookup. Note: I have taken out any code I've tried to use to make the vlookup return that second message, as it hasn't been working...



Sub properCarrierName()
Dim varRow As Integer
varRow = 4
Do Until Sheets("Gate Control").Cells(varRow, 3).Value = ""
'VLOOKUP FOR CONVERTING CARRIER NAME TO ORACLE CARRIER NAME
On Error Resume Next 'Does error handling for Vlookup
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, call Nathan"
Exit Sub
End If
Sheets("Gate Control").Cells(varRow, 9).Value = _
WorksheetFunction.VLookup(Sheets("Gate Control").Cells(varRow, 9) _
.Value, Sheets("Carrier_Lookup_Table") _
.Range("C3:D1000"), 2, False) ' Performs the Vlookup for WMS SKU's
If Sheets("Gate Control").Cells(varRow, 9) = "" Then
Sheets("Gate Control").Cells(varRow, 9) = "NO CARRIER INFORMATION IN DATA FILE"
Sheets("Gate Control").Cells(varRow, 9).Interior.ColorIndex = 6
Sheets("Gate Control").Cells(varRow, 9).Font.ColorIndex = 1
End If
'varRow = varRow + 1
Loop
End Sub


Note this is where the #n/a values get their message, which is working.

Please help me out - this is driving me nuts!! Also, let me know if any further information is required.

Thanks SO much in advance!!

[lucas]

Threads merged....
Nate,
starting a new thread asking the same question just because you're frustrated will only frustrate those who might try to help you....

[lucas]

A couple of ideas to help you get assistance...
post a workbook with explainations.
use linebreaks in your code so it doesn't run off the screen to the right and we have to scroll to read it....

[NateW]

Sorry, I figured the new approach warranted a new thread - the thread topic of this doesn't reflect what I've learned about the problem.

Also, sorry about the line breaks - it fits on my screen.

Thanks for any help you can offer...sorry about the ettiquette breech.

[NateW]

Next, here is the reference file (attached), as requested.

Gate Control Tab is the one I'm trying to populate, in the Planned Carriers column. All other columns are populated by other macros.

The information comes first from the outbound tab - in reality, this is part of a seperate workbook, I'm only showing this to illustrate the way the data looks on the way in. The Carrier Name column is the information being populated onto the Gate Control tab.

Once that's populated, the Vlookup puts the data from the Planned Carriers column on the Gate Control tab against the data from the Carrier_Lookup_Table tab.

As you can see, these data files are rough, but they are what I've been given to work with. The messages are important because of how brutal the data files are. The date files will get better in time, as they are updated, but it's got to run as is for now.

Let me know if I can provide any further information...thanks again for any help that can be given.

[lucas]

Please help me out - this is driving me nuts!! Also, let me know if any further information is required.

I would suggest rather than long winded explainations and descriptions which are hard to visualize that you sanitize and post a workbook as was suggested in post # 8 and post #11

[NateW]

Furthermore, if you look at the Gate Control tab, this is showing the results of running the macro. Zion Transport is the one carrier that is not on the Carrier_Lookup_Table. I want this situation to return a error message in the cell, instead of the carrier name as represented on the outbound tab.

[NateW]

Sorry, Lucas, this is hard to explain. See the files I've attached - hopefully these clear up what I'm trying to say. The reason I have been so "long winded" is that trying to simplify hasn't worked previously, which is why we are at where are now. Thanks for your continued patience.

[lucas]

Try to state in simple language ......what are you trying to accomplish.

[NateW]

Okay, let's try this again.

I want to populate the Gate Control tab with the proper carrier name from our central database, and when the proper name cannot be found, I want the cell to display a specific error message that addresses the specific problem.

There are various steps involved with this, which I can go into, but are you cool with what I've said so far?

[lucas]

You wish to populate column I of sheet Gate control with information from sheet Carrier_Lookup_Table based on information in a column in Gate Control....which column? Do I understand correctly?

[figment]

i think the problem with your properCarrierName funtion is that you, turn on On Error Resume Next and then try to do something based of an error condition.

try this:

Sub properCarrierName()
Dim varRow As Integer
varRow = 4
With Sheets("Gate Control")
Do Until .Cells(varRow, 3).Value = ""
'-VLOOKUP FOR CONVERTING CARRIER NAME TO ORACLE CARRIER NAME --
On Error Resume Next 'Does error handling for Vlookup
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, call Nathan"
Exit Sub
End If
If IsError(WorksheetFunction.VLookup(.Cells(varRow, 9) _
.Value, Sheets("Carrier_Lookup_Table").Range("C3:D1000"), 2, False)) Then
.Cells(varRow, 9) = "No DATA"
Else
.Cells(varRow, 9).Value = WorksheetFunction.VLookup(.Cells(varRow, 9) _
.Value, Sheets("Carrier_Lookup_Table") _
.Range("C3:D1000"), 2, False) ' Performs the Vlookup for WMS SKU's
With .Cells(varRow, 9)
If .Cells(varRow, 9) = "" Then
.Cells(varRow, 9) = "NO CARRIER INFORMATION IN DATA FILE"
.Interior.ColorIndex = 6
.Font.ColorIndex = 1
End If
End With
End If
varRow = varRow + 1
Loop
End With
End Sub

i dont have you documents so i could not test it

and a guess on the Zion Transport:

your trying to get the error message to wright over Carriers that are not in your list, but right now the error handling is causing it to just skip over them, ao Zion is probibly one of those not on your list. thuse the vlookup is erroring, and errorchecking is keeping it from being writen over. thuse it stays on your list.

[lucas]

Please use the line breaks guys and figment, glad you are offering help....if you use the vba button instead of the code button your code will be formatted as it is in the vbe.

ps figment....op has posted a file in post #13

[NateW]

Figment...awesome, we're getting there, thanks!!

Now it is properly catching what I need it to catch. I made a couple amendments to the code, because when I ran it the first time, it turned half the column (the bottom half for some reason) yellow. I also added some formatting to it. Here is what I have now:

(Sorry, lucas, but I have no idea where to put these line breaks - everything so far is fitting on my screen)



Sub properCarrierName2()
Dim varRow As Integer
varRow = 4
With Sheets("Gate Control")
Do Until .Cells(varRow, 3).Value = ""
'--------------------- VLOOKUP FOR CONVERTING CARRIER NAME TO ORACLE CARRIER NAME ---------------

On Error Resume Next 'Does error handling for Vlookup
If Error.Number = 1004 Then
Err.Clear
Else
MsgBox "There is an Error, call Nathan"
Exit Sub
End If
If IsError(WorksheetFunction.VLookup(.Cells(varRow, 9).Value, _
Sheets("Carrier_Lookup_Table").Range("C3:D1000"), 2, False)) _ Then
.Cells(varRow, 9) = "NO CARRIER CROSSREFERENCE IN LOOKUP TABLE"
.Cells(varRow, 9).Interior.ColorIndex = 3
.Cells(varRow, 9).Font.ColorIndex = 2
Else
.Cells(varRow, 9).Value = _
WorksheetFunction.VLookup(.Cells(varRow, 9).Value, _
Sheets("Carrier_Lookup_Table").Range("C3:D1000"), 2, False) _
' Performs the Vlookup for WMS SKU's
'With .Cells(varRow, 9)
' If .Cells(varRow, 9) = "" Then
' .Cells(varRow, 9) = "NO CARRIER INFORMATION IN DATA FILE"
' .Interior.ColorIndex = 6
' .Font.ColorIndex = 1
' End If
'End With
End If
'---------------------------------------------------------------------------------------

varRow = varRow + 1
Loop
End With
End Sub



See, I've commented out some of the stuff you gave me.

What happens now, though, is that there is no difference made between what has no corresponding value on the lookup table, and what has no value to begin with on the data source.

This isn't a deal breaker, but it would be great if I could distinguish between the two. Any suggestions?

[NateW]

There...that's gotta fit now...after editting a couple hundred times...hehe... Hope you can still read it, Figment...

[figment]

ok i think the fix for this needs to be implimented on the Carrier_Lookup_Table, rather then in the code. try adding #n/a to your lookup table. this way when the vlookup gets given #n/a there is something for it to find, in the Carrier_lookup_table. this is the easyest fix, but if you cant do this for some reason, then you need to add another if stament to your code that checks to see if .Cells(varRow,9) = "#n/a" then skips the vlookup and just puts the error message in the cell. befor continuing on.

[NateW]

Thanks, Figment, for all your help - I talked to the guy I'm doign this for, and he no longer requires a distinction be made between the two, so we're golden.

Thanks again.