|
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:confused: -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
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:confused: -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=555574 |
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 |
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 |
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 |
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 |
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 |
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 |
How can I get around "Find" if there's nothing to find
Thanks Jim. That somewhat makes sense. Just a couple other questions...
Why can I still use if FindCell = ""? Do I just use "is" with the "nothing" command? Die_Another_Day Jim Thomlinson wrote: 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 |
How can I get around "Find" if there's nothing to find
Because the default property of the range object is ".Value". So you are
really comparing a string value and hence "=" not "Is" with code : If FindCell.Value = "".... I always explicitly state the property of an object, to avoid confusion as above NickHK "Die_Another_Day" wrote in message oups.com... Thanks Jim. That somewhat makes sense. Just a couple other questions... Why can I still use if FindCell = ""? Do I just use "is" with the "nothing" command? Die_Another_Day Jim Thomlinson wrote: 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 |
| All times are GMT +1. The time now is 06:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com