#1
|
|||
|
|||
How to do row specific macro only if column criteria is met
Good day,
I would like to know how to do a macro that occurs only on a specific row IF a that row's first column has the string "Q.J." inside it. For example, if we have this table: Column 1 Column 2 Column 3 2021-Q.J. Short-Case-Name-c. Doe-Clapton 2021-A.K. Short-Case-Name-v. Doe-Clapton-french 2020-T.J. Short-Case-Name-v. Doe-Clapton-english I would like the macro to find for the string "Q.J." in the entire table's first column. If it finds "Q.J.", I would like to have the following changes applied to that specific row only: 1. replace "Q.J." with "J.Q." 2. replace the string "c." in column with "v." 3. insert the string "english in column 3 This would the result: Column 1 Column 2 Column 3 2021-J.Q. Short-Case-Name-v. Doe-Clapton-english 2021-A.K. Short-Case-Name-v. Doe-Clapton-french 2020-T.J. Short-Case-Name-v. Doe-Clapton-english I've also attached a screenshot of the real world document where I will be using the macro. I hope you could help me on this one. Thanks! |
#2
|
||||
|
||||
Typically when people put in requests like this without providing a realistic sample document, the initial solutions don't fit because of things that aren't said. If you provided a sample doc you would get a much better solution since we can test the macro without having to make assumptions.
The specific questions I would need answered first are:
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
||||
|
||||
For what the OP described, taking the 3 to mean the 3rd highlighted column:
Code:
Sub Demo() Application.ScreenUpdating = False Dim Rng As Range With ActiveDocument.Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "Q.J." .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = True End With Do While .Find.Execute If .Information(wdWithInTable) = True Then If .Cells(1).ColumnIndex = 1 Then .Text = "J.Q." Set Rng = .Rows(1).Range With Rng.Cells(5).Range.Find .Text = "v." .Replacement.Text = "c." .Wrap = wdFindStop .Execute Replace:=wdReplaceOne End With Rng.Cells(10).Range.InsertAfter " english" End If End If .Collapse wdCollapseEnd Loop End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Quote:
1. The doc contains only a single large table and has no merged cells. 2. The disparity in columns is simply because I was using a simplified example to avoid bothering other people with unnecessary data. 3. Yes, all these macros should only take effect ONLY IF the first condition (That "Q.J." string is detected on first column) is met and will only be applied to that particular row only. 4. Yes, the " c." should only be replaced if the initial condition is met (That "Q.J." string is detected on first column) and if the " c." is a separate entity with a space before it. For example, in the string "(CIUSSS MCQ) c. R.F." To review, using my real world attached document's real column names: I would like the macro to set an initial condition: in JQ column – search "Q.J." and replace with "J.Q." and in that row's short case name column, change the "c." to "v." also, again in that row, insert the word “English” in the judge(s) field column. I hope this answers your question. Apologies for taking your time. This is part of my work and I've already sorted out other macro functions which are outside of the original post. So these are the only ones I'm having trouble with. Thanks again. |
#5
|
|||
|
|||
sir you are a genius. the macro now works on my test document. Will be running this on other documents for verification. How were you able to come up with it so fast? Huge thanks once again.
|
#6
|
||||
|
||||
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
table macro condition, word table macro |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Looking for a macro for word count in one specific column of table, only rows with white background | mobj | Word VBA | 2 | 10-17-2019 03:09 AM |
How can I return Vlookup only if Specific Criteria is met in 1 column of the Vlookup Array | EcommDOC | Excel | 7 | 01-22-2018 11:00 AM |
How can I save all attachments in a folder with specific criteria? | terrymac | Outlook | 1 | 11-12-2015 06:55 AM |
Excel VBA Macro - Deleting Specific Data based on criteria | MD011 | Excel Programming | 3 | 12-10-2014 02:15 AM |
Deleting rows with specific criteria | joflow21 | Excel | 9 | 11-22-2013 12:10 PM |