ComboBox-Textbox Linked Values with VLOOKUP Function Only keep some certain range (Not all Range)

I got 1 ComboBox [Vendor_Number_CO_List] & 1 TextBox [Vendor_Name_Box_CO]. Both of them have their own Cells value in excel, "B2:C15452". I want to change the ComboBox only and the TextBox automated follows the value in pointed cells. Im using VLOOKUP Function, it is sucess but only showing TrueValue under "C9887", if it above C9887 it shows the FalseValue... Is there any limitations in Textbox/Cells Range?

Im totally new on VBA, i just started this thing yesterday.. and im quite fond of it. I want to proceed this kind of thing, so please your help regarding this issue.

I also try to shorten the range become: "B2:C9887", and it only show the FalseValue. The only differences on "C2:C9887" and "C9887:15452" is its cells value. "Number" and "Combination of word & Number".

Here's my code,

Private Sub Vendor_Number_CO_List_Change()

    Dim c, d As Variant


    c = Vendor_Number_CO_List.Value
    d = Application.VLookup(c, _
        ThisWorkbook.Sheets("Vendor Database").Range("B2:C15452"), 2, False)

    Vendor_Name_Box_CO.Value = IIf(IsError(d), "Vendor Not Found.", d)

End Sub

I want when the combobox pointed value among the "B2:B15452" it shows the "C2:C15452" value in excel. Kindly need your guys advise on this..

Solution

Congratulations! It seems that your code is working well, except for the error condition you designed. That won't work. The code below shows how to implement your idea correctly.

    Private Sub Vendor_Number_CO_List_Change()

        Dim c As Variant, d As Variant
        Dim Rng As Range

        c = Vendor_Number_CO_List.Value
        Set Rng = Sheets("Vendor Database").Range("B2:C15452")

        On Error Resume Next
        d = Application.VLookup(c, Rng, 2, False)
        If Err.Number Then d = "Vendor Not Found"
'        If Len(d) = 0 Then d = "Vendor Not Found"

        Vendor_Name_Box_CO.Value = d
    End Sub

VLOOKUP can't find partial cell values. Therefore failing to find "1234" in "ABC-1234" doesn't point to a coding error. You might try and implement the following logic.

If the criterium without prefix isn't found an error will occur.
If an error occurs, add the prefix to c and search again.
Only if this also fails proceed with "Value Not Found".

You might be more evenhanded with your naming of variables. c might not be descriptive enough and Vendor_Number_CO_List is probably too descriptive. Your code will be more readable if you avoid underscores and allcaps.