VBA: Pull Record Into UserForm

[Mcasteel]

I have a worksheet full of Customer Information. A simplified recor
would like somewhat like the following:

_*Lname,_Fname,_Mname,_SSN,_info1,_info2,_info3*_

I want to pull the customers LName, FName, SSN, and info into my for
based upon the customers SSN.

So the end user will type in the SSN, and all other cells associate
with that customer will be inserted into either txtboxes or labels o
the user form.

Im getting an error on the code I am working on and could use you
expertise.

_Here_is_the_code_I_have_so_far._

Private Sub cmdSelectRecord_Click()

Dim LName As String
Dim FName As String
Dim MName As String

Dim rngActive As Range
Set rngActive = Application.ActiveCell

Dim iRow As String
Dim CustSSN As String

'Fill variable with User Inputed Cust. SSN
CustSSN = txtCustSSN

iRow = WorksheetFunction.Match(CustSSN
Worksheets("sheet1").Range("f27:f307"), 0)

If iRow = 0 Then
MsgBox "Customer SSN Was Not Found"
Else
MsgBox "Record Found."

'Move Customer info from Worksheet to current form
'lblLName.caption = rngActive.offset(0, -3).value
'lblFName.caption = rngActive.offset(0, -2).Value
'lblMiddle.caption = rngActive.offset(0, -1).value

End If

End Su

 

[Bob Phillips]

Mike,

This works okay for me, both found and not.

I changed iRow to a long (Match returns a number) and put error handling
around the call to Match.

With the line continuation, and assuming F27:F307 are strings, all works.



Private Sub cmdSelectRecord_Click()

Dim LName As String
Dim FName As String
Dim MName As String

Dim rngActive As Range
Set rngActive = Application.ActiveCell

Dim iRow As Long
Dim CustSSN As String

'Fill variable with User Inputed Cust. SSN
CustSSN = txtCustSSN.Text

On Error Resume Next
iRow = WorksheetFunction.Match(CustSSN, _
Worksheets("Sheet1").Range("f27:f307"), 0)
On Error GoTo 0

If iRow = 0 Then
MsgBox "Customer SSN Was Not Found"
Else
MsgBox "Record Found."

'Move Customer info from Worksheet to current form
'lblLName.caption = rngActive.offset(0, -3).value
'lblFName.caption = rngActive.offset(0, -2).Value
'lblMiddle.caption = rngActive.offset(0, -1).value

End If

End Sub

 

[Dave Peterson]

First, I'd change the worksheetfunction.match to application.match.

if you're using

iRow = WorksheetFunction.Match(CustSSN, _
Worksheets("sheet1").Range("f27:f307"), 0)

You'll get a run time error if no match is found.

so maybe:

Dim iRow as long
irow = 0
on error resume next
iRow = WorksheetFunction.Match(CustSSN, _
Worksheets("sheet1").Range("f27:f307"), 0)
on error goto 0

if irow = 0 then
'no match
else
'match
end if

but I like to use application.match instead of worksheetfunction.match. That
returns an error that can be checked.

dim iRow as Variant
iRow = Application.Match(CustSSN,
Worksheets("sheet1").Range("f27:f307"), 0)
if iserror(irow) then
'not found
else
'found
end if

Some other things to consider.

In your list, are the SSN's numeric or Text?

If they're numbers formatted as "000-00-0000", you may want to make sure that
you're searching for a number:

iRow = whateveryouchoose.Match(clng(CustSSN), _
Worksheets("sheet1").Range("f27:f307"), 0)

if they're really text, you'll have to make sure you format them to match your
values in F27:f307.