Solved: Code to find text in table (loop each cell)

[jimboy]

Hi - I need some code to check every cell in a table to see if it contains some text, something like this;

for i= 1 to activedocument.tables(5).cells
if i="Look for this text" then...
Next i
or, can I use Find in the table?


When I have found the text I want to find out how many letters are in that cell (using len(activedocument.tables(5).cell(irow,icolumn)))
Thanks

[Killian]

Ahh... if only cells was a collection of the cells in a table - the would be useful.
If you have a lot of stuff to do with the cells and want their properties, it would be good to create a collection and add them, but thats probably overkill.
I guess you could use FInd, but since you want the row and column indexes, you might as well loop throughDim r As Long
Dim c As Long

With ActiveDocument.Tables(5)
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
If .Cell(r, c).Range.Text Like "*test*" Then
MsgBox "There are " & Len(.Cell(r, c).Range.Text) & _
" characters in row " & r & " column " & c
End If
Next c
Next r
End WithA couple of points to note:
Len(.Cell(r, c).Range.Text) will include spaces and non-printing characters so you might need to strip those out and/or check for aplhanumerics only

when comparing strings, you probably want to make everything the same case (with UCase or LCase) so there's nothing missed

[TonyJollans]

Hi jimboy,

Welcome to VBAX!

You can loop through the cells or you can use Find - it's up to you. I would think it depends how many occurrences you expect to find. It is easy enough to get the row and column indices of a cell once you have identified it, if that's what you want - or the number of characters, or whatever, more or less.


K - TableRef.Range.Cells is a collection of the Cells in a Table.

[fumei]

If you are just wanting to do some action to each cell - with a check to see if it is NOT "" (empty)- then you do not need find. Nor, from what I can see,do you need the row/column numbers. Unless you really need to....why bother getting the row/column numbers. You want to check each cell...then make a Cell object and look at it. Use:

Sub EachCellText()
Dim oCell As Word.Cell
Dim strCellString As String
For Each oCell In ActiveDocument.Tables(1).Range.Cells
strCellString = Left(oCell.Range.Text, _
Len(oCell.Range.Text) - 1)
If strCellString <> "" Then
' do what ever you want to do
' with the string
End If
Next
End Sub

You need to make the string variable Len(range.Text) - 1 to strip off the end-of-cell marker (paragraph mark).

As was also noted, you may need to also do some Trim to strip off any other blank spaces.

As Tony pointed out, the collection that holds the cells is tableRef.Range.Cells

[MOS MASTER]

This kb has a simular loop but a different way to check for Empty (That is only end of cell marker) cells:
http://vbaexpress.com/kb/getarticle.php?kb_id=631

And there are of course more ways to find that information. :whistle:

[jimboy]

Thanks very much.
The table has two rows with 2 columns and then one row with 4 columns, so I was getting an error, I used this to get around it...
With ActiveDocument.Tables(i)
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
If .Cell(r, c).Range.Text Like "*User notified on:*" Then
If Err.Number <> 5941 Then
' MsgBox "There are " & Len(.Cell(r, c).Range.Text) & _
" characters in row " & r & " column " & c

If Len(.Cell(r, c).Range.Text) < 25 Then
MsgBox ("Less then 20 - " & Len(.Cell(r, c).Range.Text))

Else
MsgBox ("More than 20 - " & Len(.Cell(r, c).Range.Text))
End If
Else

End If
End If
Err.Number = 0
Next c
Next r
End With
Thanks again, I can see me coming back to this site :bow:

[MOS MASTER]

Glad to you've found your sollution!

Don't forget to mark your thread solved. :*)

[fumei]

I just do not understand......I wonder why I bother to post.....

Of course you would get an error, as you are using row and column numbers of the cells.

My code does not. Did you read it? Apparently not. Here it is again....but with your logic added to the part where I wrote where you could put your logic.

Sub EachCellText()
Dim oCell As Word.Cell
Dim strCellString As String
For Each oCell In ActiveDocument.Tables(1).Range.Cells
strCellString = Left(oCell.Range.Text, _
Len(oCell.Range.Text) - 1)
If strCellString <> "" Then
If Len(strCellString) < 25 Then
Msgbox "Less than 25."
Else
Msgbox "25 or more."
End If
End If
Next
End Sub

The code could not care less about 2 row, then 4 rows, because it just looks at the each cell - regardless of rows.

I am curious....am I being very dense and not writing well?

[geekgirlau]

Hey Gerry,

I can't speak for anyone else but I loved your code - streamlined, elegant, efficient use of the object model. However one of the properties of VBA is that there is always more than one way to skin a cat, and people will often choose the method that they know and feel most comfortable with over a more efficient but less familiar method.

Keep your head held high, keep posting and go and have some more coffee - are you really writing code that early in the morning??

[fumei]

Yeah....I was outta line.

Yes I really write code that early in the morning...among other things. I have a sleep disorder which means if I get two hours of sleep a day, I am lucky. That is a good night. So I have to do SOMETHING....

One of the reasons I became an astronomer.

My apologies (again) for my rant. I get tired and cranky......

[fumei]

And geekgirlau....thanks. Coming from you (I always read your posts carefully) that is real compliment.

[geekgirlau]

Hey, you're talking to the mother of a teething toddler - I know all about tired and cranky.

[fumei]

Yes you do. Bu at least it is for good cause, it WILL pass eventually, and you will have the incredible joy of helpful a new human being be the best they can be.

Congratulations!

[MOS MASTER]

Yes you do. Bu at least it is for good cause, it WILL pass eventually, and you will have the incredible joy of helpful a new human being be the best they can be.

Congratulations!

Amen to you buddy! :hi: