[RESOLVED] Searching a column of data

[tomdooley]

I need to be able to search a column for a certain last name. When i find that name, the data associated with it, which is horizontally listed in a spreadsheet will transfer to a textbox in a userform. Anybody have any ideas on how to go about this?

[westconn1]

use the find method to set a range based on the range of the column you want to search

set rngfound = range("c:c").Find("smith") ' other arguments

[Webtest]

Here is the row you are looking for:

dim aRow as long
dim i as Integer

aRow = rngfound.Row
i = 1  'Init for Column A data if you write a loop

Now you can do a loop or a linear set of instructions copying "Cells(arow, i).Value 'for Col A" etc. into your textboxes on your form.

Does that help?

[tomdooley]

I tried placing "Cells(arow, i).Value 'for Col A" in the Textbox properties. I pasted it in the Name and Data text locations. Neither one worked properly. Where should I paste it then?

[Webtest]

I was under the impression that you are trying to write VBA code for a Macro. That's what this forum is all about ... "Visual Basic".

Cells(arow, i).Value is part of a VBA instruction that fetches the value out of a cell on the sheet. Once you have that value, you have to have code to paste it into the proper text box. You will need to know how to reference the form and textbox in code. That isn't hard, but it depends on the Form Name property and the TextBox Name property.

[westconn1]

textbox1.text = sheets("sheet1").cells(arow,1).value  'from col A
textbox2.text = sheets("sheet1").cells(arow,2).value  'form col B

[tomdooley]

Thanks guys!!! Hey could I put the same text in two seperate userforms? I tried this code but it just takes me too Userform2.

Private Sub CommandButton1_Click()
 
Dim aRow As Long
Dim i As Integer
Dim rngfound As Variant
 
 
Set rngfound = Range("B:B").Find(LName.Text) ' other arguments
 
 
 
aRow = rngfound.Row
 
 
 
FName.Text = Cells(aRow, 1).Value 'for Col A
LName.Text = Cells(aRow, 2).Value 'for Col B
 
UserForm2.Show
firstname.Text = Cells(aRow, 1).Value 'for Col A
LastName.Text = Cells(aRow, 2).Value 'for Col B
 
 
 
End Sub

[westconn1]

userform2.firstname.Text = Cells(aRow, 1).Value 'for Col A
userform2.LastName.Text = Cells(aRow, 2).Value 'for Col B
UserForm2.Show