PDA

View Full Version : Search another spreadsheet to populate userform



hypuk
04-04-2012, 06:45 AM
Hi there

I was wondering how I'd be able to search another spreadsheet which has a account number, first name and surname in there, by entering a account number on my excel userform and clicking a search button on the form to return matched data and populate it in to my first name and surname field.

Below is a link to a screenshot of my userform

http://imageshack.us/photo/my-images/851/formg.gif/ (http://imageshack.us/photo/my-images/851/formg.gif/)

Any help would be muched appreciated.

Mark

CatDaddy
04-05-2012, 12:59 PM
vlookup

jolivanes
04-05-2012, 04:16 PM
Or:

Assuming Textboxes for Account #, First Name and Surname on UserForm.
Code goes under the Search Button.



Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet2") '<---- Change to Sheet Name where info is.
ac = TextBox1
With wsSheet
Set rFound = .Columns(1).Find(What:=ac, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
fn = rFound.Offset(, 1)
sn = rFound.Offset(, 2)
TextBox2 = fn
TextBox3 = sn
End With

hypuk
04-10-2012, 01:58 AM
Thanks for your help that worked like a charm :)