Not finding value, range method failing

  • Thread starter Thread starter davegb
  • Start date Start date
D

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.
 
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...
 
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
 
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)
 
Tom said:
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
Click to expand...

Thanks Tom!
 
If there is a question in that post, I don't know where it is since
everything in quoted.
 
Tom said:
If there is a question in that post, I don't know where it is since
everything in quoted.
Click to expand...

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.
 
Back
Top