Code to find Text in a column of a table?

[Jade]

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.

 

[Helmut Weber]

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Helmut Weber]

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Jade]

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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.

 

[Helmut Weber]

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
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

' 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
.Cell(rownum, 4).Range = txtFax.Text
.Cell(rownum, 5).Range = txtEmail.Text
End With
.Save
.Close
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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Jade]

Hi Jade,








' 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


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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

- 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 (e-mail address removed)



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.

 

[Helmut Weber]

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

Company Contact Tel
Fax Email
Kraft Ellen Smith 212-333-4444
212-333-6666 (e-mail address removed)



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

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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