Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set and then select
Hi group,
I am doing a find for text. I thought i had to Set as a range, then do the "Find" then select it, but I am getting an error. This is the code I have that produces the Error: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Any help would be appreciated. -- David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
But
Hi again,
I got part of this, but it is part of a loop. I was able to select the cell in the first instance of the find, but when the loop goes around again, it fails. The loop includes the Set and Find again, but the FoundCell has nothing in it at this point, so it can not be activated. New Code: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Range(FoundCell.Address).Activate <-This is now where it fails. -- David "David" wrote: Hi group, I am doing a find for text. I thought i had to Set as a range, then do the "Find" then select it, but I am getting an error. This is the code I have that produces the Error: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Any help would be appreciated. -- David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
But
Hi David,
You can test FoundCell with something like this: If Not FoundCell Is Nothing Then 'a match was found. FoundCell.Activate End If -- Hope that helps. Vergel Adriano "David" wrote: Hi again, I got part of this, but it is part of a loop. I was able to select the cell in the first instance of the find, but when the loop goes around again, it fails. The loop includes the Set and Find again, but the FoundCell has nothing in it at this point, so it can not be activated. New Code: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Range(FoundCell.Address).Activate <-This is now where it fails. -- David "David" wrote: Hi group, I am doing a find for text. I thought i had to Set as a range, then do the "Find" then select it, but I am getting an error. This is the code I have that produces the Error: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Any help would be appreciated. -- David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
But
Hi Again,
I am able to Select the cell on the first instance, but in the loop on the second instance it fails. "Object Variable or With block variable not set" Thanks, -- David "Vergel Adriano" wrote: Hi David, You can test FoundCell with something like this: If Not FoundCell Is Nothing Then 'a match was found. FoundCell.Activate End If -- Hope that helps. Vergel Adriano "David" wrote: Hi again, I got part of this, but it is part of a loop. I was able to select the cell in the first instance of the find, but when the loop goes around again, it fails. The loop includes the Set and Find again, but the FoundCell has nothing in it at this point, so it can not be activated. New Code: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Range(FoundCell.Address).Activate <-This is now where it fails. -- David "David" wrote: Hi group, I am doing a find for text. I thought i had to Set as a range, then do the "Find" then select it, but I am getting an error. This is the code I have that produces the Error: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Any help would be appreciated. -- David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
But
Hi David,
I'm not sure what you mean by being able to select on the first instance as there is no loop in the code that you provided. But basically, the reason you get the error is if Find does not find any match, it returns Nothing. Using the code you provided, try it this way: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'Range(FoundCell.Address).Activate <-This is now where it fails. If Not FoundCell is Nothing Then FoundCell.Activate End if -- Hope that helps. Vergel Adriano "David" wrote: Hi Again, I am able to Select the cell on the first instance, but in the loop on the second instance it fails. "Object Variable or With block variable not set" Thanks, -- David "Vergel Adriano" wrote: Hi David, You can test FoundCell with something like this: If Not FoundCell Is Nothing Then 'a match was found. FoundCell.Activate End If -- Hope that helps. Vergel Adriano "David" wrote: Hi again, I got part of this, but it is part of a loop. I was able to select the cell in the first instance of the find, but when the loop goes around again, it fails. The loop includes the Set and Find again, but the FoundCell has nothing in it at this point, so it can not be activated. New Code: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Range(FoundCell.Address).Activate <-This is now where it fails. -- David "David" wrote: Hi group, I am doing a find for text. I thought i had to Set as a range, then do the "Find" then select it, but I am getting an error. This is the code I have that produces the Error: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Any help would be appreciated. -- David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
But
Maybe you should be using the FindNext method:
myRange = Range("WhateverYouWant") With Worksheets(1).Range(myRange) Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With "David" wrote: Hi again, I got part of this, but it is part of a loop. I was able to select the cell in the first instance of the find, but when the loop goes around again, it fails. The loop includes the Set and Find again, but the FoundCell has nothing in it at this point, so it can not be activated. New Code: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Range(FoundCell.Address).Activate <-This is now where it fails. -- David "David" wrote: Hi group, I am doing a find for text. I thought i had to Set as a range, then do the "Find" then select it, but I am getting an error. This is the code I have that produces the Error: Dim FoundCell As Range Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Any help would be appreciated. -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
End Select without Select Case, Block If without End If errors | Excel Programming | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) |