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

mbcohn · #1 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 · #2 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).

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