Find a string in a column

[Francis Hookham]

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]

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

 

[Francis Hookham]

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

 

[Helmut Weber]

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. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Francis Hookham]

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