Solved: Search for String in table loops forever

[fredlo2010]

Hello,

I am a VBA beginner and I am having some problems with my macro. The sub is supposed to search a determined text string and if its within a table then move the selection back twice to the first column enter some text and then move to the next column and do the same. It is supposed to do this throughout the whole document.

The issue I am having is that it finds the text and it [performs the actions but it gets stuck in the first finding instead of moving to the next occurrence.



Sub thrtee()

Dim sText As String
Dim bmRange As Range

'Complete Table Header

sText = "List Price"
Selection.Find.ClearFormatting
With Selection.Find
.Text = sText
.Wrap = wdFindStop
End With
Do While Selection.Find.Execute
If Selection.Information(wdWithInTable) Then
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.TypeText Text:="Units"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="Description"

End If
Loop
End Sub




I have attached a document for a better understanding http://dl.dropbox.com/u/30987064/Units.docm


Thank you so much. Bellow there are some images with what I am trying to achieve.

http://dl.dropbox.com/u/30987064/Before.jpg"]http://dl.dropbox.com/u/30987064/Before.jpg

http://dl.dropbox.com/u/30987064/After.jpg"]http://dl.dropbox.com/u/30987064/After.jpg

[Tinbendr]

While most beginners use Selection, do yourself a favor and move to Ranges. While you can't get away from Selection, as my code shows, you can really curtail it's use. This leads to faster code.

This seems to work.

Sub FillInTitles()

Dim aDoc As Document
Dim Rng As Range
Dim SearchString As String

Set aDoc = ActiveDocument
Set Rng = aDoc.Range

SearchString = "List Price"

Do
With Rng.Find
.Text = SearchString
.Forward = True
.MatchWildcards = False
.MatchWholeWord = False
.Format = False
.Wrap = wdFindStop
.Execute
End With

If Rng.Find.Found Then
Rng.Select
TblNum = aDoc.Range(0, Selection.Tables(1).Range.End).Tables.Count
aDoc.Tables(TblNum).Cell(Rng.Information(wdEndOfRangeRowNumber), 1).Range.Text = "UNITS"
aDoc.Tables(TblNum).Cell(Rng.Information(wdEndOfRangeRowNumber), 2).Range.Text = "DESCRIPTION"
End If

Loop Until Not Rng.Find.Found

Selection.Collapse 0

End Sub

[fredlo2010]

Thanks for the advise, The truth is that I don't understand what you are telling me. It is hard to get some concepts; specially when there is no programming background. I even bought the book Word Macros from O'Reilly, but doesn't help much. I have to say I am very happy with what I have achieved ( it might not the most reliable code ever) but it works. VBA does 80% of my office job and I am more than happy with that :) Maybe you could suggest good literature?

OK, now to the problem. When I used the code, it gave me an error saying that variable TblNum was not declared. I declared it as Range (I am not sure if it was ok) and then I get another error,

http://i47.tinypic.com/90cdfm.jpg

Thanks for the help

[fumei]

An alternative...Sub Addtext()
Dim r As Range
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(findtext:="List Price", Forward:=True) = True
If r.Information(wdWithInTable) Then
With r
.Rows(1).Cells(1).Range.Text = "UNITS"
.Rows(1).Cells(2).Range.Text = "DESCRIPTION"
.Collapse 0
End With
End If
Loop
End With
End Sub
Explanation.

1. declare the range object
2. set the range object
3. Execute range object Find
4. IF the Found is in a table
5. make the first cell of the row the Found (range) is in "UNITS"
6. make the second cell of the row the Found (range) is in "DESCRIPTION"
7. collapse the range so it can continue
8. loop 4 to 7 until there is nothing to search

[fumei]

Word Macros from O'Reilly

I have never found them to be all that good from a newbie POV.

Word 2000 Developer's Handbook (Sybex)
VBA Developer's Handbook (Sybex)

while a little outdated (but NOT from a basic learning POV), are good. Try and find them in a secondhand store.

[fredlo2010]

Oh wow it works perfectly.. a little problem though "UNITS" and "DESCRIPTION" are supposed to be in sentence case; I will have to figure something out for that ( :) joking)

Thanks for the books i will try to get those and see if i have better luck.

[fumei]

Do not get too messed up that you got an error with: If Rng.Find.Found Then
Rng.Select
TblNum = aDoc.Range(0, Selection.Tables(1).Range.End).Tables.Count
aDoc.Tables(TblNum).Cell(Rng.Information(wdEndOfRangeRowNumber), 1).Range.Text = "UNITS"
aDoc.Tables(TblNum).Cell(Rng.Information(wdEndOfRangeRowNumber), 2).Range.Text = "DESCRIPTION"
End IfThe potential for error is rather high.

aDoc.Range(0, Selection.Tables(1).Range.End).Tables.Count
returns a NUMBER (not a range). Any time you see Count, it is a number. In this case it is the NUMBER of tables in the declared range (aDoc.Range(0, Selection.Tables(1).Range.End).

The assumption is that there is going to be only one, which is likely. However, using that number with:
aDoc.Tables(TblNum)will - again likely - mean using Table(1). In other words, if there are multiple tables in the document, they will NOT get processed.

[fumei]

"a little problem though "UNITS" and "DESCRIPTION" are supposed to be in sentence case; "

make it whatever you want..Rows(1).Cells(1).Range.Text = "Units"

[fredlo2010]

Well I was thinking about the count of tables. The example i have there is a single table. I will split it into separate tables later on.

By the way is there a way to add bookmarks to tables and then sort them. I know you guys have helped me a lot and I don't wanna push it. But maybe i can research about it later. This is the only part that I will missing after a few years trying to get it right.

Maybe you guys have a better idea on how i should approach it.

I am thinking add a numerical even bookmark to the first tables and then an odd sequence to the second tables; and later sort numerically.

http://dl.dropbox.com/u/30987064/Original.docx

http://dl.dropbox.com/u/30987064/Final.docx

One last thing, is there a way to mark this threat as solved? I cannot find it.

Thank a lot for the help

:) :) :) :) :) :) :) :) :) :) :) :)

[fredlo2010]

"a little problem though "UNITS" and "DESCRIPTION" are supposed to be in sentence case; "

make it whatever you want..Rows(1).Cells(1).Range.Text = "Units"


I know :) it was a joke. Thanks again for the help

[fumei]

Not sure what you mean by "sort" bookmarks. Perhaps start a new thread.

[Tinbendr]

As Gerry correctly stated, TblNum is a number.

In the first question and in the example, you weren't clear about whether there were multiple tables, I tried to anticipate. You could change it to:
aDoc.Tables(1).Range.Text = "Units"
In regard to books, I like anything by Guy Hart-Davis.

I also still have Writing Word Macros by Steven Roman 1999 sitting on my desk. It's very dated now, but the sections on Ranges and Tables are still valid.

Assign Bookmarks to tables and sort them? Hmm, interesting concept. Gerry is right, start a new thread.

Marking thread solved. Look at top of message box. Thread Tools.

[fredlo2010]

An alternative...Sub Addtext()
Dim r As Range
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(findtext:="List Price", Forward:=True) = True
If r.Information(wdWithInTable) Then
With r
.Rows(1).Cells(1).Range.Text = "UNITS"
.Rows(1).Cells(2).Range.Text = "DESCRIPTION"
.Collapse 0
End With
End If
Loop
End With
End Sub
Explanation.

1. declare the range object
2. set the range object
3. Execute range object Find
4. IF the Found is in a table
5. make the first cell of the row the Found (range) is in "UNITS"
6. make the second cell of the row the Found (range) is in "DESCRIPTION"
7. collapse the range so it can continue
8. loop 4 to 7 until there is nothing to search


Fumei,

I tried to use tour code to fix something but i didn't get it to work. The concept is the same look for a text string and if you find it delete the cell 1 and cell 2 in the table header. Instead it deletes from the line it finds the text on.

Any ideas?




'CLEANINGUP THE "UNITS" AND DESCRIPTION" FROM THE SALES HEADER

Dim r As Range
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(findtext:="Sale", Forward:=True) = True
If r.Information(wdWithInTable) Then
With r
.Rows(1).Cells(1).Range.Text = ""
.Rows(1).Cells(2).Range.Text = ""
.Collapse 0
End With
End If
Loop
End With

[fredlo2010]

As Gerry correctly stated, TblNum is a number.

In the first question and in the example, you weren't clear about whether there were multiple tables, I tried to anticipate. You could change it to:
aDoc.Tables(1).Range.Text = "Units"
In regard to books, I like anything by Guy Hart-Davis.

I also still have Writing Word Macros by Steven Roman 1999 sitting on my desk. It's very dated now, but the sections on Ranges and Tables are still valid.

Assign Bookmarks to tables and sort them? Hmm, interesting concept. Gerry is right, start a new thread.

Marking thread solved. Look at top of message box. Thread Tools.


Here is the link for the new in case you guys wanna take a look at it.

http://www.vbaexpress.com/forum/showthread.php?p=266003#post266003

Thanks for all the help. :)

[fumei]

"Instead it deletes from the line it finds the text on."

Of course it does.¸ That is what

With r
.Rows(1).Cells(1).Range.Text = ""

does. The Row it finds the text on. If you want to delete some OTHER place you must tell it that.