| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select Method Failing | Excel Programming | |||
| Publish method failing, can't understand why | Excel Programming | |||
| Excel Copy Method Failing. | Excel Programming | |||
| select method of range class failing ? | Excel Programming | |||
| add method of validation failing | Excel Programming | |||
Linear Mode
