I am looking to update a database record using a userform. I have set up a userform with and ID number at the top and below are list of boxes that match the rest of the database. When the ID number is selected I would like all the other boxes in the userform to be filled e.g. using a vlookup. Below is the current formula but isn't working as all of the lookup functions are in red
Dim SLNo As Integer
SLNo = cmbdid.Value
Me.TextSendCode.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“EditForm”).Range(“B10:K13”), 2, 0)
Me.TextReceiveCode.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“EditForm”).Range(“B10:K13”), 3, 0)
Me.ComboEntity.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“EditForm”).Range(“B10:K13”), 4, 0)
Me.TextAccNumber.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“EditForm”).Range(“B10:K3”), 5, 0)
Error states - "Expected: list separator or )" - this is on the colon between B3:K13
Any help to make work?
Welcome to SO. Your double quotes are not the right ones. You are using ” and they should be ". Actually, the ANSI number of whatever you are using as double quotes is 148 and you should use the ones with number 34.
Note the differente:
Yours: (“B10:K13”) VS the right one ("B10:K13")
Replace your double quotes with " in all cases.
Actually, this should avoid the error you are getting:
Dim SLNo As Integer
SLNo = cmbdid.Value
Me.TextSendCode.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("EditForm").Range("B10:K13"), 2, 0)
Me.TextReceiveCode.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("EditForm").Range("B10:K13"), 3, 0)
Me.ComboEntity.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("EditForm").Range("B10:K13"), 4, 0)
Me.TextAccNumber.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("EditForm").Range("B10:K3"), 5, 0)
UPDATE: Last line of your code is:
Me.TextAccNumber.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("EditForm").Range("B10:K3"), 5, 0)
Notice you have typed Range("B10:K3") with a 3 instead of 13 . I guess it should be Range("B10:K13")