CODEWITHSUNDEEP
vbaexcelms-accessvlookup
Tomdw94
Tomdw94

Reputation: 37

Unable to get the VLookup property of the WorksheetFunction class Error 1004

I am trying to send a query to Access to fill in a certain value of my worksheet. I would get this worksheet by getting the current user's login name and look through a column. This column gets updated according to a column in access. The value I would need is in the column to the RIGHT of the column I search through. I have the below code as a temporary try-out to get my VLookup to work

Private Sub CommandButton1_Click()
    Dim user, cUser As String
    user = (Environ$("Username"))
    cUser = Application.WorksheetFunction.VLookup(user, Worksheets("Sheet1").Range("C2:C1000"), -2, False)
    Worksheets("Sheet1").Range("F1").Value = cUser
    End Sub

I am trying to get the value that I need into cell F1. If this works, I would put the value into a variable and add this to my query. I'll also insert error handling to ensure the code keeps working, but for now I just want it to work on my 1 value that I know is in the column.

Upvotes: 1

Views: 1588

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

try this instead:

Private Sub CommandButton1_Click()
    Dim user As String, cUser As String '// Note you have to declare the type each time!
    user = Environ$("USERNAME")
    cUser = Sheets("Sheet1").Range("C:C").Find(user).Offset(, -2).Value
    Sheets("Sheet1").Range("F1").Value = cUser
End Sub

Which can be simplified again to:

Private Sub CommandButton1_Click()
    Sheets("Sheet1").Range("F1").Value = Sheets("Sheet1").Range("C:C").Find(Environ$("USERNAME")).Offset(, -2).Value
End Sub

A vlookup simply isn't suitable for what you're trying to do.

Upvotes: 1

Related Questions