ExcelBanter - Can't Find Correct Value

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Can't Find Correct Value (https://www.excelbanter.com/excel-programming/310486-cant-find-correct-value.html)

Can't Find Correct Value

The following is a procedure that is giving me incorrect results when a
"Flight Number" is repeated in the list.
Cell C3 has a value of "Alaska Airlines Flt 2369/516"
Cell C15 has a value of "Alaska Airlines Flt 2369"

In this case, because 2369 is repeated, Cell C3 is selected, even though I
have specified Cell C15.

I want the value of the entire cell to be evaluated, not just the number.

Can someone help?

Thanks,
Bernie
-----------
Sub PickMyFlight()
Dim c, Rownum, Prompt, firstaddress
Dim FltNum As Range
S1Cell = ActiveCell.Address
SortGreen
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 1
DoAgain:
Range("C1").Select
Prompt = "Find your ""From"" Flight in Column D." & Chr(13) & Chr(10)
_
& "Find your ""To"" Flight in Column E." & Chr(13) & Chr(10) _
& Chr(13) & Chr(10) & "Then Select your specific flight from Column C."
'& Chr(13) & Chr(10)
On Error GoTo ErrHandler
Set FltNum = Application.InputBox(Prompt, "Airline/Flight Number", ,
340, 150, Type:=8)
MsgBox FltNum
If FltNum Is Nothing Then GoTo ErrHandler
With Worksheets("ListNames").Range("$A$1:H$99")
Set c = .Find(FltNum)
If Not c Is Nothing Then
firstaddress = c.Address
' Do
If Range(firstaddress).Column < 3 Then
MsgBox "Invalid Selection. Choose a Flight from Column C."
GoTo DoAgain
End If
If Range(firstaddress).Value < "" Then
Range(firstaddress).Select
' MsgBox "Found " & Range(firstaddress).Value
GoTo FoundFlight
End If
MsgBox "Your Selection is Empty. Please Try Again."
GoTo DoAgain
' Set c = .FindNext(c)
' Loop While Not c Is Nothing And c.Address < firstaddress
End If
On Error GoTo MultipleValues
If Range(firstaddress).Value < "" Then MsgBox
Range(firstaddress).Value & " is out of range"
End With
GoTo DoAgain
CancelButton:
Sheets("Sheet1").Select
Range(S1Cell).Select
SetFontSheet1 (S1Cell)
LastCellBeforeBlankInColumn
GoTo Finished

FoundFlight:
Application.ScreenUpdating = False
Selection.Copy
Sheets("Sheet1").Select
Range(S1Cell).Select
ActiveSheet.Paste
SetFontSheet1 (S1Cell)
LastCellBeforeBlankInColumn
GoTo Finished

MultipleValues:
MsgBox "You must select from Column C"
Resume DoAgain
ErrHandler:
GoTo CancelButton
Finished:
Application.ScreenUpdating = True
End Sub

Can't Find Correct Value

Hi BW,

Try using the optional LookAt argument for the find method.

Set c = .Find(FltNum, LookAt:=xlWhole)

---
Regards,
Norman

"bw" wrote in message
...
The following is a procedure that is giving me incorrect results when a
"Flight Number" is repeated in the list.
Cell C3 has a value of "Alaska Airlines Flt 2369/516"
Cell C15 has a value of "Alaska Airlines Flt 2369"

In this case, because 2369 is repeated, Cell C3 is selected, even though I
have specified Cell C15.

I want the value of the entire cell to be evaluated, not just the number.

Can someone help?

Thanks,
Bernie
-----------
Sub PickMyFlight()
Dim c, Rownum, Prompt, firstaddress
Dim FltNum As Range
S1Cell = ActiveCell.Address
SortGreen
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 1
DoAgain:
Range("C1").Select
Prompt = "Find your ""From"" Flight in Column D." & Chr(13) & Chr(10)
_
& "Find your ""To"" Flight in Column E." & Chr(13) & Chr(10) _
& Chr(13) & Chr(10) & "Then Select your specific flight from Column C."
'& Chr(13) & Chr(10)
On Error GoTo ErrHandler
Set FltNum = Application.InputBox(Prompt, "Airline/Flight Number", ,
340, 150, Type:=8)
MsgBox FltNum
If FltNum Is Nothing Then GoTo ErrHandler
With Worksheets("ListNames").Range("$A$1:H$99")
Set c = .Find(FltNum)
If Not c Is Nothing Then
firstaddress = c.Address
' Do
If Range(firstaddress).Column < 3 Then
MsgBox "Invalid Selection. Choose a Flight from Column C."
GoTo DoAgain
End If
If Range(firstaddress).Value < "" Then
Range(firstaddress).Select
' MsgBox "Found " & Range(firstaddress).Value
GoTo FoundFlight
End If
MsgBox "Your Selection is Empty. Please Try Again."
GoTo DoAgain
' Set c = .FindNext(c)
' Loop While Not c Is Nothing And c.Address < firstaddress
End If
On Error GoTo MultipleValues
If Range(firstaddress).Value < "" Then MsgBox
Range(firstaddress).Value & " is out of range"
End With
GoTo DoAgain
CancelButton:
Sheets("Sheet1").Select
Range(S1Cell).Select
SetFontSheet1 (S1Cell)
LastCellBeforeBlankInColumn
GoTo Finished

FoundFlight:
Application.ScreenUpdating = False
Selection.Copy
Sheets("Sheet1").Select
Range(S1Cell).Select
ActiveSheet.Paste
SetFontSheet1 (S1Cell)
LastCellBeforeBlankInColumn
GoTo Finished

MultipleValues:
MsgBox "You must select from Column C"
Resume DoAgain
ErrHandler:
GoTo CancelButton
Finished:
Application.ScreenUpdating = True
End Sub

Can't Find Correct Value

Thanks, Norman.

That works fine, and I appreciate your help.

Bernie

On Sun, 19 Sep 2004 21:01:46 +0100, Norman Jones
wrote:

Hi BW,

Try using the optional LookAt argument for the find method.

Set c = .Find(FltNum, LookAt:=xlWhole)

---
Regards,
Norman

"bw" wrote in message
...
The following is a procedure that is giving me incorrect results when a
"Flight Number" is repeated in the list.
Cell C3 has a value of "Alaska Airlines Flt 2369/516"
Cell C15 has a value of "Alaska Airlines Flt 2369"

In this case, because 2369 is repeated, Cell C3 is selected, even
though I
have specified Cell C15.

I want the value of the entire cell to be evaluated, not just the
number.

Can someone help?

Thanks,
Bernie
-----------
Sub PickMyFlight()
Dim c, Rownum, Prompt, firstaddress
Dim FltNum As Range
S1Cell = ActiveCell.Address
SortGreen
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 1
DoAgain:
Range("C1").Select
Prompt = "Find your ""From"" Flight in Column D." & Chr(13) &
Chr(10)
_
& "Find your ""To"" Flight in Column E." & Chr(13) & Chr(10) _
& Chr(13) & Chr(10) & "Then Select your specific flight from Column
C."
'& Chr(13) & Chr(10)
On Error GoTo ErrHandler
Set FltNum = Application.InputBox(Prompt, "Airline/Flight Number", ,
340, 150, Type:=8)
MsgBox FltNum
If FltNum Is Nothing Then GoTo ErrHandler
With Worksheets("ListNames").Range("$A$1:H$99")
Set c = .Find(FltNum)
If Not c Is Nothing Then
firstaddress = c.Address
' Do
If Range(firstaddress).Column < 3 Then
MsgBox "Invalid Selection. Choose a Flight from Column C."
GoTo DoAgain
End If
If Range(firstaddress).Value < "" Then
Range(firstaddress).Select
' MsgBox "Found " & Range(firstaddress).Value
GoTo FoundFlight
End If
MsgBox "Your Selection is Empty. Please Try Again."
GoTo DoAgain
' Set c = .FindNext(c)
' Loop While Not c Is Nothing And c.Address < firstaddress
End If
On Error GoTo MultipleValues
If Range(firstaddress).Value < "" Then MsgBox
Range(firstaddress).Value & " is out of range"
End With
GoTo DoAgain
CancelButton:
Sheets("Sheet1").Select
Range(S1Cell).Select
SetFontSheet1 (S1Cell)
LastCellBeforeBlankInColumn
GoTo Finished

FoundFlight:
Application.ScreenUpdating = False
Selection.Copy
Sheets("Sheet1").Select
Range(S1Cell).Select
ActiveSheet.Paste
SetFontSheet1 (S1Cell)
LastCellBeforeBlankInColumn
GoTo Finished

MultipleValues:
MsgBox "You must select from Column C"
Resume DoAgain
ErrHandler:
GoTo CancelButton
Finished:
Application.ScreenUpdating = True
End Sub