none
VBA VLookup Error RRS feed

  • Question

  • I wrote the code below to return the value found in column in the CostingDB spreadsheet based on VLookup using a value selected from a combobox:

    Private Sub cobSupInv_Change()

    Dim SupInv As String

    SupInv = Me.cobSupInv.Value

    Me.txtPONum.Value = Application.VLookup(SupInv, Sheets("CostingDB").Range("A2:N1087").Value, 2, 0)

    End Sub

    It is generating an error that states "Could not set the Value property. Type mismatch". Please advise on how to resolve this error as the values in the excel spreadsheet where the VLookup is reading from are strings

    Wednesday, May 23, 2018 8:35 AM

All replies

  • Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:
    https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

    Andreas.

    Wednesday, May 23, 2018 2:15 PM
  • Hi Andrea

    Thanks for your response, it turns out the "A" column had no data that is why it was causing an error. I have resolved by changing the line of code below:

    From: Me.txtPONum.Value = Application.VLookup(SupInv, Sheets("CostingDB").Range("A2:N1087").Value, 2, 0)

    To: Me.txtPONum.Value = Application.VLookup(SupInv, Sheets("CostingDB").Range("B2:N1087").Value, 2, 0)

    Wednesday, May 23, 2018 2:49 PM