Trapping a NO FIND after a find

I use the code below to store a row number to a variable after a find.

I would like to trap a NO FIND if the find is unsuccessfull
Any ideas. FSt1 provided the code below

Sub macfindrow()
dim rn as string
dim rng as range
dim therow as long

rn = inputbox("enter something to find")
if rn <> "" then
     Set rng = nothing
     Set rng = range("A1:IV65536").Find(what:=rn, _
                              After:=Range("A1"), _
                              Lookin:=xlformulas, _
                              Lookat:=xlpart, _
                              SearchOrder:=xlbyrows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=false)
end if
therow = rng.row
msgbox "Found at cell " & rng.address
msbbox "The row number is " & therow
end sub


0
Alan6824 (103)
8/31/2005 7:53:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
680 Views

Similar Articles

[PageSpeed] 37

hi,
if nothing was found, how do you trap nothing?
add this to the code before the other msgboxes.

If rng Is Nothing Then
MsgBox rn & "  was Not found."
exit sub
End If

regards
FSt1


"Alan" wrote:

> I use the code below to store a row number to a variable after a find.
> 
> I would like to trap a NO FIND if the find is unsuccessfull
> Any ideas. FSt1 provided the code below
> 
> Sub macfindrow()
> dim rn as string
> dim rng as range
> dim therow as long
> 
> rn = inputbox("enter something to find")
> if rn <> "" then
>      Set rng = nothing
>      Set rng = range("A1:IV65536").Find(what:=rn, _
>                               After:=Range("A1"), _
>                               Lookin:=xlformulas, _
>                               Lookat:=xlpart, _
>                               SearchOrder:=xlbyrows, _
>                               SearchDirection:=xlNext, _
>                               MatchCase:=false)
> end if
> therow = rng.row
> msgbox "Found at cell " & rng.address
> msbbox "The row number is " & therow
> end sub
> 
> 
0
FSt1 (238)
8/31/2005 12:07:06 PM
Hello,

Thanks again. 
I can now go off and do something else within the script if nothing is found
"FSt1" wrote:

> hi,
> if nothing was found, how do you trap nothing?
> add this to the code before the other msgboxes.
> 
> If rng Is Nothing Then
> MsgBox rn & "  was Not found."
> exit sub
> End If
> 
> regards
> FSt1
> 
> 
> "Alan" wrote:
> 
> > I use the code below to store a row number to a variable after a find.
> > 
> > I would like to trap a NO FIND if the find is unsuccessfull
> > Any ideas. FSt1 provided the code below
> > 
> > Sub macfindrow()
> > dim rn as string
> > dim rng as range
> > dim therow as long
> > 
> > rn = inputbox("enter something to find")
> > if rn <> "" then
> >      Set rng = nothing
> >      Set rng = range("A1:IV65536").Find(what:=rn, _
> >                               After:=Range("A1"), _
> >                               Lookin:=xlformulas, _
> >                               Lookat:=xlpart, _
> >                               SearchOrder:=xlbyrows, _
> >                               SearchDirection:=xlNext, _
> >                               MatchCase:=false)
> > end if
> > therow = rng.row
> > msgbox "Found at cell " & rng.address
> > msbbox "The row number is " & therow
> > end sub
> > 
> > 
0
Alan6824 (103)
8/31/2005 12:28:39 PM
Reply:

Similar Artilces:

How to send message to advanced find results?
Running Outlook 2003. I run an advanced query to get a list of contacts. Now I have exactly the list of contacts I want to email in the Advanced Find results dialog. Is there an easy way to send an e-mail to everyone in the results list? Thanks for your help. Select all, drag to your inbox? --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Tom C. asked: | Running Outlook 2003. I run an advanced query to get a list of | c...

Macro to find and delete all FALSE statements
So i have a sheet filled with formulas and I have put IF,Then statements in them to return FALSE is I don't get the result I want. However, I need to erase all these FALSE results so they do not plot as zero on my chart. Anyway to create a macro? Thanks. You may not need a macro. If you formulas are of the type =IF(A1<10,FALSE,A1), select the range and use Edit | Replace to change FALSE to NA(). The NA() will show in cell as #N/A and will be ignored by the chart. best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Woody13" &l...

VLookup VBA error trapping
Can anyone tell me how to modify this formula to trap errors? I tried wrapping it in IsError (code) Then...but it doesn't work. Public Sub RunMeNow() Dim i As Long Dim iLastRow As Long With Sheets("Sheet2") iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 3 To iLastRow 'iLastRow to 1 Step -1 .Cells(i, "B").Value = Application.VLookup( _ .Cells(i, "A").Value, Range("RegionGrouping"), 2, False) Next i End With End Sub Or any other way I can handle an error with this type of pr...

Implementing Find on a CListCtrl
I'm trying to implement something similar to the "CListBox::FindString " function but on a CListCtrl in Report View. I'm feeding the list with a simple CData class member (this class consists only of 3 CStrings), and after the list has been fed with all the info, i'm sending one CData variable to a function named "UpdateList (CData* pData)". In this function, i'm trying to find this pData in the List by doing this: void CWordToolView::UpdateList(CData* pData) { .... LVFINDINFO findStruct; LV_ITEM editItem; findStruct.flags = LVFI_PARAM; ...

Where can I find a Downloadable Manual for Publisher2003?
I am having trouble finding a downloadable maual for publisher 2003. Can anyone help please? There are several excellent sources of information available on the web but I don't know of anything that's a "manual" per se. If you want something that's electronic, why not just use the Help file? It's already there, no installation or downloading needed. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Wheelbrace1" <Wheelbrace1@discussions.microsoft.com> wrote in message news:DC0C9268-...

How to find the check form to use for GP 9.0
I don't want to use Deluxe as our check vendor. I want to use Harland. Microsoft MBS sales just gives us Deluxe's phone number, when we request help with obtaining the correct format for Harland checks. Harland has two check formats for GP payable checks. 1) Great Plains Accounting 7.0 - 9.0 2) Great Plains Dynamics Harland isn't sure and can't reference Deluxe. Thanks, John John, From what I can tell from Harland's website, you want the Laser Multi-Purpose checks they show under Microsoft Great Plains. That's the one with the check in the middle, stub on t...

Unable to find email server
Hello, One of the computers on the LAN gets the following error: 'Sending and Receiving' reported error (0x800CCC0D): 'Unable to find e-mail server. Please verify the server information in your account' The Internet Explorer is also unable to connect to the Internet. Any suggestions? Thanks, Lee It sounds like the network connection isn't working. Have you verified the network card, cable, port, and router are all OK? This doesn't sound like just an Outlook problem. -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you c...

trapping LVN_ENDSCROLL
Hi there, I'm trying to trap the LVN_ENDSCROLL notification in my parent, but don't seem to get it. Here's what I have. In my parent's header: void OnLvnEndScroll(NMHDR *pNMHDR, LRESULT *pResult); In the source: void CSKListCtrl::OnLvnEndScroll(NMHDR *pNMHDR, LRESULT *pResult) { Invalidate(); UpdateWindow(); *pResult = 0; } In the message map: ON_NOTIFY(LVN_ENDSCROLL, WM_QUICKLIST, OnLvnEndScroll) where WM_QUICKLIST is mapped to my child's ID. What am I doing wrong? Thank you. Well since I couldn't find a way of trapping LVN_ENDSCROLL, I just used the HSCROL...

finding values in a sum
if you have an array of numbers and want to identify which of those numbers add up to a specified value, is there a function in excel that can help you to find the correct combination of numbers. eg in a simple example; if the array of numbers was 2,3,5,6 and the specified value was 9, we know the only combination of numbers from this array that would sum to give the value 9 are 6 & 3, however with a larger array of numbers (20) or more, it would be more difficult solve the problem manually. http://groups.google.com/groups?threadm=e3iWLUiYDHA.2960%40tk2msftngp13.phx. gbl See my previo...

The system cannot find the file specified
I am receiving this error in Outlook 2003 when attempting to open Word attachments. I have tried deleting all temporary internet files and all files in the OKLE temp folder and the problem was corrected - for about five minutes! If I open the attachment a second time, it opens no problem. The error is popping up sporadically on our users machines. Any thoughts on this would be greatly appreciated! Have you cleared the IE cache? Tools --> Internet Options --> Delete Files. -- Rodney Buike MVP Windows Server - Directory Services http://thelazyadmin.com "Natalie Lewi...

Finding Friday
Okay, I have a spreadsheet. Cell A1 contains the month, Column B contains all the weekdays of that month. Daily data is stored is Column C. I want to do weekly totals of the data in Column C as well. However, if the user inputs a different month in A1, the weekdays will all change, shift...etc. Therefore, to total week 1, I need to locate the first instance of Friday in Column B, and then add that to everything above it. Week 2 needs to find the 2nd instance of Friday and add everything from that Friday to the first Friday...and so on. How does one do this? Thank you very much....

find nth position of a string
Thanks in advance if you can plese give me a function to find 2/g/25/21/k/2/5 22/h/25/21/l/3/5 1. 4 th or 5th position string"/" in the above text strings. 2. After nth position what text is there? 3.4 th or 5th position of string"/" from right side ? I have tried with right,left mid functions but didn't get correctt result considering that you have the above in A1 and A2, then use: 1. =MID(A1,4,1) =MID(A2,5,1) 2. After nth position, (excluding position n) =MID(A1,n+1,255) (replace n with whatever number If you want to include nth charater as well =MID(A1,n,255) 3....

Find Missing Number?
Ok probably a easy answer here, but I'm a excel Novice. what I have is a series of numbers and what I want to find is what Number are missing in between all the different numbers to complete a whole set of number. Example I have: 1 2 6 7 9 13 How could I find whats missing as to make it straight count up from 1 to 13 And return from this example 3 4 5 8 10 11 12 Thanks for any help Hi there, one possibility is in an additional column or sheet, i would create the whole series (type A1: 1, A2: = A1+1, then copy downwards until you have the required series) then, next to this, so...

Where can i find a football/Soccer table template to download?
I need a blank template, so that we can fill in the team names, and one that add's up the socres etc. ...

Finding files that don't match
I have two excel files that I've imported into Access. One has titles that the other doesn't. How do I create a query that shows the non-matches? Thanks, Josh Axelrad joshua.axelrad@tvguide.com Titles of fields do not have to match do run an unmatched query, just make sure they are the same datatype. -- Build a little, test a little. "Josh" wrote: > I have two excel files that I've imported into Access. One has titles that > the other doesn't. How do I create a query that shows the non-matches? > > Thanks, > > Jo...

Advanced Find For Opportunities
Trying to find a name of a Potential Customer in a large list does not seem very easy. TheFind box does not work unless you type in something it is regarding. Advanced Find you can choose to find Opportunities, then can get the Potential Customer Field, but when you choose equals you cannot choose and Opportunity Name from the lookup, only Contacts and Accounts. There is no Contains so you can put in *Name* in it to locate the name. Any suggestion on the best way to find a name in an opportunity? Thanks! Shauna Hi Shauna, Are you looking for a particular Opportunity that has a...

Trapping errors
How do write a piece of code that traps VBA error messages and ignores them? See my post on 22/07/07 about an Automation error message Thanks Tony Take a look at what Allen Browne has at http://www.allenbrowne.com/ser-23a.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Tony Williams" <tw@invalid.com> wrote in message news:uWKdkqVzHHA.5992@TK2MSFTNGP02.phx.gbl... > How do write a piece of code that traps VBA error messages and ignores > them? > See my post on 22/07/07 about an Automation error message > Thanks >...

can't find my macro...
Using Excel 2002 I had been experimenting with making a macro that would copy and paste values before closing. I decided that I would not need it after all and thought I had deleted it using the tools; Macro; macros window. Actually I can no longer see it or any macros in that window. Now, when I open my spreadsheet it still gives me the disable macros dialog. Where else do I need to look to delete whatever is still left of this macro so that the macro dialog no longer pops up when the spreadsheet is opened? Thank you! ------------------------------------------------ ~~ Message pos...

Finding formulas
Do you have a formula or something that I can use in conditional format to tell if a cell contains a formula. >-----Original Message----- >Do you have a formula or something that I can use in >conditional format to tell if a cell contains a formula. >. You could use this macro Sub test() Selection.SpecialCells(xlFormulas).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Range("A1").Activate End Sub Peter Atherton Copy/paste this UDF to a general module in your workbook. Function IsFormula(cell) Application.Vol...

how do i put formula to find difference between two dates
Re: how do i put formula to find difference between two dates Put in C1: =B1-A1 where A1 contains the start date, say: 01-May-2005 B1contains the end date, say: 10-May-2005 For the sample dates in A1 and B1, C1 will return: 9 --- Pl post your question within the message area in future. The "Subject" line is meant only for some key words/descripts, not to house your *entire* post -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "A.D" <A.D@discussions.microsoft.com> wrote in message news:6A8ACB02-0724-4A0B-BB...

where can I find a door hanger flyer template
I am a mortgage broker who wants to market appartment complexes and I stumbled accross a template a few weeks ago for door hangers and now I can't find it. Does anyone know where I can get one of these? I appreciate any feedback. HP has several on their site but they are .pdfs. That won't help you unless you have Acrobat, not just the reader. -- JoAnn Paules MVP Microsoft [Publisher] "Doug" <Doug@discussions.microsoft.com> wrote in message news:25F44A6D-B3E3-46F5-9B2B-BECC9A7C5BB4@microsoft.com... >I am a mortgage broker who wants to market appartment co...

97: How can I find duplicate entries in a column?
Hello and thanks in advance for any help. This is a very basic question from an even more basic user. I have a spread sheet with a long list of ID numbers for skips. Sometimes duplicate numbers arise from paper work from elsewhere and I get a false total of skips in hand. How can I organise that column to highlight a duplicate entry please? Shen --- Message posted from http://www.ExcelForum.com/ Insert a helper column: =COUNTIF(A1:A9999,A1) (adjust the range to suit.) then the formulas that evaluate to more than 1 will be duplicated. Chip Pearson has some other techniques at: http://ww...

How to find reports
Hi to all... I'm new to GP as well to business processes. I modified the report "SOP Blank Invoice Form" and want to know how can I see this report. I know how to access reports, but don't know under which "Sales Report" I can see this specific report. Thanks in advance. -nat Well, when you print sales invoices, select the Blank Invoice Form. "aistorres@hotmail.com" wrote: > Hi to all... I'm new to GP as well to business processes. I modified > the report "SOP Blank Invoice Form" and want to know how can I see this > report....

Trapping for keystrokes...
Is there a way I can write a little program that will trap the keyboard and save whatever is being typed to a file? In other words, I need to write something that will run in the background but will be able to know whatever is typed on the keyboard and then save those keystrokes to a file on the hard drive. Thanks, Fred A keyboard hook. joe On Tue, 30 Sep 2003 03:41:26 GMT, "sftwrdvlpr" <bogus@news.com> wrote: >Is there a way I can write a little program that will trap the keyboard and >save whatever is being typed to a file? In other words, I need to write &g...

Unable to find complete headers in Outlook 2002
I get quite a bit of spam and I want to be able to forward the spam to their ISP. But I connot find the complete headers for the message in outlook 2002 Can you please help me? Either open the message and click View | Options, or right-click the message in the Inbox and select Options. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Sandra Gordon" <sandyg2@ix.netcom.com> wrote in message news:060601c35318$f86fe8b0$a101280a@phx.gbl... > I ...