Match cell value and return cell address

[zekethewolf]

Hi
Im quite new to excel programing and im stuck at a problem:

I have 2 sheets with 1 beeing the workspace for users and the other containing data
The function im looking for, is the when a user choose a cell in column F, a userform pops up and shows the data connected to that choice. I fixed the userform, but not how the textboxes collects the data.

So the cell value of the cell activated, needs to be matched in column D on the other sheet and return the address, so my textboxes can use it to point to right data. I cant figure out what function i should use to do that and how it should be stored, so the textbox function can use it to collect the right data.

Im greatfull for all the help i can get!
Best regards Jesper

[Domski]

Have a look at the VBA Find Method. This will return a range object that you can then use.

Dom

[zekethewolf]

Hi i tryed building a find function yesterday as you suggested, but i cant seem to mamange to get it to work. Heres whats done so far :
Attached the workwook if its better!

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim strFind As String 'what to find
Dim FirstAddress As String
Dim rSearch As Range 'range to search
Set rSearch = Kompetens.Range("d3", Range("d65536").End(xlUp))

strFind = ActiveCell().Value 'what to look for

With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
With Me 'load entry to form
.TextBox2.Value = c.Offset(0, 1).Value
' .TextBox3.Value = c.Offset(0, 2).Value
'.TextBox4.Value = c.Offset(0, 3).Value
End With

End If

End With


End Sub

[Domski]

This example might help.

Dom

[zekethewolf]

This example might help.

Dom

Tnx, but im only getting "Subscript out of range" in that example.

[Domski]

Oops, changed the sheet name after writing the code.

I've amended the attached file above.

Dom

[zekethewolf]

Hmm can you look at the my workbook again, uppdated it with your code and changed all the variables to math my needs, but i cant get it to show the info in the textboxes.

[zekethewolf]

NM that last, was just me beeing stupid. Fixed it and now it works perfectly!

Tnx for all the help Domski!!