none
Setiing the vloookup for combobox (show cell from another variable colume in database) RRS feed

  • Question

  • Hi,

    i am trying to fill a certain cell in excel worksheet with data from worksheet "database"  with following code:

    nvdsheet.Cells(3, 7) = Application.VLookup((Me.cbmnaslovnvd), Range(Cells(2, 8), Cells(500000, 8)), 2, 0)

    It returns in the righ cell #N/A but i can´t figure out the mistake. I make a variable colume range for combobox and it works fine and variable colume range for cell lookup. I am trying to do the similar thing made in this video with exception copying result to .xsl workbook cell (not in label).

    "https://www.youtube.com/watch?v=KJJcruEG1C0"

    Please help me. Thanks,

    Wednesday, January 20, 2016 8:57 PM

All replies

  • You have a number of errors. I will try to cover all.

    In the Vlookup the Table array Range(Cells(2, 8), Cells(500000, 8)) is a single column.

    The column index number is 2 which should indicate the 2nd column of the table array and it can't find the second column because you have only assigned one column to the table array. The Vlookup function looks up on the first column of the table array and then returns the value from the column indicated by the column index number. (If only one column is used for the table array then the column index must be 1.)

    With good programming all ranges should include the worksheet reference. This applies to the Table Array which should contain the worksheet reference otherwise it is referring to the ActiveSheet and the range might not be on the ActiveSheet; particularly when you are using Userforms. The best way to ensure that the table array contains the worksheet reference is to assign the range to a range variable and that way the range variable contains the reference to the worksheet (See example code for how to do this.)

    Values in controls are usually strings and if the string is required in any other format then it needs to be converted to another format. For example if the control contains a number then that number is in string (or text) format and therefore it needs to be converted to a number or it will not match a real number on the worksheet.

    For converting values to another format look up "Type conversion functions" in Help.

    Explanation of the following example code.

    Code runs when CommandButton1 is clicked.

    Dimensions variables used in the code with Dim statements.

    Worksheet "Sheet2" is assigned to the worksheet variable.

    Range for the table array for the Vlookup is assigned to a range variable and it is done including the Worksheet name. Note that it has a minimum of 2 columns because the column index number is 2 which returns the column adjacent to the first column when the lookup is found.

    The MsgBox is included simply so you can see that the range variable contains the Workbook name, Sheet name and the Range so wherever the range variable is used after the range is assigned to it, it fully references the workbook, worksheet and range.

    The Vlookup function uses the CLng conversion function to convert the text number in the control to a long number. (I don't know if you need to do this and if you are looking up text then it is not required but it is included for reference and you can refer to Help as described above for more information on conversions.)

    The range variable is used for the table array in the Vlookup.

    Hope all this helps you to understand.

    Private Sub CommandButton1_Click()
        Dim nvdsheet As Worksheet
        Dim rngTblArray As Range
       
        Set nvdsheet = Worksheets("Sheet2")
       
        With Worksheets("Sheet1")
            'Note when using With statement that .Range and .Cells start with a dot
            Set rngTblArray = .Range(.Cells(2, 8), .Cells(500000, 9))   'Two columns
        End With
       
        MsgBox rngTblArray.Address(External:=True)  'This line for demo purposes only

        nvdsheet.Cells(3, 7) = Application.VLookup(CLng(Me.cbmnaslovnvd.Value), rngTblArray, 2, 0)

    End Sub


    Regards, OssieMac

    Thursday, January 21, 2016 11:09 AM
  • Hi,

    thank you for your answer and your explaination. I arrange function for my program but 1 more problem exists:

    My code now:

     Dim rngTblArray As Range

        Set nvdsheet = Worksheets("iNVD")

        With Worksheets("database")
            'Note when using With statement that .Range and .Cells start with a dot
            Set rngTblArray = .Range(.Cells(2, 8), .Cells(500000, 8))   'One column
        End With

        MsgBox rngTblArray.Address(External:=True)  'This line for demo purposes only

        nvdsheet.Cells(3, 7) = Application.VLookup(CLng(Me.cbmnaslovnvd.Value), rngTblArray, 2, 0)

    Me.vbmnaslovnvd.Value, return "Ulica Jakca 1" for example but

    CLng(Me.cbmnaslovnvd.Value) = <type mismatch> whene I use buttonclick.

    The cell in excel "Ulica Jakca 1" is text format cell in datasheet.


    Thursday, January 21, 2016 3:50 PM