find and replace a number that's always in a table and always adjacent to a cell with known text

mbcohn · #16 03-31-2021, 09:55 PM

Okay, that worked.

It turned out that sometimes there is a space after "published," and sometimes there isn't. So what I did is:

Code:

Case "Published "
              If .Cells(1).ColumnIndex = 1 Then
                .Cells(1).Next.Range.Text = "2 April 2021"
              End If
            Case "Published"
              If .Cells(1).ColumnIndex = 1 Then
                .Cells(1).Next.Range.Text = "2 April 2021"
              End If

Would you mind providing some guidance on what this means?

Code:

.Text = "[PR][eu][bv][ils]{3}[ehno]{2}[ Ndo]{1,3}"

macropod · #17 03-31-2021, 09:57 PM

It would be better to use:
Select Case Trim(.Text)
and:
Case "Published"

mbcohn · #18 03-31-2021, 10:08 PM

As you command it, so shall it be done.

Much like Bartles & James, I thank you again for your support.

macropod · #19 03-31-2021, 10:19 PM

Quote:

Originally Posted by mbcohn

Would you mind providing some guidance on what this means?

Code:

.Text = "[PR][eu][bv][ils]{3}[ehno]{2}[ Ndo]{1,3}"

Basically, that's a wildcard Find expression. The:
• [PR] says to find either P or R;
• [eu] says to find either e or u;
• [bv] says to find either b or v;
• [ils]{3} says to find any 3-letter combination consisting of i, l, and/or, s;
• [ehno]{2} says to find any 2-letter combination consisting of e, h, n, and/or o
• [ Ndo]{,1 3} says to find any 1- to 3-letter combination consisting of a space, N, d, and/or, o.
Whilst the character combination possibilities are almost endless, the reality is that, given the words used in the English language, such an expression is unlikely to Find anything other than 'Revision No' or 'Published' (with or without the trailing space for the latter).

mbcohn · #20 03-31-2021, 10:22 PM

Interesting. Why was that step necessary? Why can't we just skip to the Do While loop, where we start checking to see what's actually in the cell?

macropod · #21 03-31-2021, 10:24 PM

Generally speaking, using Find is quicker than looping through all rows.

mbcohn · #22 03-31-2021, 10:59 PM

But there were only four rows. Now if I want to change the code to have it search for more things, I have to play around with that expression.

Could you please show me how to do it with just a loop, so I'll know?

macropod · #23 04-01-2021, 05:16 AM

Quote:

Originally Posted by mbcohn

But there were only four rows. Now if I want to change the code to have it search for more things, I have to play around with that expression.

Well, that's a consequence of you not properly specifying your requirements from the outset...

Quote:

Originally Posted by mbcohn

Could you please show me how to do it with just a loop, so I'll know?

When I started out on this thread, I didn't even know which table in the document you're referring to or how the table(s) are structured - not even which column the data of interest might be in. All you originally said was:

Quote:

Originally Posted by mbcohn

I have a series of documents that all contain identical formatting.
Each has a table that contains a cell with the value "Revision No."

Given that I still don't know how many tables there are, you might use code like:

Code:

Dim Tbl As Table, r As Long
For Each Tbl In wdDoc.Tables
  With Tbl
    For r = 1 To .Rows.Count
      Select Case Trim(Split(.Cell(r, 1).Range.Text, vbCr)(0))
        Case "Revision No.": .Cell(r, 2).Range.Text = "2.5"
        Case "Published": .Cell(r, 2).Range.Text = "2 April 2021"
      End Select
    Next
  End With
Next

mbcohn · #24 04-01-2021, 10:07 AM

Sorry for not being more clear.

The number of tables in the document varies, but the changes that I want to make are always to the first table. always to the second column, and the table itself is never more than about five rows tall.

mbcohn · #25 04-01-2021, 01:04 PM

And now I'm getting a run-time error. I'm not sure why. I can't figure out what about the situation has changed. I went back through the thread and re-pasted every bit of code and every change to be sure, and I also moved the files from one location to another. I've also tried restarting Word, and my computer.

The error says: "Run-time error '5174': Sorry, we couldn't find your file. Was it moved, renamed, or deleted?"

The line of code that this error happens on is:

Code:

 Set wdDoc = Documents.Open(FileName:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)

I feel like this must be something happening on my end outside the program, but I can't imagine what it could be.

macropod · #26 04-01-2021, 02:40 PM

Code:

Set wdDoc = Documents.Open(FileName:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)

should be:

Code:

Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)

macropod · #27 04-01-2021, 02:42 PM

Quote:

Originally Posted by mbcohn

The number of tables in the document varies, but the changes that I want to make are always to the first table. always to the second column, and the table itself is never more than about five rows tall.

In which case:

Code:

Dim r As Long
  With wdDoc.Tables(1)
    For r = 1 To .Rows.Count
      Select Case Trim(Split(.Cell(r, 1).Range.Text, vbCr)(0))
        Case "Revision No.": .Cell(r, 2).Range.Text = "2.5"
        Case "Published": .Cell(r, 2).Range.Text = "2 April 2021"
      End Select
    Next
  End With

mbcohn · #28 04-01-2021, 02:53 PM

I made your correction to "Set wdoc" (adding the slash), but now I get "Runtime error 5, invalid procedure call or argument" on:

Code:

  FileName = Mid(myPath, InStrRev(myPath, "") + 1, _
   InStrRev(myPath, ".") - InStrRev(myPath, "") - 1)

mbcohn · #29 04-01-2021, 04:29 PM

So, I see that the slashes are missing from that code I just entered above. I added them back, and am now getting the error here (invalid procedure call or argument).

Code:

'Store Information About Word File
   myPath = ActiveDocument.FullName
   CurrentFolder = ActiveDocument.Path & "\"
   FileName = Mid(myPath, InStrRev(myPath, "\") + 1, _
    InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)

No idea what's going on. Could the code somehow be changing when it runs?

macropod · #30 04-01-2021, 06:10 PM

Is there any reason for not using the filename code I provided in post #4?

And no, the macro is not deleting the path separators. Any error there is in your copying/pasting of the code - most likely when copying from an on-line post.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find and Replace rows in a table based on bold text.	OfficeAssociate99	Word VBA	2	07-26-2017 06:32 AM
Select Cell Text to paste into Find/Replace	CBarry	Word VBA	2	02-16-2017 04:37 AM
Increase number in cell, based on value in adjacent cell	scottyb	Excel	3	02-02-2017 03:51 AM
VBA Table – Search All Tables - Find & Replace Text in Table Cell With Specific Background Color	jc491	Word VBA	8	09-30-2015 06:10 AM
Word VBA Find Table Text Shading Colour and replace with another	QA_Compliance_Advisor	Word VBA	10	09-19-2014 08:36 AM