Code to find Text in a column of a table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jade - 04 Mar 2007 00:50 GMT

Hello,

Im using Word 2003. I have a userform that uses the data in a word
table to populate a combobox. If the user chooses the "Add"
commandbutton then it will add the data in the textboxes to the last
row in the word table. I would like to modify it, if possible, so
that it can look to see if the name is already in the table and if so
then it would populate that row with the new/updated information the
user provides instead of creating a new line at the end of the table.
In excel you can use the Find function to do that for a range;
however, not sure if it can be done in Word.

Bottomline....is it possible to search a table and replace the text in
the table when it finds a match in a column?

Thank you.

Reply to this Message

Helmut Weber - 04 Mar 2007 09:17 GMT

Hi Jade,

like this:

Sub Macro9AA()
MsgBox FoundInTableCol(1, 2, "testx")
End Sub
' -------------------------------------------
Public Function FoundInTableCol(t As Long, c As Long, s As String) As
Boolean
' t = a table
' c = a column
' s = a string
Dim r As Range
Set r = ActiveDocument.Tables(t).Range
With r.Find
.Text = s
.MatchWholeWord = True
.MatchCase = True
If .Execute And r.Information(wdEndOfRangeColumnNumber) = c Then
FoundInTableCol = True
End If
End With
End Function

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Helmut Weber - 04 Mar 2007 09:40 GMT

hmm...

if that hepls you, its alright,

however, if the text to search for is to be found
elsewhere in the table, at a position before it appears
in column c, then the function would stop searching further
and return "false".

Improved version:

Public Function FoundInTableCol(t As Long, c As Long, s As String) As
Boolean
' t = a table
' c = a column
' s = a string
Dim r As Range
Set r = ActiveDocument.Tables(t).Range
With r.Find
.Text = s
.MatchWholeWord = True
.MatchCase = True
While .Execute ' <<<
If r.Information(wdEndOfRangeColumnNumber) = c Then
FoundInTableCol = True
Exit Function
End If
Wend
End With
End Function

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Jade - 05 Mar 2007 00:25 GMT

> hmm...
>
[quoted text clipped - 34 lines]
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"

Hi Helmut

Thank you for the feedback...I'm very much a novice and appreciate
your help just trying to figure out how to combine it or implement it
to my code...

Here's what I have .... I realize the change will come in the
section....With Log...and the match should be in the cmbContact which
lies in column 2..however if there is a match then the row where data
is found has to be repopulated.

Sub Add()
Dim Log As Word.Document
Dim logrange As Range, logtable As Table, rownum As Integer
With Word.Application
Set Log = Documents.Open(File)
End With

With Log
Set logtable = Log.Tables(1)
logtable.Rows.Add
rownum = logtable.Rows.Count
With logtable
.Cell(rownum, 1).Range = cmbComp.Text
.Cell(rownum, 2).Range = cmbContact.Text
.Cell(rownum, 3).Range = txtTel.Text
.Cell(rownum, 4).Range = txtFax.Text
.Cell(rownum, 5).Range = txtEmail.Text
End With
.Save
.Close
End With
End Sub

Again, I appreciate your help.

Reply to this Message

Helmut Weber - 05 Mar 2007 15:34 GMT

Hi Jade,

>Thank you for the feedback...I'm very much a novice and appreciate
>your help just trying to figure out how to combine it or implement it
>to my code...

>Here's what I have .... I realize the change will come in the
>section....With Log...and the match should be in the cmbContact which
[quoted text clipped - 14 lines]
> With logtable
> .Cell(rownum, 1).Range = cmbComp.Text

' so you want to check whether cmbContact.Text
' is to be found in column 2?
if FoundInTableCol(1, 2, cmbContact.Text) then
' your code
endif

> .Cell(rownum, 2).Range = cmbContact.Text
> .Cell(rownum, 3).Range = txtTel.Text
[quoted text clipped - 5 lines]
> End With
>End Sub

Public Function FoundInTableCol(t As Long, c As Long, s As String) As
Boolean
' t = a table ' table 1 in your case
' c = a column ' column 2 in your case
' s = a string
Dim r As Range
Set r = ActiveDocument.Tables(t).Range
With r.Find
.Text = s
.MatchWholeWord = True
.MatchCase = True
While .Execute ' <<<
If r.Information(wdEndOfRangeColumnNumber) = c Then
FoundInTableCol = True
Exit Function
End If
Wend
End With
End Function

Instead of restricting to search to a column,
I search the whole table and check, if found,
whether the found item is in column 2.
In that case, the function's return value
is set to true and the function is left.

Which is good enough, I think, for small tables.

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Jade - 06 Mar 2007 13:43 GMT

> Hi Jade,
>
[quoted text clipped - 75 lines]
>
> - Show quoted text -

Hi Helmut,

Please help....maybe i'm doing something wrong but it doesn't seem to
be working...if I change the number of a person or the email it
doesn't reflect that change on the table...One question I meant to ask
is with your code if the person's last name changes would it pick that
up?

table looks like

Company Contact Tel
Fax Email
Kraft Ellen Smith 212-333-4444
212-333-6666 esmith@kraft.com

Private Sub Add()
Dim Log As Word.Document
Dim logrange As Range, logtable As Table, rownum As Integer
With Word.Application
Set Log = Documents.Open(FileName)
End With

With Log
Set logtable = Log.Tables(1)
logtable.Rows.Add
rownum = logtable.Rows.Count
If FoundInTableCol(1, 2, cmbContact.Text) Then
With logtable
.Cell(rownum, 1).Range =cmbComp.Text
.Cell(rownum, 2).Range =cmbContact.Text
.Cell(rownum, 3).Range = txtTel.Text
.Cell(rownum, 4).Range = txtFax.Text
.Cell(rownum, 5).Range = txtEmail.Text
End With
End If
.Save
.Close
End With
End sub

Any help you can offer me would be so great.

Reply to this Message

Helmut Weber - 06 Mar 2007 19:43 GMT

Hi Jade

>Please help....maybe i'm doing something wrong but it doesn't seem to
>be working...if I change the number of a person or the email it
>doesn't reflect that change on the table...One question I meant to ask
>is with your code if the person's last name changes would it pick that

>table looks like
>
[quoted text clipped - 27 lines]
> End With
>End sub

in general, the function checks whether the text from
your combobox appears in column 2 in the table, exactly
as it was returned from the combobox (matchcase, matchwholeword).
If you change telephone or mail-address, the function
returns "false", as neither of them appear in column 2,
and your code does nothing at all.

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message