Retrieving Excel data to post to a form

  • Thread starter Thread starter Joe Mac
  • Start date Start date
J

Joe Mac

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
 
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
 
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
 
Back
Top