VBA: New Idea to lookup record
 

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

--
Mcastee
-----------------------------------------------------------------------
Mcasteel's Profile: http://www.excelforum.com/member.php...fo&userid=1569
View this thread: http://www.excelforum.com/showthread.php?threadid=27258

New Idea to lookup record
 

Mike,

What is this for

n = ActiveCell.Value

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

--

HTH

RP

"Mcasteel" wrote in message
...

I guess I didnt fully understand the Match function, and I find coding
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 the
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 one
is offered.


--
Mcasteel
------------------------------------------------------------------------
Mcasteel's Profile:
http://www.excelforum.com/member.php...o&userid=15698
View this thread: http://www.excelforum.com/showthread...hreadid=272584

VBA: New Idea to lookup record
 

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.

On Tue, 26 Oct 2004 14:59:19 -0500, Mcasteel
wrote:


I guess I didnt fully understand the Match function, and I find coding
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 the
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 one
is offered.