Not finding value, range method failing

davegb · 03-03-2006, 01:50 PM

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 · 03-03-2006, 02:15 PM

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

Jim Thomlinson

"davegb" wrote:

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

Tom Ogilvy · 03-03-2006, 02:25 PM

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

--
Regards,
Tom Ogilvy

"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

Tom Ogilvy · 03-03-2006, 02:40 PM

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)

--
Regards,
Tom Ogilvy

"Jim Thomlinson" <[email protected]> wrote in
message news:[email protected]...
> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "davegb" wrote:
>
> > 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 · 03-03-2006, 03:10 PM

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

Jim Thomlinson

"Tom Ogilvy" wrote:

> 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)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim Thomlinson" <[email protected]> wrote in
> message news:[email protected]...
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "davegb" wrote:
> >
> > > 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.
> > >
> > >
>
>
>

Tom Ogilvy · 03-03-2006, 03:20 PM

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

--
Regards,
Tom Ogilvy

"Jim Thomlinson" <[email protected]> wrote in
message news:[email protected]...
> Nice catch... I just saw the missing Set statement and didn't look any
> closer...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
> > 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)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Jim Thomlinson" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "davegb" wrote:
> > >
> > > > 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.
> > > >
> > > >
> >
> >
> >

davegb · 03-03-2006, 04:30 PM

Tom Ogilvy wrote:
> 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
>
> --
> Regards,
> Tom Ogilvy

Thanks Tom!

>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >

Tom Ogilvy · 03-03-2006, 05:15 PM

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

--
Regards,
Tom Ogilvy

"davegb" <[email protected]> wrote in message
news:[email protected]...
>
> Tom Ogilvy wrote:
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
>
> Thanks Tom!
>
> >
> >
> > "davegb" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
>

Dave Peterson · 03-03-2006, 05:30 PM

Almost everything, Kemo Sabe!

>
> --
> Regards,
> Tom Ogilvy

Thanks Tom!

>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > I'm having at least 2 problems with the following code:

Tom Ogilvy wrote:
>
> If there is a question in that post, I don't know where it is since
> everything in quoted.
>
> --
> Regards,
> Tom Ogilvy
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Tom Ogilvy wrote:
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> >
> > Thanks Tom!
> >
> > >
> > >
> > > "davegb" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> >

--

Dave Peterson

davegb · 03-06-2006, 11:45 AM

Tom Ogilvy wrote:
> If there is a question in that post, I don't know where it is since
> everything in quoted.
>
> --
> Regards,
> Tom Ogilvy

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.

>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Tom Ogilvy wrote:
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> >
> > Thanks Tom!
> >
> > >
> > >
> > > "davegb" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> >