Finding a text and save it's address

Hi all,
In a specificspred sheet list,I am look for a specific text.after
finding the text,I need  to save it's position  .
my hinge  is  to use combination of .find ()& .address....If you know
the answer,please let me know
Thank you very much!
0
8/6/2009 3:13:52 PM
excel 39879 articles. 2 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 39

I have a VBA procedure at http://www.cpearson.com/Excel/Findall.aspx
that will find all occurrences of a value in a specified range of
cells. As its result, it returns a Range object that references each
cell in which the value was found.  Using that function (posted at the
end of this message), you can do something like:

Sub AAA()

Dim RangeToSearch As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim R As Range

Set RangeToSearch = Worksheets("Sheet1").Range("A1:C20")
FindWhat = "abc"

Set FoundCells = FindAll(SearchRange:=RangeToSearch, _
                FindWhat:=FindWhat, _
                LookIn:=xlValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                MatchCase:=False)

If FoundCells Is Nothing Then
    Debug.Print "not found"
Else
    For Each R In FoundCells
        Debug.Print FindWhat, "found in content '" & _
            R.Text & "' in cell: ", R.Address
    Next R
End If
End Sub

This code will use FindAll to set the variable FoundCells to all the
cells in which "abc" was found. With that variable, you can get both
the value of the cell and the address of the cell. This example just
lists the content and address of the found cells in the Immediate
Window, but you can do whatever you want with the Value and Address
properties of the cells in FoundCells. 

You can download a bas module with the FindAll function at
http://www.cpearson.com/Excel/Findall.aspx or copy the code below. The
downloadable module has other related functions in addition to
FindAll, including an extension of FindAll to search multipe ranges
and multiple worksheets.


Function FindAll(SearchRange As Range, _
                FindWhat As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FindAll
' This searches the range specified by SearchRange and returns a Range
object
' that contains all the cells in which FindWhat was found. The search
parameters to
' this function have the same meaning and effect as they do with the
' Range.Find method. If the value was not found, the function return
Nothing.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range

With SearchRange
    Set LastCell = .Cells(.Cells.Count)
End With
'On Error Resume Next
On Error GoTo 0
Set FoundCell = SearchRange.Find(what:=FindWhat, _
        after:=LastCell, _
        LookIn:=LookIn, _
        LookAt:=LookAt, _
        SearchOrder:=SearchOrder, _
        MatchCase:=MatchCase)

If Not FoundCell Is Nothing Then
    Set FirstFound = FoundCell
    Set ResultRange = FoundCell
    Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Do Until False ' Loop forever. We'll "Exit Do" when necessary.
        If (FoundCell Is Nothing) Then
            Exit Do
        End If
        If (FoundCell.Address = FirstFound.Address) Then
            Exit Do
        End If
        Set ResultRange = Application.Union(ResultRange, FoundCell)
        Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Loop
End If
    
Set FindAll = ResultRange

End Function





Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Thu, 6 Aug 2009 08:13:52 -0700 (PDT), layla
<princess.layla2@gmail.com> wrote:

>Hi all,
>In a specificspred sheet list,I am look for a specific text.after
>finding the text,I need  to save it's position  .
>my hinge  is  to use combination of .find ()& .address....If you know
>the answer,please let me know
>Thank you very much!
0
chip1 (1821)
8/7/2009 12:35:55 AM
Reply:

Similar Artilces:

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Text Very Small When Replying
I'm able to change the text size when I'm replying to an email but how does one set the default size? No matter what I ser the font size to when replying it always reverts back to the smallest size when I do another reply. Look, I'm 54! I can't read that small text! James Are you using stationery? Also, Alt + T, Options, Compose,....... -- Ron Sommer MS MVP-Mail "JamesJ" <jjy@darwin_roadrunne0r.com> wrote in message news:OGt2E1OhKHA.1460@TK2MSFTNGP06.phx.gbl... > I'm able to change the text size when I'm replying to an emai...

Extract text from field
If you have a filed that contatins the following data:LastName, FirstNameWhere LastName has varing lengths. Can you run an update query to obtain just the LastName part of the field? If so, what would be the command?Many, many thanks in advance. On Tue, 6 Mar 2007 14:09:45 -0500, "Mary M" <none@no_email.com> wrote:>If you have a filed that contatins the following data:>>LastName, FirstName>>Where LastName has varing lengths. Can you run an update query to obtain >just the LastName part of the field? If so, what would be the command?>>Many, many thanks in...

Adding a certain text label in a excel chart
I am plotting in regularly basis a certain set of data in excel. Based on some data analysis this set of data has to be fitted to these equations: y = 1/x^a (1) and /or y = b/x^c (2) from data analysis, constants a, b and c are found and are placed lets say in cells A1, B1, C1. On my graph, I am putting then two small text labels where the real equation is displayed: smth. like: y = 1/ x^3.45 and / or y = 0.256 / x^3.12 The whole process is similar with excel curve fitting, when the “show equation on chart” is checked. Thank you in advance My question is: Can ...

Set default address book automatically (registry)
I know I have done this before a few years back in another site - but I cant remember how. We need to set the default lookup address book for all our 2000+ OL2k3 users and to use a specific lookup order. I know they could each do this manually but we have 2000+ users and the support overhead would be huge. We dont want to users to use the GAL as their default lookup (which it is out of the box) because it contains lost of confusing system addresses and duplicate names for the same person in different security level networks; which the users find VERY confusing to distinguish between). We wan...

Program Trying to Access Email Addresses #3
Outlook 2002. When I try to create new mail, forward, or reply I get a dialog box telling me: A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No". How do I determine what program is trying to access the Outlook addresses? I have up to date Norton Anti-virus definitions and have done a system scan, with nothing detected. Thanks for the help. ...

Find and mark duplicates
If I have two columns with data, how do I compare these two columns to find all duplicates and put an X on a third colum next to all duplicates? Thanks. =if(a1=b1,"X","") "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com... > If I have two columns with data, how do I compare these two columns to > find > all duplicates and put an X on a third colum next to all duplicates? > Thanks. The problem is, I don't know which two cells to compare, I need to know if any o...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Save Outlook attachments to PC folder automatically
Hi All, I am an absolute newbie to Outlook programming who has been entrusted with the task of figuring out how to get Outlook to dump attachments to emails sent in by certain users to a specified folder on the PC without having to do it manually. I could not get Rules Wizard to do this. Any pointers on how to go about this is really appreciated. Thanks, Rahul Hi, I found the soluton at a website and leave the link for those who might be interested, http://www.outlookvba.com/examples/code/saveattachmentsimp ..htm Rahul >-----Original Message----- >Hi All, > >I am a...

Link to Page when saving to web
Hi, I have some drawings that have several shapes with "Go to page on double-click" behavior. How can I keep this functionality when saving as a web page? Thanks ...

Won't save office applications, appcrash
Help I cant save documents , excel etc in office 2007 on vista laptop. I have tried open office but this suffers the same problem. Also tried uninstalling and reinstalling office but no change. see below for word report, in excel the fault module was given as PROPSYS.dll Problem Event Name: APPCRASH Application Name: WINWORD.EXE Application Version: 12.0.4518.1014 Application Timestamp: 45428028 Fault Module Name: unknown Fault Module Version: 0.0.0.0 Fault Module Timestamp: 00000000 Exception Code: c0000005 Exception Offset: 00000001 ...

greyed out auto text
Created custom toolbar in Word with menu, autotext and email button as directed in "Quick sales letters for e-mail" demo video, but when I click email button, new email message appears in Outlook but auto text choices are greyed out???? Any ideas?? ...

Transfer address bk and messages
How do I transfer fromm outlook 2003 my info to 2003 on anew computer. Can it really be that hard? Nope. This is your second post. One is enough: http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010771141033&CTT=98 -- Russ Valentine [MVP-Outlook] "diggy" <anonymous@discussions.microsoft.com> wrote in message news:37da01c469f6$b6172d20$3a01280a@phx.gbl... > How do I transfer fromm outlook 2003 my info to 2003 on > anew computer. Can it really be that hard? ...

Howto save an edited xml doc w/MSXML
The following script modifies the content of my xml file: --------------------------------------------------------------------------------------------------------------------------------------- Dim nodeSel, nodeVal, index Dim name : name = "//ROOT/first_level/second_level/third_level" Dim xmlfile : xmlfile = "temp.xml" Dim value : value = "false" Dim xDoc Set xDoc = CreateObject( "MSXML.DOMDocument" ) index = 0 If xDoc.Load( xmlfile ) Then Set nodeSel = xDoc.selectSingleNode( name ) 'WScript.Echo "Found node", nodeS...

insert text to different pages and update
Is there a way to insert a text cell from one page put into another page and when the first page cell is updated the second page cell will also be updated. Using the = sign just displays the formula, not the text. :confused: Thanks Frank -- fwburkey ------------------------------------------------------------------------ fwburkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30314 View this thread: http://www.excelforum.com/showthread.php?threadid=505331 Not clear what you are doing wrong. the formula should look lik =Sheet1!A1 Make sure there is noth...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Finding and naming duplicates
Hi All, not sure if anyone can help with this. I have a column called VNTR24 which has 10+ digit numbers - these are not unique and we want to be able to identify people with matching VNTR24 numbers to cluster them. In excel I've used the following formula: =COUNTIF(A:A,A2)>1 to return in the next column a TRUE or FALSE based on whether two records are matched on VNTR24 number. I need to do the same in Access 2007, returning either a "TRUE" or "FALSE" if the records are clustered/matched on this VNTR24 or not in an UPDATE query!? Can anyone h...

Cursor on new message window but no text appears when typed?
When using Outlook 2003 for sending new e-mail, I'm unable to type text into the message body. The cursor appears but no text shows. Text appears when I use Word 2003 to send my new e-mail message. It is more convenient for me to use Outlook. Help. ...

Text Box & Border Art
New Publisher 2003 user and getting quickly frustrated. I have setup a custom page size of 6.5" x 4.5" under Page Setup/Print Settings. I then created a text box of the same size, and filled it with the text I wanted. I then want to add a border to this text box. So, I right click on the text box, and choose Border Art under the Colors and Lines tab, and select the border "Twisted Lines" and hit apply. All is good. Now comes the trouble. I hit print preview and about 1/8" around the entire page is cutoff. All the margins are set to 0". I'm at a ...

Find two different, unknown, names in list
I have a football spreadsheet with different picks listed in columns. For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc. How can I have both names displayed at the bottom of the column: Ala and Tex? The first name is easy, simply select the top pick in the column. The second pick is the "other" name. How can this be done? Thanks, Ed M. On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote: > I have a football spreadsheet with different picks listed in columns. > For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc. > How...

Find and replace #3
I have a table with 6 columns, A to F. Column A contains unique item numbers. Column B contains descriptions of those items. Columns C-F contain a random listing of the numbers from column A. I want to replace the random listings of items in C-F with the descriptions of the items from column B. There are 1300 items. Can I do this without running 1300 Find and Replaces? I would be happy with four new columns added that have the descriptions for the random items in C-F in the same order (not exactly a replace). Thanks! Al -- ovid962 -----------------------------------------------------...