| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I get around "Find" if there's nothing to find
I need to my macro to be able to Find certain words and delete the rows wherever these words occur. If I use the Cells.Find(????) to locate the words, the macro doesn't work ifthe word to find isn't in the spreadsheet. How can I get around the Cell.Find(????), ie to ignore and move on to the net step if there aren't any more of ??? to find. This is causing me so much frustration!!! Thanks  -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
Set a range to the cell.find then check if it is nothing like this:
Dim FindCell as Range Set FindCell = Cells.Find(What:="YourText", After:=Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=True) If FindCell = nothing then Msgbox "Search String not Found" Else 'Your Code here End if HTH Die_Another_Day CarolineHedges wrote: I need to my macro to be able to Find certain words and delete the rows wherever these words occur. If I use the Cells.Find(????) to locate the words, the macro doesn't work ifthe word to find isn't in the spreadsheet. How can I get around the Cell.Find(????), ie to ignore and move on to the net step if there aren't any more of ??? to find. This is causing me so much frustration!!! Thanks -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
Cool thanks! -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
I have input the code, but it doesn't like the bit: If FindCell = Nothing Then An error message comes up as "Invalid use of object". It wont even run the macro with this part.... help!! -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
Typo!
If FindCell is Nothing Et voilà! Cheers, -- AP "CarolineHedges" <CarolineHedges.2a0kgp_1151332807.9415@excelforu m-nospam.com a écrit dans le message de news: ... I have input the code, but it doesn't like the bit: If FindCell = Nothing Then An error message comes up as "Invalid use of object". It wont even run the macro with this part.... help!! -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
I have done this: Dim FindCell As Range Set FindCell = Cells.Find(What:="Security", After:=Range("A2"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=True) If FindCell Is Nothing Then GoTo 2 Else Selection.EntireRow.Delete End If But it doesn't do anything. Sorry I'm not very advanced on macros, I've taught myself over the last 10mnths!! Do I need to give it a range? When you said set a range to the cel......... Thank you in advance -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
Your range FindCell is not selected. You are deleting whatever has been
selected which is not specified in the code you have posted here. you probably want something more like... Dim FindCell As Range Set FindCell = Cells.Find(What:="Security", After:=Range("A2"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=True) If FindCell Is Nothing Then GoTo 2 Else findcell.EntireRow.Delete End If -- HTH... Jim Thomlinson "CarolineHedges" wrote: I have done this: Dim FindCell As Range Set FindCell = Cells.Find(What:="Security", After:=Range("A2"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=True) If FindCell Is Nothing Then GoTo 2 Else Selection.EntireRow.Delete End If But it doesn't do anything. Sorry I'm not very advanced on macros, I've taught myself over the last 10mnths!! Do I need to give it a range? When you said set a range to the cel......... Thank you in advance -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
Hi Ardus, quick question. What's the difference between "is" and "="? I
mix those up frequently and am not sure when it's appropriate to use them. Tx Die_Another_Day Ardus Petus wrote: Typo! If FindCell is Nothing Et voilà! Cheers, -- AP "CarolineHedges" <CarolineHedges.2a0kgp_1151332807.9415@excelforu m-nospam.com a écrit dans le message de news: ... I have input the code, but it doesn't like the bit: If FindCell = Nothing Then An error message comes up as "Invalid use of object". It wont even run the macro with this part.... help!! -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get around "Find" if there's nothing to find
Is referes to objects. = refers to variables. So for a range object or a
worksheet object you need is. For an Integer or a String you want =... -- HTH... Jim Thomlinson "Die_Another_Day" wrote: Hi Ardus, quick question. What's the difference between "is" and "="? I mix those up frequently and am not sure when it's appropriate to use them. Tx Die_Another_Day Ardus Petus wrote: Typo! If FindCell is Nothing Et voilÃ*! Cheers, -- AP "CarolineHedges" <CarolineHedges.2a0kgp_1151332807.9415@excelforu m-nospam.com a écrit dans le message de news: ... I have input the code, but it doesn't like the bit: If FindCell = Nothing Then An error message comes up as "Invalid use of object". It wont even run the macro with this part.... help!! -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
| Where is "open/tools/find/find files that match these criteria"? | Excel Discussion (Misc queries) | |||
| How to change the default in Excel from "find next" to "find all" | Excel Discussion (Misc queries) | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
![CarolineHedges[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
