PDA

View Full Version : Search for Record in Userform



LOSS1574
10-31-2008, 07:14 AM
I'm using a search button in my userform to find in a worksheet by account name. However, if there muliple accounts with the same name the data will not pull correclty.

Is there a way I can modify the below code so the search button will pull by the account name and matching file number associated with the account name. I assigned each account name with a unique file ID number in a separate column. The properties name for the unique file ID num is filenum. Thanks


Private Sub cmdbranch_Click()
Dim rn As Range
If combranch <> "" Then
'ClearData
Set rn = Sheet1.Range("B2 ", Sheet1.Range("B2").End(xlDown)).Find(combranch.Value, _
, , xlWhole, , xlNext)
If Not rn Is Nothing Then
DisableSave
Filenum.Value = rn.Offset(0, -1)

Actname.Value = combranch.Value
daterec.Value = rn.Offset(0, 1)
dateneed.Value = rn.Offset(0, 2)
datecomp.Value = rn.Offset(0, 3)
status1.Value = rn.Offset(0, 4)
vendor1.Value = rn.Offset(0, 5)
Actype1.Value = rn.Offset(0, 6)
Dept1.Value = rn.Offset(0, 7)
uw1.Value = rn.Offset(0, 8)

C.Value = rn.Offset(0, 9)
Q.Value = rn.Offset(0, 10)
EM.Value = rn.Offset(0, 11)
MR.Value = rn.Offset(0, 12)
MRC.Value = rn.Offset(0, 13)
EX.Value = rn.Offset(0, 14)
SP.Value = rn.Offset(0, 15)
RM.Value = rn.Offset(0, 16)
L.Value = rn.Offset(0, 17)
N.Value = rn.Offset(0, 18)
D.Value = rn.Offset(0, 19)
E.Value = rn.Offset(0, 20)
P.Value = rn.Offset(0, 21)
B.Value = rn.Offset(0, 22)
G.Value = rn.Offset(0, 23)
I.Value = rn.Offset(0, 24)
CR.Value = rn.Offset(0, 25)
S.Value = rn.Offset(0, 26)
CL.Value = rn.Offset(0, 27)
srvtype.Value = rn.Offset(0, 28)
Request1.Value = rn.Offset(0, 29)
otherdes.Value = rn.Offset(0, 30)
ActDes.Value = rn.Offset(0, 31)
subnum.Value = rn.Offset(0, 32)
polnum.Value = rn.Offset(0, 33)
UserForm1.rownumber.Value = Filenum.Value + 1

Else
MsgBox "Account Name not found. Please Try Again."
End If
Else
combranch.Text = "Please Enter Account Name."
End If
Set rn = Nothing
End Sub

Kenneth Hobs
10-31-2008, 08:42 AM
You can use this method to return an array of found address strings. You can then iterate the array as I did using a For loop to check an offset match.
Sub test()
Dim sRange As String, a() As String, rc As Boolean
Dim i As Integer
sRange = Sheets("Sheet1").Range("A5", Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)).Address
rc = FindAll("5", Sheets("Sheet1"), sRange, a())
If rc Then
For i = 1 To UBound(a)
MsgBox a(i), , i
Next i
Else: MsgBox "5 Not Found"
End If
End Sub


'http://vbaexpress.com/kb/getarticle.php?kb_id=975
Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean

' --------------------------------------------------------------------------------------------------------------
' FindAll - To find all instances of the1 given string and return the row numbers.
' If there are not any matches the function will return false
' --------------------------------------------------------------------------------------------------------------

On Error GoTo Err_Trap

Dim rFnd As Range ' Range Object
Dim iArr As Integer ' Counter for Array
Dim rFirstAddress ' Address of the First Find

' -----------------
' Clear the Array
' -----------------
Erase arMatches
Set rFnd = oSht.Range(sRange).Find(What:=sText, LookIn:=xlValues, LookAt:=xlPart)
If Not rFnd Is Nothing Then
rFirstAddress = rFnd.Address
Do Until rFnd Is Nothing
iArr = iArr + 1
ReDim Preserve arMatches(iArr)
arMatches(iArr) = rFnd.Address ' rFnd.Row ' Store the Row where the text is found
Set rFnd = oSht.Range(sRange).FindNext(rFnd)
If rFnd.Address = rFirstAddress Then Exit Do ' Do not allow wrapped search
Loop
FindAll = True
Else
' ----------------------
' No Value is Found
' ----------------------
FindAll = False
End If


' -----------------------
' Error Handling
' -----------------------
Err_Trap:
If Err <> 0 Then
MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
Err.Clear
FindAll = False
Exit Function
End If
End Function

LOSS1574
10-31-2008, 01:01 PM
Thank you for your assistance.

I'm not sure how to apply your code to the code I posted. I tried replacing my code with yours, in the search command button. However, message boxs ($A$2) popped up for each record in the row found but did not pull the record.
Please advise.

Regards,

Kenneth Hobs
10-31-2008, 01:19 PM
Replace the Sheet1, A5 and column "A" for your search range and "5" for your name to search. Replace the 123 for your ID value for the second match.

Sub test()
Dim sRange As String, a() As String, rc As Boolean
Dim i As Integer
Dim rn as Range
sRange = Sheets("Sheet1").Range("A5", Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)).Address
rc = FindAll("5", Sheets("Sheet1"), sRange, a())
If rc Then
For i = 1 To UBound(a)
Set rn=Sheets("Sheet1").Range(a(i))
If rn.Offset(0, -1).value=123 then
'do your thing using rn
End If
Next i
Else: MsgBox "Name Not Found"
End If
End Sub

LOSS1574
10-31-2008, 01:55 PM
I'm having trouble with runtime errors

sRange = Sheets("worksheet").Range("B2 ", Sheets("worksheet").Cells(Rows.Count, "B2 ").End(xlUp)).Address
rc = FindAll(combranch.Value, Sheets("worksheet"), sRange, a())

Aussiebear
10-31-2008, 03:41 PM
What happens if you remove the space after "B2 " to simply "B2"?

Kenneth Hobs
10-31-2008, 06:40 PM
Your 2nd B2 in Cells, should just be "B". Cells can be Cells(1,2) or Cells(1,"B") but not Cells(1,"B2").

I use this method to come up from the bottom. Your other code goes down from the first cell to the first empty cell which may not get all of the range if you have blank data.

If you still have trouble, attach the xls if you like.

LOSS1574
11-14-2008, 08:33 AM
Attached please find my worksheet as I'm still having some difficulty. Any assistance is greatly appreciated

Currently my search button on the user form will pull data associated with the name selected. However, if multiple accounts with the same name exist, only the data from the First duplicate name will pull and not the correct duplicate name selected. To try and rectify the issue I assigned Id numbers to each account name and tried to modify the code to pull the data from the account name and associated ID number. But it hasn't worked. :)

Thanks

Kenneth Hobs
11-14-2008, 09:16 AM
Please detail what to enter to get the incorrect result and what the correct result would be.

LOSS1574
11-14-2008, 02:02 PM
On my worksheet I have 3 account names listed in different rows with the same name (column A). Each account has a unique ID number (Column B).

The userform search combo box lists all the account names & Id numbers included in my worksheet. Lets say I highlight/select Emerald Beach Resort #18 in the combo box and click on the search command button. The incorrect Emerald Beach Resort #17 data is pulled into the form and not the data for the selected Emerald Beach Resort #18.

For some reason if there is duplicate account names the first account data is pulled into the form and not the correct duplicated name & Id number that was choosen. Does this help?

Kenneth Hobs
11-14-2008, 10:47 PM
There are two ways to get the value. One is by using the ListIndex for the combobox item that was selected. The easier method is to use BoundColumn. By default, the BoundColumn is 1 even though you show more than 1.

Doubleclick your search button and replace your code down to DisableSave. I put snip meaning to use your code to the last line before End Sub where I set the BoundColumn back to 1.

Private Sub cmdbranch_Click()
Dim rn As Range

If combranch <> "" Then
'ClearData
combranch.BoundColumn = 2

Set rn = Sheet1.Range("B2", Sheet1.Range("B2").End(xlDown)).Find(combranch.Value, _
, , xlWhole, , xlNext)
If Not rn Is Nothing Then
Set rn = rn.Offset(0, -1)
DisableSave
'snip
combranch.BoundColumn =1
End Sub
Notice how I offset rn. It actually searched for the 2nd column value of the selected combobox item and returned that cell. I also reset the range to Find since that is what you are really looking for.