PDA

View Full Version : Solved: Modifying the Search to include Searching in another worksheet



jason_kelly
12-01-2010, 09:11 AM
Hi There,

I need your help.

I have attached an excel document that I have been working on. I would like to modify the "Search Record" button (FillForm Function in Macros) on my userform to include searching both the "Active_Data" and "Inactive_Data" worksheets. How can the code below be modified to accomplish this?

Using Excel 2000


Private Sub FillForm()
Const ws As String = "Active_Data"
On Error Resume Next
With Sheets(ws)
Rw = .Range("C:C").Find(txt3.Value, LookIn:=xlValues, LookAt:=xlPart).Row
If Rw = 0 Then
MsgBox ("Error: File Number not found!")
txt3 = ""
txt3.SetFocus
Reset_frmIMTS
Exit Sub
End If

'fill out form if found
txt1.Value = .Range("A" & Rw).Value
txt2.Value = .Range("B" & Rw).Value
txt3.Value = .Range("C" & Rw).Value
txt4.Value = .Range("D" & Rw).Value
txt5.Value = .Range("E" & Rw).Value
txt6.Value = .Range("F" & Rw).Value
If .Range("G" & Rw).Value = "Yes" Then notify.Value = True
If .Range("H" & Rw).Value = "Yes" Then remind.Value = True
txt7.Value = .Range("I" & Rw).Value
txt8.Value = .Range("J" & Rw).Value
txt9.Value = .Range("K" & Rw).Value
txt10.Value = .Range("L" & Rw).Value
txt11.Value = .Range("M" & Rw).Value
txt12.Value = .Range("N" & Rw).Value
txt13.Value = .Range("O" & Rw).Value
End With

End Sub



Any help with this is greatly appreciated.

Thanks in advance for all your help and support.

Jay