Lookup application function in vb

[jharaldson]

I have a Table of names and data that goes along with the names in excel on one worksheet. Another worksheet has the same names on it but they change everyday. I would like to look at the name on the sheet that changes, find the name in the table and extract the data that goes with the name to an userform textbox. I can use the lookup function in excel but I cant seem to get the lookup application function to work in vb.

Thanks
Jeremy

[jharaldson]

This is my code but I get no result back in my textbox. Any help would be awesome!!



Private Sub CommandButton1_Click()
Dim res As String
Dim dname
Dim drange
Dim rrange

dname = Sheet5.range("b8").Text
Set drange = Sheet4.range("a2:a1500")
Set rrange = Sheet4.range("c1:c1500")

res = Application.WorksheetFunction.Lookup(dname, drange, rrange)

TextBox1 = res


End Sub