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