set cell reference using vlookup vba

[maxx_daddy]

Trying to reference a cell using vlookup in vba, this way I can offset from the cell and paste data from a userform text box.

Heres what I thought may work, but it didnt. Thoughts?


Dim acell As RangeSet acell = Application.VLookup(Val(TextBox1.Text), Sheets("thesheet").Range("a1:d42"), 1, False)</pre>

 

[njimack]

This should work for you:
Set acell = Sheets("thesheet").Range("A:A").Find(Val(TextBox1.Text))

 

[maxx_daddy]

that worked! thanks a bunch. vlookup vba cell reference, offset, text boxes,

Private Sub CommandButton1_Click()
' textbox1 is the search field, treat it like a cell for a vlookup
'textbox 2, 3, & 4 will be like vlookup results, in this case the vlookup is a work order # and the results are customer name, address
' and the amount of hours we sold the job for
TextBox2.Text = Application.VLookup(Val(TextBox1.Text), Sheets("thesheet").Range("a1:d42"), 4, False)
TextBox3.Text = Application.VLookup(Val(TextBox1.Text), Sheets("thesheet").Range("a1:d42"), 2, False)
TextBox4.Text = Application.VLookup(Val(TextBox1.Text), Sheets("thesheet").Range("a1:d42"), 3, False)
'acell is the same as the result from the initial vlookup, by setting a refernce to acell we can offset from there to paste additional
' data from a userform, in this case the name of the technician and the actual amount of time it took to complete the job.
Dim acell As Range
Set acell = Sheets("thesheet").Range("A:A").Find(Val(TextBox1.Text))
'ecell will work like any referenced textbox, i will set the refernce cell on the text box to be ecell and kill the refernce when closing.
'bam!
Dim ecell As Range
Set ecell = acell.Offset(0, 5)
ecell.Value = "do it cohagen"
End Sub

This should work for you:
Set acell = Sheets("thesheet").Range("A:A").Find(Val(TextBox1.Text))