VBA Code to Find and Display Records

**Kris12** · 02-01-2010, 12:15 PM

I am very new to VBA. I have created a userform in excel. The idea is the excel worksheet will be prepopulated with data. upon opening excel, the user is prompted to enter the account number, via input box. I have all of this so far. My problem is I have no idea how to once the account number is inputed to search the spreadsheet for the account number and have it populate my userform so it may be updated. Please any help would be so very much appreciated. I have attached file I am working on for your assistance.

**pike** · 02-02-2010, 03:09 AM

Hi Kris12

Try something like....

Sub Rectangle1_Click()
Dim searchstring, LookInR, FoundOne, fAddress
searchstring = Application.inputbox("Enter Account Number", "Search", , 100, 100, , , 2)
Set LookInR = Sheets("VA2VA Form").Range("O2:O200")

With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
           Sheets("VA2VA Form").Range(fAddress).EntireRow.Copy Destination:=Sheets("Client Data Entry").Range("A1")
              Loop While FoundOne.Address <> fAddress
        Else
        MsgBox "not found"
        End If

End With
Set FoundOne = Nothing
End Sub

**Kris12** · 02-02-2010, 09:34 AM

Hi Pike -
you are amazing! it works where i can search and pull up the data to the spreadsheet. I am going to continue to work at having the userform be the item that is pulled up and displayed!!! Thank you so much, as this will help !

**hitesh_asrani_j** · 04-27-2011, 11:58 AM

Originally Posted by pike

Hi Kris12

Try something like....

Sub Rectangle1_Click()
Dim searchstring, LookInR, FoundOne, fAddress
searchstring = Application.inputbox("Enter Account Number", "Search", , 100, 100, , , 2)
Set LookInR = Sheets("VA2VA Form").Range("O2:O200")

With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
           Sheets("VA2VA Form").Range(fAddress).EntireRow.Copy Destination:=Sheets("Client Data Entry").Range("A1")
              Loop While FoundOne.Address <> fAddress
        Else
        MsgBox "not found"
        End If

End With
Set FoundOne = Nothing
End Sub

Hello,
I used the code But I am unable to pull more than one Record ( actually want to pull all the duplicate records as well)

**skhalaili** · 12-17-2010, 12:04 PM

I am new to VBA. I have created a userform in excel, and would like to add a find code using the vendor #, so when I press find and put the vendor # if I have that vendor I want his data to appeare in that form so I can update it and if not a message appeare says not found. I have found a similar case here but when I tryed it It didn't work.
Also I want to write a code for the vendor # text and for the Vendor name to prevent any duplicate while entering data.If any one can help it would be so much appreciated. I have attached the file I am working on for your assistance.