Microsoft Office Forums Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-18-2019, 08:50 PM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Smile Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells

I have this macro that works for me, the only thing, if I have 5 pages long of table(s), it takes for ever to do it 1 by 1 on each $ or %.



In a nut shell, I need to find $ or % in a table, then move it to the next cell which I've already created an empty column.
I wish I could show an example of a table picture, but I don't know how.

Normally Find and Replace, Execute All at once. What can I do to have it do it all at once?


This is my macro.....
Sub Change_Value_to_Next_Cell_GOOD()
'33
'Change_Value_to_Next_Cell Macro
' I can change the % or $ or any string of text and have it copy, cut, and paste to the next cell.
' Alt+2
'
Dim sText As String
sText = InputBox("Enter text to move cell")
Selection.Find.ClearFormatting
With Selection.Find
.Text = sText
.Wrap = wdFindStop
End With
Do While Selection.Find.Execute
If Selection.Information(wdWithInTable) = True Then
Selection.Copy
Selection.Cut
Selection.MoveRight Unit:=wdCell
Selection.PasteAndFormat (wdFormatOriginalFormatting)
Selection.Move Unit:=wdCell, Count:=1
End If
Loop
MsgBox " You're Selection is Done in the Active Document!"
On Error GoTo 0

End Sub


Any insight would be appreciated.


Oh an added bonus, if I can find multiple values at once, such as if found $ or %, or another value, cut move to next cell, then paste.

P.S.1 Reason we must move the $ or % to the next cell, is because that is what we do in our French documents.
P.S.2 My table example to test a macro, has 5 columns of numbers only, with an extra empty columns on each columns to move in the $.

Thanks in advance


Cendrinne

Last edited by Cendrinne; 08-19-2019 at 08:45 AM. Reason: Added p.s. 2
Reply With Quote
  #2  
Old 08-18-2019, 09:57 PM
Guessed's Avatar
Guessed Guessed is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,260
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

This does seem slower than I would expect. You could try this version using ranges but it doesn't show a significant speed improvement.
Code:
Sub Change_Value_to_Next_Cell_GOOD()
  Dim aRng As Range
  Dim sText As String
  sText = InputBox("Enter text to move cell")
  Set aRng = ActiveDocument.Range
  ActiveWindow.View = wdNormalView
  Application.ScreenUpdating = False
  With aRng.Find
    .ClearFormatting
    .Text = sText
    .Wrap = wdFindStop
    Do While .Execute
      If aRng.Information(wdWithInTable) Then
        aRng.Cells(1).Next.Range.FormattedText = aRng.FormattedText
        aRng.Delete
        aRng.MoveStart Unit:=wdCell, Count:=2
      End If
    Loop
  End With
  Application.ScreenUpdating = True
  MsgBox " You're Selection is Done in the Active Document!"
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 08-19-2019, 12:50 AM
gmayor's Avatar
gmayor gmayor is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,898
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

If, as appears to be the case, the tables have an empty last column and the search relates to the next to last column, then it might be simpler and faster to process the columns e.g. using the macro below. It won't, however cater for merged cells or nested tables.

It would also be much quicker to run the process in Word 2010, but that's another story entirely.

Code:
Sub Macro1()
Dim i As Integer
Dim oTable As Table
Dim oRng As Range, oCell As Range
Dim sText As String
Dim lngCol As Long
    sText = InputBox("Enter text to move cell")
    Application.ScreenUpdating = False
    For Each oTable In ActiveDocument.Tables
        lngCol = oTable.Columns.Count
        For i = 1 To oTable.Rows.Count
            Set oRng = oTable.Cell(i, lngCol - 1).Range
            oRng.End = oRng.End - 1
            If InStr(1, oRng.Text, sText) > 0 Then
                Set oCell = oTable.Cell(i, lngCol).Range
                oCell.End = oCell.End - 1
                oCell.FormattedText = oRng.FormattedText
                oRng.Text = ""
            End If
            DoEvents
        Next i
        DoEvents
    Next oTable
    Application.ScreenUpdating = True
    MsgBox "Tables processed"
lbl_Exit:
    Set oTable = Nothing
    Set oRng = Nothing
    Set oCell = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #4  
Old 08-19-2019, 08:27 AM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default Hummmm didn't work, but maybe my instruction where not clear....

GMayor,


What it did, it went to the last columns and in each cell that had the value ''$'' in it, moved completely the whole cell including the ''$'' to it's Right empty cell. And ignore all other columns.


In my test table (I only used tables of numbers, No text), I have 5 columns of numbers, and every columns has it's empty column besides, to enable to: Cut (the selected value, ...) ''$'', Move 1 Cell unit to the Right, and Paste (the selected value,...) ''$'', Loop to the end.


I just wish my macro I did, would do it all at once, versus, find and replace 1 value at the time.


I wish I could give an example of the test table. How can I insert without having to put a URL link?


Anyway, thanks and if you could think of something... I would be so grateful


I'll try the Guessed suggestion.

Last edited by Cendrinne; 08-19-2019 at 08:54 AM. Reason: Tried to be clearer in my answer to GMayor
Reply With Quote
  #5  
Old 08-19-2019, 08:39 AM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default

Hello Guessed,


I got an error message at this line, ''aRng.Cells(1).Next.Range.FormattedText= aRng.FormattedText''
stating (well I have to translate it from French to English, so please I apologize in advance, if I don't write the correct terms).... Execution Error '91': Variable object or Variable bloc With, not defined


I hope this helped.
















Reply With Quote
  #6  
Old 08-19-2019, 03:28 PM
Guessed's Avatar
Guessed Guessed is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,260
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

Is it possible that the last cell in the table contains the search term? I can reproduce that error if I set the table up that way.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #7  
Old 08-19-2019, 04:22 PM
gmaxey gmaxey is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 936
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

So your table has 10 columns odd have numbers with "$" and even are empty. Yes? Try:
Code:
Sub Macro1()
Dim lngIndex As Integer
Dim oTbl As Table
Dim oRng As Range
Dim strText As String
Dim lngCol As Long
Dim varVal
  strText = InputBox("Enter text to move cell")
  Application.ScreenUpdating = False
  For Each oTbl In ActiveDocument.Tables
    For lngCol = 1 To oTbl.Columns.Count - 1 Step 2
      For lngIndex = 1 To oTbl.Rows.Count
        Set oRng = oTbl.Cell(lngIndex, lngCol).Range
        oRng.End = oRng.End - 1
        varVal = Split(oRng.Text, strText)
        If UBound(varVal) = 1 Then
          oRng.Text = Trim(varVal(1))
          oRng.Cells(1).Next.Range.Text = strText
        End If
      Next lngIndex
      DoEvents
    Next lngCol
  Next oTbl
  Application.ScreenUpdating = True
  MsgBox "Tables processed"
lbl_Exit:
  Set oTbl = Nothing
  Set oRng = Nothing
  Exit Sub
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #8  
Old 08-19-2019, 10:43 PM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Red face YES!!!!!!!!! It worked Greg gmaxey

OMG, You did it. Wow, wow, wow, you are a genius.
I've been trying for months, analyzing, trying, and I didn't come close to your result.


Thank you soooooo very much.


Most of our French documents are like that, if they have $ and %, we usually need to put them in a separate columns to have all the numbers aligned perfectly. And unlike in English, where the $ are in front of the numbers, our $ are in the back. So we put both the % and $ in the same columns and all the numbers can be aligned.


What a fantastic group of people here. I'm truly grateful


Have a super duper reste of the week
Reply With Quote
  #9  
Old 08-19-2019, 10:52 PM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default

Hello Guessed/Andrew Lockton
You've asked if ==> Is it possible that the last cell in the table contains the search term? I can reproduce that error if I set the table up that way.
Yes most of our tables have a $ on top and $ on the last row, so it's possible that it created the error.


But see my reply to Greg Maxey, that macro worked!!!!!! I'm still in shock, wow. And so fast
That will save me tons of time. Cause, 5 pages of tables are on an average. At times I have 20 pages of tables, where % and $ is on tons of rows and columns, I had time to go to washroom or get a coffee


Anyway, I'm grateful even to you to have tried. It's a lesson to all of us Have a great evening everyone
Reply With Quote
  #10  
Old 08-20-2019, 05:18 AM
gmaxey gmaxey is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 936
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

Good. Thanks.


You mentioned symbols $ and %. Here is a revision to process data like $50.00 or 25%


Code:
Sub Macro1()
Dim oTbl As Table
Dim oRng As Range
Dim strText As String
Dim lngCol As Long, lngRow As Long, lngIndex As Long
Dim varVal
Dim varSyms

  strText = InputBox("Enter the symbols separated with colon, eg. $:%", "SYMBOLS", "$:%")
  Application.ScreenUpdating = False
  varSyms = Split(strText, ":")
  For Each oTbl In ActiveDocument.Tables
    For lngCol = 1 To oTbl.Columns.Count - 1 Step 2
      For lngRow = 1 To oTbl.Rows.Count
        Set oRng = oTbl.Cell(lngRow, lngCol).Range
        oRng.End = oRng.End - 1
        For lngIndex = 0 To UBound(varSyms)
          varVal = Split(oRng.Text, varSyms(lngIndex))
          If UBound(varVal) = 1 Then
            If Trim(varVal(0)) <> vbNullString Then
              oRng.Text = Trim(varVal(0))
            Else
              oRng.Text = Trim(varVal(1))
            End If
            oRng.Cells(1).Next.Range.Text = varSyms(lngIndex)
            Exit For
          End If
        Next lngIndex
      Next lngRow
      DoEvents
    Next lngCol
  Next oTbl
  Application.ScreenUpdating = True
  MsgBox "Tables processed"
lbl_Exit:
  Set oTbl = Nothing
  Set oRng = Nothing
  Exit Sub
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #11  
Old 08-20-2019, 10:05 AM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default

Heuhhhh, Greg Maxey
I don't know what happened. I was so happy to used the first thing you've sent me, I didn't try the second yet, but now the numbers that was besides the % is removed, then the % is moved to the next cell.


What happened? Yesterday it worked fine. I've tested about 3-4 times without any problems, and today, I lost the numbers.


In the table I need to work with, the $ amounts were already in their proper place, so I only needed to move the %. Could that be the reason why it's not working?


I'll try my example table of 5 columns to test it out.


Reply With Quote
  #12  
Old 08-20-2019, 10:15 AM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default

Greg Maxey,
Unless yesterday when I've used my example table of 10 Columns in total, 5 of numbers and 5 empty ones besides them, I've only tested the $ and assumed it worked for the %.


Now all $ works A1, it doesn't work for the %.


The only difference is maybe the position of the searched value. When I use the English Tables that I must convert to French, the position of the $ is in front of the numbers, which needs to be moved to the next cell. & The %, is at the end regardless French of English, which needs to be moved to the next cell.


Is it fixable? It was working so great. I've almost showed people at work.


Any advice?


Cendrinne
Reply With Quote
  #13  
Old 08-20-2019, 10:26 AM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default

Greg Maxey,
I've just tested the 2nd macro you've created, since I saw $:%, I had nothing to lose to try it, THIS ONE WORKED!!!!!!!! WOW.
So maybe the first one was only for $, but this one works for both.


OMG you are a freaking genius. I truly hope it will work tomorrow and so forth. I was pretty sure I've tried it both yesterday $ and % and worked without any problems.


I'll keep you posted. And sorry for the triple post to you, but I wanted to let you know.


Again, I'm in an awwwwwww of your skills. I hope one day, I'll get there. I've learned how to do it with youtube videos and internet research, plus my logic with the knowledge I have, and this is for the past 3 years.


Is there a place I can have lessons with you? Do you give courses and how much? I would need a good coach cause it's now my passion doing VBA.


I was thought with an IT guy Excel VBA 5-6 years ago, then with this job I'm doing, we do work 99% of the time in Word, so I had to learn on my own.


Where are you from?


OK got to get back to work

Last edited by Cendrinne; 08-20-2019 at 12:59 PM.
Reply With Quote
  #14  
Old 08-20-2019, 11:28 AM
gmaxey gmaxey is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 936
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

Cendrinne,


The code yesterday only worked if the $ symbol was in front of the amount. So, if you have $10 is worked 10$ it wouldn't. Same for %10 and 10%. The code today works regardless which side of the amount the symbol is located.

I develop solutions with VBA (mainly Word) and charge a fee. Contact me via my website if you wish. I've never done a course.

My website has a bit of background on me.


Thanks and it should work going forward.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #15  
Old 08-20-2019, 01:02 PM
Cendrinne Cendrinne is offline Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Windows 10 Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Office 2013
Novice
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells
 
Join Date: Aug 2019
Location: Montreal Quebec Canada
Posts: 9
Cendrinne is on a distinguished road
Default

Ok super, Greg, I will check you out after work and thanks so much again. I knew there was a way, I just couldn't find a post on it or figured it out. Ok later, still got work to do
Reply With Quote
Reply

Tags
find & replace, help please, speed

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
what method to find all cells paste linked to a certain cell ? DBenz Excel 1 06-28-2018 12:16 PM
Select Cell Text to paste into Find/Replace CBarry Word VBA 2 02-16-2017 04:37 AM
Help to spead up macro - Find and Replace in Tables, Cut Paste Next Cells Find and Replace Macro amparete13 PowerPoint 3 03-11-2014 05:29 AM
macro or find/replace JamesVenhaus Word 2 02-27-2012 03:34 PM
Find and Replace Macro - A Better Way Tribos Word VBA 0 10-08-2008 03:22 AM


All times are GMT -7. The time now is 04:01 PM.


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