#1
|
||||
|
||||
Help, FNR Wildcards Amounts in tables Cells alone
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 |
#2
|
||||
|
||||
Word's Find tools don't support the finding of table end-of-cell or end-of-row markers.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
ah Thank you Paul
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 |
#4
|
||||
|
||||
Wait, yes Word does it!!!!!
Quote:
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 |
#5
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
||||
|
||||
OK, I've tested something, and found what could work for me
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 |
#7
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
||||
|
||||
I see, well
Quote:
******************************* 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 |
#9
|
||||
|
||||
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 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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
||||
|
||||
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 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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#11
|
||||
|
||||
Responding to your Stop vs Continue comment....
Quote:
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 |
#12
|
||||
|
||||
Thank you :)
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 |
#13
|
||||
|
||||
Quote:
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#14
|
||||
|
||||
Quote:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
||||
|
||||
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 |
Tags |
cells, helpme, wildcards |
|
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 |