VBA: New Idea to lookup record

[Mcasteel]

I guess I didnt fully understand the Match function, and I find codin
it this way easier. Although I still cant get it to work.

With the following code I get the message "ssn not found" even when th
ssn entered is listed in the column.

Do I need to convert the InputCustSSN to an Integer?


Private Sub cmdSelectRecord_Click()

Dim Cell As Range
Dim found As Boolean

Dim lookUpRange As Range
Dim lookupsheet As Sheet1
Dim InputCustSSN As String
Dim rngActive As Range

n = ActiveCell.Value

Set rngActive = ActiveCell.Range("f27:f307")

InputCustSSN = txtCustSSN

found = False

If n <> "" Then
For Each Cell In rngActive
If Cell.Value = InputCustSSN Then
found = True
Exit For
End If
Next Cell
End If

If Not found Then
MsgBox "SSN Not Found!"
Else
MsgBox "Customer SSN Found"
End If

End Sub


Its been a long day.
I promise I will take a refresher course on VBA as soon as the next on
is offered

 

[Bob Phillips]

Mike,

What is this for

n = ActiveCell.Value

you test it for <> "" but then use tyhe textbox value? Does it have a value?

 

[Myrna Larson]

Have you checked the value of InputCstSSN to be sure it is set correctly?

I assume txtCustSSN is a text box on a form? Is VBA recognizing that?

If you put Option Explicit at the top of the module, and it isn't being
recognized, the code won't compile.

Another way to check is to set a break point on the "n = ..." line, then step
through the code with F8. When you hover the mouse over a variable name,
you'll see it's value.