Do you want to find the top most match?

If yes, you can start at the bottom and find the next one.
(If you wanted to find the last occurance, you can start at the top and find the
previous one. (.cells(1) instead of .cells(.cells.count) in the following
code).

You have a few .selects and .activates, so I'm not sure if this is what you
want--but it compiled. Note that instead of letting the user type in a column
letter or number, I changed it to application.inputbox. This allows the user to
point and click--and you don't need to validate that the user actually entered a
correct column number/letter.

Option Explicit
Sub testme01()

Dim wsCtyLst As Worksheet
Dim wsSrc As Worksheet
Dim rFndCell As Range
Dim sCtySrcCol As Long
Dim sColMrk10 As Long
Dim rCtySrc As Range
Dim rCtyLst As String

Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst")

Set wsSrc = ActiveSheet

'Workbooks("Top Ten Extract Test.xls").Activate

sCtySrcCol = 0
sColMrk10 = 0
On Error Resume Next
sCtySrcCol = Application.InputBox _
(prompt:="Please enter the column where the " & _
"counties are currently listed", _
Type:=8, Default:="$a$1").Cells(1).Column
If sCtySrcCol = 0 Then
Exit Sub 'user hit cancel
End If
sColMrk10 = Application.InputBox _
(prompt:="Please enter the column to mark " & _
"the Top Ten Counties", _
Type:=8, Default:="$e$1").Cells(1).Column
If sColMrk10 = 0 Then
Exit Sub 'user hit cancel
End If
On Error GoTo 0

rCtyLst = "something or another"

With wsSrc
Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _
.Cells(2, sCtySrcCol).End(xlDown))
End With

With rCtySrc
Set rFndCell = .Cells.Find(What:=rCtyLst, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If rFndCell Is Nothing Then
MsgBox "Not found"
Else
'do what you want
End If

End Sub


davegb wrote:

I wrote the following code:

Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst")

Set wsSrc = ActiveSheet
'sWhichTop = _
'InputBox("Please enter 10 or 21 to determine which counties you want
extracted", , 10)
'REPLACE with code to determine which table to use
Set rCtyLst = wsCtyLst.Range("C2:C11")
Workbooks("Mark Top 10.xls").Activate
wsCtyLst.Select
rCtyLst.Select

Workbooks("Top Ten Extract Test.xls").Activate

sCtySrcCol = InputBox("Please enter the column where the counties are
currently listed", _
, "A")
sColMrk10 = InputBox("Please enter the column to mark the Top Ten
Counties", "E")

'TEST for valid entries on both variables
'TEST sColMrk10 for existing data

' TEST for county numbers/names or names

With wsSrc
Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _
.Cells(2, sCtySrcCol).End(xlDown))
End With

wsSrc.Select
rCtySrc.Select

Set rFndCell = Cells.Find(What:=rCtyLst, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

I want it to start searching rCtySrc using the first value in rCtyLst,
but it uses the last value. How to I tell it to search from the top
down?
Thanks!

--

Dave Peterson