starting from bottom of range instead of top

[davegb]

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]

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]

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).

Thanks for your reply, Dave. This helps a lot. I'm still having
problems with this other part. In the code following the remark, 'TEST
for county numbers/names or names, I want to test for valid county name
at the top of the list (wsSrc) by comparing it to the first value in
the reference list (rCtyLst). If the names match, go ahead with the
macro, if they don't, tell the user something is wrong. But for some
reason, when I define the range rCtyLst, it selects the last value in
the list not the first, and tests for that value. So the rest of the
macro shouldn't run. But I don't know how to tell it to start with the
first value in the range, since I always thought that when you do a
comparison like this, it would automatically start at the top of the
range. Any ideas on how to tell it to start with the first value in the
range? I guess I could just compare that particular cell instead of
using the range.

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.

The .selects and .activates are mostly just to make sure the program is
getting the values where I want it to get them. Most of them will be
removed when this part of the code is running properly. Of course, I
have to be careful that the correct sheet is activated when needed.
Thanks for changing it to having the user select the appropriate
columns. You are a step ahead of me. I had planned on asking how to do
that after I got the macro running properly.

 

[Dave Peterson]

Would rCtyLst.cells(1) give you the top cell in rCtyLst?

Or did you mean something like:

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

This starts at cells(1), but then looks up the list (xlprevious). (I forgot to
mention that change in the previous message.)

 

[davegb]

Would rCtyLst.cells(1) give you the top cell in rCtyLst?

Or did you mean something like:

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

This starts at cells(1), but then looks up the list (xlprevious). (I forgot to
mention that change in the previous message.)

I tried your second solution, but it didn't work. I don't know why. I
did try .cells(2) and .cells(3) and it made no difference, rFndCell
still returned the last item in rCtyLst.
However, the solution at the top worked fine. I defined rCtyLstStrt =
rCtyLst.cells(1) and it picked up the first item in that list.
Thanks for the help.