All...

I'm running into a couple of issues when attempting to run the code below
and would appreciate any assistance in what I'm doing incorrectly... First
is an Overflow issue when attempting to set a LookupID variable, secondly,
I'm not sure whether the RowIndex variable is structured properly...

Here is what I'm attempting to do:
I have a 10 digit numeric value that is stored in Excel using a form; I now
want to use the same 10 digit numeric field to allow the User to find the
specific Row of data and display the corresponding fields to a new form...

Here is the code that I'm attempting to run:
Private Sub ReferenceID_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim LastRow As Long
Dim LookupID As Long
Dim RowIndex As Long

LastRow = (Selection.CurrentRegion.Rows.Count)

If IsNumeric(Me.ReferenceID.Text) Then
LookupID = (CLng(Me.ReferenceID.Text))
Else
Me.ReferenceID.Text = ""
MsgBox "Reference ID number must be numeric."
Cancel = True
End If

With Worksheets("Raw Data").Range("C1:C" & LastRow)
Set RowIndex = (Selection.Find(What:=LookupID, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row)
End With

Department.Text = Cells(RowIndex, 1)
Department.Text = Cells(RowIndex, 2)
Department.Text = Cells(RowIndex, 3)
Department.Text = Cells(RowIndex, 4)
Department.Text = Cells(RowIndex, 5)
Department.Text = Cells(RowIndex, 6)
Department.Text = Cells(RowIndex, 7)
Department.Text = Cells(RowIndex, 8)
End Sub


--

Joe Mac

Have a play with this code & see if it does what you want.
Place behind the form you are using to enter search criteria - I assume that
you already have another userform to place & display the found data?

Private Sub CommandButton1_Click()
Dim Foundcell As Range
Dim Search As String

Search = Me.ReferenceID.Text

With ThisWorkbook.Worksheets("Raw Data")
'search Col C
Set Foundcell = .Columns(3).Find(Search, LookIn:=xlValues,
LookAt:=xlWhole)

If Foundcell Is Nothing = False Then

With UserForm1 '<< change name as required

'gather data from Col A to Col H
'& place in 8 textboxes named
'TextBox1, Textbox2 etc etc on userform
For na = 1 To 8

.Controls("TextBox" & na).Text = _
Foundcell.Offset(0, na - 3).Value

Next na

.Show

End With

Else

'inform user no match found
msg = MsgBox("Reference ID: " & Search & Chr(10) & _
"Not Found", 16, "Search ID")

With Me.ReferenceID

.Text = ""
.SetFocus

End With

End If

End With
End Sub

--
jb


"Joe Mac" wrote:

All...

I'm running into a couple of issues when attempting to run the code below
and would appreciate any assistance in what I'm doing incorrectly... First
is an Overflow issue when attempting to set a LookupID variable, secondly,
I'm not sure whether the RowIndex variable is structured properly...

Here is what I'm attempting to do:
I have a 10 digit numeric value that is stored in Excel using a form; I now
want to use the same 10 digit numeric field to allow the User to find the
specific Row of data and display the corresponding fields to a new form...

Here is the code that I'm attempting to run:
Private Sub ReferenceID_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim LastRow As Long
Dim LookupID As Long
Dim RowIndex As Long

LastRow = (Selection.CurrentRegion.Rows.Count)

If IsNumeric(Me.ReferenceID.Text) Then
LookupID = (CLng(Me.ReferenceID.Text))
Else
Me.ReferenceID.Text = ""
MsgBox "Reference ID number must be numeric."
Cancel = True
End If

With Worksheets("Raw Data").Range("C1:C" & LastRow)
Set RowIndex = (Selection.Find(What:=LookupID, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row)
End With

Department.Text = Cells(RowIndex, 1)
Department.Text = Cells(RowIndex, 2)
Department.Text = Cells(RowIndex, 3)
Department.Text = Cells(RowIndex, 4)
Department.Text = Cells(RowIndex, 5)
Department.Text = Cells(RowIndex, 6)
Department.Text = Cells(RowIndex, 7)
Department.Text = Cells(RowIndex, 8)
End Sub


--

Joe Mac

John...

Thank you for the reply... I've worked through your model as well as my own
and have remedied the issue... thanks you for the time
--
Joe Mac


"john" wrote:

Have a play with this code & see if it does what you want.
Place behind the form you are using to enter search criteria - I assume that
you already have another userform to place & display the found data?

Private Sub CommandButton1_Click()
Dim Foundcell As Range
Dim Search As String

Search = Me.ReferenceID.Text

With ThisWorkbook.Worksheets("Raw Data")
'search Col C
Set Foundcell = .Columns(3).Find(Search, LookIn:=xlValues,
LookAt:=xlWhole)

If Foundcell Is Nothing = False Then

With UserForm1 '<< change name as required

'gather data from Col A to Col H
'& place in 8 textboxes named
'TextBox1, Textbox2 etc etc on userform
For na = 1 To 8

.Controls("TextBox" & na).Text = _
Foundcell.Offset(0, na - 3).Value

Next na

.Show

End With

Else

'inform user no match found
msg = MsgBox("Reference ID: " & Search & Chr(10) & _
"Not Found", 16, "Search ID")

With Me.ReferenceID

.Text = ""
.SetFocus

End With

End If

End With
End Sub

--
jb


"Joe Mac" wrote:

All...

I'm running into a couple of issues when attempting to run the code below
and would appreciate any assistance in what I'm doing incorrectly... First
is an Overflow issue when attempting to set a LookupID variable, secondly,
I'm not sure whether the RowIndex variable is structured properly...

Here is what I'm attempting to do:
I have a 10 digit numeric value that is stored in Excel using a form; I now
want to use the same 10 digit numeric field to allow the User to find the
specific Row of data and display the corresponding fields to a new form...

Here is the code that I'm attempting to run:
Private Sub ReferenceID_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim LastRow As Long
Dim LookupID As Long
Dim RowIndex As Long

LastRow = (Selection.CurrentRegion.Rows.Count)

If IsNumeric(Me.ReferenceID.Text) Then
LookupID = (CLng(Me.ReferenceID.Text))
Else
Me.ReferenceID.Text = ""
MsgBox "Reference ID number must be numeric."
Cancel = True
End If

With Worksheets("Raw Data").Range("C1:C" & LastRow)
Set RowIndex = (Selection.Find(What:=LookupID, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row)
End With

Department.Text = Cells(RowIndex, 1)
Department.Text = Cells(RowIndex, 2)
Department.Text = Cells(RowIndex, 3)
Department.Text = Cells(RowIndex, 4)
Department.Text = Cells(RowIndex, 5)
Department.Text = Cells(RowIndex, 6)
Department.Text = Cells(RowIndex, 7)
Department.Text = Cells(RowIndex, 8)
End Sub


--

Joe Mac