Not finding value, range method failing

[davegb]

I'm having at least 2 problems with the following code:

Set wCtyLstSht = Workbooks("Mark Top 10.xls").Worksheets("Sheet1")
Set wTrgtSht = ActiveSheet
Set rCtyLst = wCtyLstSht.Range("C2:C11")

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


' TEST for county numbers/names or names


For Each rCell In rCtyLst

wTrgtSht.Activate
Set rFndCell = Cells.Find(What:=rCell, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False)

If Not rFndCell Is Nothing Then
rCtyMrkr = wTrgtSht.Range(Cells(rFndCell.Row, sColMrk10))
<---ERROR

rCtyMrkr = "y"
End If

Next

End Sub

Range method is failing at the marked place. Can't figure out why.
A watch on rCell shows it is blank, but cell C2 in that worksheet
contains the text ADAMS (which is the value that I want to search the
other sheet for).
Any ideas?
Thanks again.

 

[Jim Thomlinson]

To assign a range object you need to use the set statement (which you omit in
the one line that errors, but that is so easy to miss that I do it myself
regularly). That is why you are having the first problem...

Set rCtyMrkr = wTrgtSht.Range(Cells(rFndCell.Row, sColMrk10))

I was a little unclear wht the second problem was...

 

[Tom Ogilvy]

You never change where rCtyMrkr references in your loop, so any output would
go to the same cell for all 10 checks.


Set wCtyLstSht = Workbooks("Mark Top 10.xls").Worksheets("Sheet1")
Set wTrgtSht = ActiveSheet
Set rCtyLst = wCtyLstSht.Range("C2:C11")

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


' TEST for county numbers/names or names

wTrgtSht.Activate

For Each rCell In rCtyLst

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

If Not rFndCell Is Nothing Then
rCtyMrkr = wTrgtSht.Cells(rFndCell.Row, sColMrk10)
Else
rCtyMrkr = "y"
End If

Next

End Sub

 

[Tom Ogilvy]

Range(Cells(rFndCell.Row, sColMrk10))

will cause an error. Range in this mode must have two cell references or a
string argument.

Either go with just Cells, or put .Address after cells.

I had interpreted the intent of the code differently, but I believe you have
the correct interpretation.

Set rCtyMrkr = wTrgtSht.Cells(rFndCell.Row, sColMrk10)

or

Set rCtyMrkr = wTrgtSht.Range(Cells(rFndCell.Row, sColMrk10).Address)

or

Set rCtyMrkr = wTrgtSht.Range(wTrgtSht.Cells(rFndCell.Row,
sColMrk10).Address)

 

[Jim Thomlinson]

Nice catch... I just saw the missing Set statement and didn't look any
closer...

 

[Tom Ogilvy]

.. . . Got the T-shirt; almost died . . .

 

[davegb]

You never change where rCtyMrkr references in your loop, so any output would
go to the same cell for all 10 checks.


Set wCtyLstSht = Workbooks("Mark Top 10.xls").Worksheets("Sheet1")
Set wTrgtSht = ActiveSheet
Set rCtyLst = wCtyLstSht.Range("C2:C11")

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


' TEST for county numbers/names or names

wTrgtSht.Activate

For Each rCell In rCtyLst

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

If Not rFndCell Is Nothing Then
rCtyMrkr = wTrgtSht.Cells(rFndCell.Row, sColMrk10)
Else
rCtyMrkr = "y"
End If

Next

End Sub

Thanks Tom!

 

[Tom Ogilvy]

If there is a question in that post, I don't know where it is since
everything in quoted.

 

[Dave Peterson]

Almost everything, Kemo Sabe!

Thanks Tom!

If there is a question in that post, I don't know where it is since
everything in quoted.

 

[davegb]

If there is a question in that post, I don't know where it is since
everything in quoted.

Sorry, I forget that the way it appears in the Google NG reader is
different than for others. There was a "Thanks Tom" cleverly hidden in
the middle of that last post.