Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help!
I am trying to use selection.find for a word that may or may not be in the
specified search. My problem is that when I do the selection find and it doesn't find the word, the macro stops and prompts me to debug with the following message: Object variable or With Block variable not set. Here is where the bug is located: Range("V38,V45,V52,V58,V65,V72,V78,V84").Select If Range("AG21").Value = "N" And ActiveCell < "Tony" Then Selection.Find(What:="Alternate", After:=ActiveCell, LookIn: =xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.FormulaR1C1 = "Tony" End If Now I think the problem is that it can't Activate a cell because it did NOT find the word "Alternate". How can I write the code so it will work? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help!
try adding this as the first line and see if it helps
On Error Resume Next -- Gary "Erik K via OfficeKB.com" <u13156@uwe wrote in message news:55ea797c00fbf@uwe... I am trying to use selection.find for a word that may or may not be in the specified search. My problem is that when I do the selection find and it doesn't find the word, the macro stops and prompts me to debug with the following message: Object variable or With Block variable not set. Here is where the bug is located: Range("V38,V45,V52,V58,V65,V72,V78,V84").Select If Range("AG21").Value = "N" And ActiveCell < "Tony" Then Selection.Find(What:="Alternate", After:=ActiveCell, LookIn: =xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.FormulaR1C1 = "Tony" End If Now I think the problem is that it can't Activate a cell because it did NOT find the word "Alternate". How can I write the code so it will work? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help!
Perfect! Thanks!
Gary Keramidas wrote: try adding this as the first line and see if it helps On Error Resume Next I am trying to use selection.find for a word that may or may not be in the specified search. My problem is that when I do the selection find and it [quoted text clipped - 17 lines] NOT find the word "Alternate". How can I write the code so it will work? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help!
Ok, there is still a problem. When I select the range of cells. the first
one selected becomes active so if I say ignore the error and continue, it populates the active cell with the name "Tony" still. What can I do? Gary Keramidas wrote: try adding this as the first line and see if it helps On Error Resume Next I am trying to use selection.find for a word that may or may not be in the specified search. My problem is that when I do the selection find and it [quoted text clipped - 17 lines] NOT find the word "Alternate". How can I write the code so it will work? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help!
Ok, there is still a problem. When I select the range of cells. the first
one selected becomes active so if I say ignore the error and continue, it populates the active cell with the name "Tony" still. What can I do now? Gary Keramidas wrote: try adding this as the first line and see if it helps On Error Resume Next I am trying to use selection.find for a word that may or may not be in the specified search. My problem is that when I do the selection find and it [quoted text clipped - 17 lines] NOT find the word "Alternate". How can I write the code so it will work? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help!
You can use a variable to represent that Found cell.
Dim FoundCell as range set foundcell = Selection.Find(What:="Alternate", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) if foundcell is nothing then 'do nothing??? else foundcell.value = "Tony" end if "Erik K via OfficeKB.com" wrote: I am trying to use selection.find for a word that may or may not be in the specified search. My problem is that when I do the selection find and it doesn't find the word, the macro stops and prompts me to debug with the following message: Object variable or With Block variable not set. Here is where the bug is located: Range("V38,V45,V52,V58,V65,V72,V78,V84").Select If Range("AG21").Value = "N" And ActiveCell < "Tony" Then Selection.Find(What:="Alternate", After:=ActiveCell, LookIn: =xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.FormulaR1C1 = "Tony" End If Now I think the problem is that it can't Activate a cell because it did NOT find the word "Alternate". How can I write the code so it will work? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|