Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Find a string in a column

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Francis Hookham - 21 Sep 2007 21:38 GMT
Column E has strings identifying items. The number of blank cells between
entries varies. (See below.)

I need to insert a new item, say D01-006, or D01-004b. So I need to find the
row number immediately above the next ID in order to insert the rows the new
item will occupy.

In order to insert D01-006 how can I find the row number immediately above
D02-001, or the row above D01-005 to insert D01-004b?

Francis Hookham

D01-001

-

D01-002

-

D01-003

-

D01-004

-

D01-004a

-

D01-005

-

D02-001

-

D02-002

-

D02-003
Shauna Kelly - 22 Sep 2007 04:55 GMT
Hi Francis

The following will find the first cell in column E that contains the text of
interest. It assumes that the table is entirely regular, with no merged
cells. You'll need to add error checking appropriate to the circumstances.

Sub FindAStringInColumnE()

Dim oTable As Word.Table
Dim colE As Word.Column
Dim oCell As Word.Cell
Dim lngRowWeFound As Long
Dim sMyText As String

   'Get a reference to our table
   Set oTable = Selection.Tables(1)

   'Identify the text we want to find
   sMyText = "D02-001"

   'Identify the column to search
   Set colE = oTable.Columns(5)

   'Go through each cell in our column...
   For Each oCell In colE.Cells

       'See if our text is found in that cell
       If InStr(1, oCell.Range.Text, sMyText, vbTextCompare) > 0 Then

           'If it's found, then record the row where
           'we found our text
           lngRowWeFound = oCell.RowIndex

           'quit while we're ahead!
           Exit For
       End If
   Next oCell

   If lngRowWeFound > 0 Then
       MsgBox "We found " & sMyText & " in row " & CStr(lngRowWeFound)
   Else
       MsgBox "We could not find " & sMyText & " in the table"
   End If

End Sub

Hope this helps.

Shauna Kelly.  Microsoft MVP.
http://www.shaunakelly.com/word

> Column E has strings identifying items. The number of blank cells between
> entries varies. (See below.)
[quoted text clipped - 41 lines]
>
> D02-003
Francis Hookham - 22 Sep 2007 09:44 GMT
Many thanks Shauna - looks good and I'll get to it this evening (UK
summertime = GMT+1)

One associated question - this is a door/ironmongery prgm for architec son -
I can use
   iDoorRow= Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row
to find the row in which the latest door sDoorID is

How can I increment text  D02-001  to  D02-002?
so can then prompt the user to confirm that as the next door or to type in a
totally different door, maybe the next floor D03-001 or an inserted door
D01-003a

Francis

> Hi Francis
>
[quoted text clipped - 92 lines]
>>
>> D02-003
Helmut Weber - 22 Sep 2007 09:58 GMT
Hi Francis,

>I can use
>    iDoorRow= Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row
>to find the row in which the latest door sDoorID is

no, not in Word, that is Excel-code.

You were asking in a group for Word-programming.
Make sure first to know which application you are using. ;-)

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Francis Hookham - 22 Sep 2007 15:54 GMT
Apologies everyone - I'm in the wrong group - for these XL queries I should
be in
microsoft.public.excel.programming
to which I'll transfer.

Francis

> Hi Francis,
>
[quoted text clipped - 6 lines]
> You were asking in a group for Word-programming.
> Make sure first to know which application you are using. ;-)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.