Not finding value, range method failing
 

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.

Not finding value, range method failing
 

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.

Not finding value, range method failing
 

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" wrote in message
oups.com...
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.

Not finding value, range method failing
 

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" wrote in
message ...
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.

Not finding value, range method failing
 

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" wrote in
message ...
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.

Not finding value, range method failing
 

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

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote in
message ...
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" wrote in
message ...
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.

Not finding value, range method failing
 

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" wrote in message
oups.com...
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.

Not finding value, range method failing
 

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

--
Regards,
Tom Ogilvy


"davegb" wrote in message
ups.com...

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" wrote in message
oups.com...
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.

Not finding value, range method failing
 

Almost everything, Kemo Sabe!


--
Regards,
Tom Ogilvy

Thanks Tom!



"davegb" wrote in message
oups.com...
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" wrote in message
ups.com...

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" wrote in message
oups.com...
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

Not finding value, range method failing
 

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" wrote in message
ups.com...

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" wrote in message
oups.com...
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.