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
1716 Views

Similar Articles

[PageSpeed] 12

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 does