Help, FNR Wildcards Amounts in tables Cells alone

Cendrinne · #1 04-12-2021, 11:58 AM

I need Help again, Pro's

Well insights!

I know how to do a wildcard research. And I know that in cells, it must ends by:
Chr(7) + Chr(13)

So why can't I find in 1 or All Tables Cells, having only amounts of money, with or without $ sign, for all instance of:
[0-9, $]{1;} + Chr(7) + Chr(13) ?

Doesn't do anything. I've tried to find the Chr codes for numbers from 0-9, and tested in one cell with the number 2 so Chr(50), it didn't do anything.

I've tried as well, Chr(50) & Chr(7) + Chr(13) vs Chr(50) + Chr(7) + Chr(13) [& vs + after the (50), didn't do anything].

So can it be done? If so, what am I doing wrong?

Cendrinne

macropod · #2 04-12-2021, 02:49 PM

Word's Find tools don't support the finding of table end-of-cell or end-of-row markers.

Cendrinne · #3 04-12-2021, 03:41 PM

So my logic was ok, it's word, that won't do what I ask

Thank you so much Paul. It was driving me crazy

Cendrinne

Cendrinne · #4 04-12-2021, 03:52 PM

Quote:

Originally Posted by macropod

Word's Find tools don't support the finding of table end-of-cell or end-of-row markers.

I've just remember, I do have plenty of macros, that a wonderful person from this forum helped me with, to find $ or % symbols alone in a cell. I've applied it to one or all tables.

Click on the macro, finds it/them, then delete thems all.

But it doesn't do it for numbers as a wildcards, neither, only a number.

Hummmmmmmm

macropod · #5 04-12-2021, 04:00 PM

Wildcard Find does work for any kind of cell content, including numbers and paragraph breaks - just not for table end-of-cell or end-of-row markers. For example a wildcard Find for <[0-9]@> will find any plain number of any length anywhere in the document.

Cendrinne · #6 04-12-2021, 07:47 PM

Hello, Paul,
I thought of what you've said, and found a solution. Maybe not the best, but it works.

I've created a few FNR (Find and Replace), and ask to put the values with the color Blue.
Before I delete, I want to see what it Finds.

I had to put back the black or Automatic font colors on areas I didn't want the macro to touch the numbers, so I only left the ones in Blue that I was OK it's deletes.

Would you recommend another solution, or is mine OK?

Thanks for your input, it's makes me think of what I could do

Cendrinne

macropod · #7 04-12-2021, 08:07 PM

Since you haven't posted any code, or said anything about what you want to do with the found numbers (or anything else you're trying to find in tables), it's impossible to know for sure what the best way is of handling the task.

Cendrinne · #8 04-13-2021, 06:12 PM

Quote:

Originally Posted by macropod

Since you haven't posted any code, or said anything about what you want to do with the found numbers (or anything else you're trying to find in tables), it's impossible to know for sure what the best way is of handling the task.

The thing is that it's a very long script. I've tried to combine them, but it didn't always work.

*******************************
So part 1 = Find & Replace the values below in tables, and put them in blue:

Code:

Application.ScreenUpdating = False
Dim aTbl As Table
For Each aTbl In ActiveDocument.Tables
  With aTbl.range.Find
  
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "[$]{1}[0-9,.]{1;}"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "[$\(]{2}[0-9,.]{1;}"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "([0-9]{1;})"
      .Replacement.Text = "\1"
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "([0-9,.]{2;})"
      .Replacement.Text = "\1"
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "([\(][0-9]{1;}[\)])"
      .Replacement.Text = "\1"
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "([\(][0-9,.]{2;}[\)])"
      .Replacement.Text = "\1"
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "(^=){1}"
      .Replacement.Text = "\1"
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "([\)])([\)])"
      .Replacement.Text = "\2"
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
  End With
'Next
Next aTbl

**********************************
Part 2, If found text in Blue in tables, then Delete

Code:

Application.ScreenUpdating = False
Dim aTbl As Table
For Each aTbl In ActiveDocument.Tables
  With aTbl.range.Find
  
    Selection.Find.ClearFormatting
    Selection.Find.Font.Color = wdColorBlue
    Selection.Find.Replacement.ClearFormatting
   'Selection.Find.Replacement.Font.Color = wdColorBlue
    With Selection.Find
      .Format = True
      .Forward = True
      .Wrap = wdFindStop
      .MatchWildcards = True
      .Text = "?"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .MatchWildcards = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
  
  End With
'Next
Next aTbl

***********************************

Please know that I dont have programming skills YET, but I manage with what I know.

I'm sure it could be improved, but since you wanted to see my scripts, here they are

Cendrinne

macropod · #9 04-13-2021, 07:25 PM

It seems to me your first macro could be reduced to:

Code:

Sub Demo1()
Application.ScreenUpdating = False
Dim Tbl As Table
For Each Tbl In ActiveDocument.Tables
  With Tbl.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Font.Color = wdColorBlue
    .Format = True
    .Forward = True
    .Wrap = wdFindStop
    .MatchWildcards = True
    .Text = "$[0-9,.]{1;}"
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
    .Text = "\($[0-9,.]{1;}\)"
    .Execute Replace:=wdReplaceAll
    .Text = "[0-9]{1;}"
    .Replacement.Text = "^&"
    .Execute Replace:=wdReplaceAll
    .Text = "[0-9,.]{2;}"
    .Execute Replace:=wdReplaceAll
    .Text = "\([0-9]{1;}\)"
    .Execute Replace:=wdReplaceAll
    .Text = "\([0-9,.]{2;}\)"
    .Execute Replace:=wdReplaceAll
    .Text = "^="
    .Execute Replace:=wdReplaceAll
    .Text = "\)\)"
    .Replacement.Text = ")"
    .Execute Replace:=wdReplaceAll
  End With
Next Tbl
Application.ScreenUpdating = True
End Sub

Similarly, the second macro could be reduced to:

Code:

Sub Demo2()
Application.ScreenUpdating = False
Dim Tbl As Table
For Each Tbl In ActiveDocument.Tables
  With Tbl.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Font.Color = wdColorBlue
    .Format = True
    .Forward = True
    .Wrap = wdFindStop
    .Replacement.Text = ""
    .Execute Replace:=wdReplaceAll
  End With
Next Tbl
Application.ScreenUpdating = True
End Sub

I see none of the 'it doesn't do it for numbers as a wildcards' issues to which you refer.

Guessed · #10 04-13-2021, 07:50 PM

Cendrinne

Edit: I see that Paul also tidied up the code while I was posting. I think the wdFindStop could be a problem as the range may collapse as you progress through the code. It is possibly better to use wdFindContinue if you are encountering issues.

You should be able to clean up your code considerably. You started by defining a range and then completely ignored it by using Selection instead. Try this cleanup on your Part 1 code and study it before applying the same principles to your Part 2

Code:

  Application.ScreenUpdating = False
  Dim aTbl As Table, rngTable As Range
  For Each aTbl In ActiveDocument.Tables
    Set rngTable = aTbl.Range
    With rngTable.Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Replacement.Font.Color = wdColorBlue
      .Format = True
      .Forward = True
      .Wrap = wdFindContinue
      .MatchWildcards = True
      
      .Text = "[$]{1}[0-9,.]{1;}"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
    
      .Text = "[$\(]{2}[0-9,.]{1;}"
      .Execute Replace:=wdReplaceAll
    
      .Text = "([0-9]{1;})"
      .Replacement.Text = "\1"
      .Execute Replace:=wdReplaceAll
        
      .Text = "([0-9,.]{2;})"
      .Execute Replace:=wdReplaceAll
      
      .Text = "([\(][0-9]{1;}[\)])"
      .Execute Replace:=wdReplaceAll
        
      .Text = "([\(][0-9,.]{2;}[\)])"
      .Execute Replace:=wdReplaceAll
        
      .Text = "(^=){1}"
      .Execute Replace:=wdReplaceAll
    
      .Text = "([\)])([\)])"
      .Replacement.Text = "\2"
      .Execute Replace:=wdReplaceAll
    End With
  Next aTbl

Another tip is to not be so liberal with your use of Application.ScreenUpdating = False
It is better to not do that until your code is fully debugged as it stops you from seeing what is going on as your macro runs and makes life difficult if the code fails at any point before you turn it on again.

Cendrinne · #11 04-18-2021, 12:19 PM

Quote:

Originally Posted by Guessed

Cendrinne

Edit: I think the wdFindStop could be a problem as the range may collapse as you progress through the code. It is possibly better to use wdFindContinue if you are encountering issues.

Another tip is to not be so liberal with your use of Application.ScreenUpdating = False
It is better to not do that until your code is fully debugged as it stops you from seeing what is going on as your macro runs and makes life difficult if the code fails at any point before you turn it on again.

First paragraph above, I always thought wdFind Stop was to have it START as of the cursor till the end of the document or range. Or else it will start again from the beginning of the document or range. Is that thinking wrong?

Second paragraph above, thank you, never thought of that.

Sorry for my tardy response, I am so busy, and put in so much hours and efforts of my time during the week, that I take a look of my former postings on the later part on the weekend.

Thank you for your insights and Scripts. I can't wait to try them and especially, ......get explanations as to why I should use Continue vs Stop

Cendrinne

Cendrinne · #12 04-18-2021, 12:33 PM

Hello, Paul, (Paul Edstein)

I thank you so much for your help and scripts. I can’t wait to try it ��

As I told Guessed, Sorry for my tardy response, I am so busy, and put in so much hours and efforts of my time during the week, that I take a look of my former postings on the later part on the weekend.

Thank you for your insights and help!!!!

Cendrinne

Guessed · #13 04-18-2021, 04:44 PM

Quote:

Originally Posted by Cendrinne

First paragraph above, I always thought wdFind Stop was to have it START as of the cursor till the end of the document or range. Or else it will start again from the beginning of the document or range. Is that thinking wrong?

FindStop does indeed stop at the end of the document. However, if the previous find has moved the range to somewhere down the document, lets assume the middle, then the NEXT find will start from there and only search to the end (hence not checking above the current range position). If any find gets a hit at the end of the file then every subsequent find will have nowhere to search.

Your choices are to reset the range before each find or simply get it to continue so that every search wraps back to the top and is performed on the entire document.

EDITED: Paul is correct, I got this wrong - ignore what I said above. In testing I couldn't reproduce what I was saying so either I've always been wrong or perhaps I've seen this long ago in earlier versions of Word or maybe I was working with the Selection object and single replaces.

macropod · #14 04-18-2021, 04:56 PM

Quote:

Originally Posted by Guessed

FindStop does indeed stop at the end of the document. However, if the previous find has moved the range to somewhere down the document, lets assume the middle, then the NEXT find will start from there and only search to the end (hence not checking above the current range position). If any find gets a hit at the end of the file then every subsequent find will have nowhere to search.

Your choices are to reset the range before each find or simply get it to continue so that every search is performed on the entire document.

The code I posted is unaffected by whatever point you're trying to make. You can demonstrate this quite easily by creating a 1-column table with, say, four rows, and numbering each cell as 4, 3, 2, 1, respectively, then running the code below (modified from post #9 above). All four numbers will be processed and turned blue.

Code:

Sub Demo1()
Application.ScreenUpdating = False
Dim Tbl As Table
For Each Tbl In ActiveDocument.Tables
  With Tbl.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Font.Color = wdColorBlue
    .Format = True
    .Forward = True
    .Wrap = wdFindStop
    .MatchWildcards = True
    .Replacement.Text = "^&"
    .Text = "1"
    .Execute Replace:=wdReplaceAll
    .Text = "2"
    .Execute Replace:=wdReplaceAll
    .Text = "3"
    .Execute Replace:=wdReplaceAll
    .Text = "4"
    .Execute Replace:=wdReplaceAll
  End With
Next Tbl
Application.ScreenUpdating = True
End Sub

Cendrinne · #15 04-18-2021, 07:38 PM

Ok I think I understood, so I can continue using Stop vs Continue.
Thanks so much

I love that we can all collaborate and help each other out! Gets our Gray matter working

Millions of thanks and have a super great week

Cendrinne

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Splitting Tables containing Merged Cells	bpike	Word VBA	7	10-28-2020 02:23 AM
How do I run code on all cells in all tables?	wido	Word VBA	2	09-04-2019 05:32 AM
Can I repeat or self-populate cells/tables?	pvh	PowerPoint	6	05-05-2015 03:21 PM
Tables in Powerpoint - Hiding Cells	manisfeld	PowerPoint	2	06-08-2014 08:17 PM
Shading in Cells in Word Tables	Cindy	Word Tables	1	01-04-2013 02:38 PM