Software

Ask something
Question Locked

vlookup in vba

0 helpful
3 replies

by simon.c.l

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am struggling to see where I am going wrong and now pulling my hair out. Why does this not work? I have a userform with 2 txtboxes. I want txtbox2 (namedtxtOrg) to display results from value entered in txtbox1 (NamedtxtVPD). In txtVPD I code on exit: txtOrg value=application.worksheetfunction.vlookup (txtVPD,sheet2(A1,[C1100]),2,False).

I have tried many variants of this and got many errors. I am doing something wrong. Please, please any ideas would be grand.

3 replies

by Former member

Rather than clog your workbook with VLOOKUP functions, you can perform the same task through vba. Unlike the VLOOKUP function, there is no need to have the data in ascending order.

This example looks for the value cell $A$1 from ?Sculpt? worksheet, returning the corresponding value in the 2nd column of a range in the ?Range? worksheet. The value is returned in the form of function and can be retrieved from other subs.

_____________________________

Public Function Vlookup() As Long

Dim x As Integer
Dim strLookup As String
x = 1
strLookup = Worksheets(?Sculpt?).Range(?$A$1:$A$1″)
Do Until Worksheets(?Range?).Cells(x, 1).Value = ??
If Worksheets(?Range?).Cells(x, 1).Value = strLookup Then
Vlookup = Worksheets(?Range?).Cells(x, 2).Value
Exit Do ?if value is found, the code stops searching
End If
x = x + 1
Loop

End Function
......................................

Please post back if you have any more problems or questions.
If this information is useful, please mark as helpful. Thanks.

by simon.c.l

...but as a relative newcomer to the world of vba I am unsure what to do with the example you have given. I don't know where to place a public function in the in the heirachy, nor how to make it work. I can vaguely see how to adjust the details to refer to a range in my worksheet but that is about it. Sorry to sound stupid but some of it goes right over my head!

This discussion is closed to new replies.