Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-23-2021, 03:58 AM
doeclapton doeclapton is offline How to do row specific macro only if column criteria is met Windows 7 64bit How to do row specific macro only if column criteria is met Office 2010
Novice
How to do row specific macro only if column criteria is met
 
Join Date: Jul 2019
Posts: 8
doeclapton is on a distinguished road
Default 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!
Attached Images
File Type: png macrohelp.png (56.6 KB, 14 views)
Reply With Quote
  #2  
Old 03-23-2021, 02:54 PM
Guessed's Avatar
Guessed Guessed is offline How to do row specific macro only if column criteria is met Windows 10 How to do row specific macro only if column criteria is met Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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:
  1. Do your tables contain any merged cells?
  2. Your text says add 'english' to column 3 but your screenshot shows it in column 10. Which is correct?
  3. Does the c./v. replacement happen anywhere in the row or only in a specific row?
  4. Does the c./v. replacement happen only when there is a space in front of the c.?
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 03-23-2021, 09:00 PM
macropod's Avatar
macropod macropod is offline How to do row specific macro only if column criteria is met Windows 10 How to do row specific macro only if column criteria is met Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #4  
Old 03-24-2021, 12:38 AM
doeclapton doeclapton is offline How to do row specific macro only if column criteria is met Windows 7 64bit How to do row specific macro only if column criteria is met Office 2010
Novice
How to do row specific macro only if column criteria is met
 
Join Date: Jul 2019
Posts: 8
doeclapton is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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:
  1. Do your tables contain any merged cells?
  2. Your text says add 'english' to column 3 but your screenshot shows it in column 10. Which is correct?
  3. Does the c./v. replacement happen anywhere in the row or only in a specific row?
  4. Does the c./v. replacement happen only when there is a space in front of the c.?
Apologies for not including my actual document. I was planning to use a simplified example just to get the fundamental template of the code by which I can modify myself. I've know attached the JQ Report which I am planning to use the macro on. Now to answer your question:

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.
Attached Files
File Type: doc JQ Report.doc (324.5 KB, 5 views)
Reply With Quote
  #5  
Old 03-24-2021, 12:55 AM
doeclapton doeclapton is offline How to do row specific macro only if column criteria is met Windows 7 64bit How to do row specific macro only if column criteria is met Office 2010
Novice
How to do row specific macro only if column criteria is met
 
Join Date: Jul 2019
Posts: 8
doeclapton is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
For what the OP described, taking the 3 to mean the 3rd highlighted column:
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.
Reply With Quote
  #6  
Old 03-24-2021, 02:46 AM
macropod's Avatar
macropod macropod is offline How to do row specific macro only if column criteria is met Windows 10 How to do row specific macro only if column criteria is met Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by doeclapton View Post
How were you able to come up with it so fast?
Experience.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

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 to do row specific macro only if column criteria is met How can I save all attachments in a folder with specific criteria? terrymac Outlook 1 11-12-2015 06:55 AM
How to do row specific macro only if column criteria is met Excel VBA Macro - Deleting Specific Data based on criteria MD011 Excel Programming 3 12-10-2014 02:15 AM
How to do row specific macro only if column criteria is met Deleting rows with specific criteria joflow21 Excel 9 11-22-2013 12:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft