Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-15-2017, 08:05 AM
CBarry CBarry is offline Select Cell Text to paste into Find/Replace Windows 10 Select Cell Text to paste into Find/Replace Office 2010 64bit
Novice
Select Cell Text to paste into Find/Replace
 
Join Date: Feb 2017
Posts: 2
CBarry is on a distinguished road
Default Select Cell Text to paste into Find/Replace

Hi Guys.



Very new to VBA so please be gentle with me and I apologies in advance for any dumb questions.

I have a text string in a table cell (actually a file reference) I'd like to select this text then run a find/replace with the selected string as the replace value.

Any help would be massively appreciated.

Cheers,

Chris Barry
Reply With Quote
  #2  
Old 02-15-2017, 03:17 PM
macropod's Avatar
macropod macropod is online now Select Cell Text to paste into Find/Replace Windows 7 64bit Select Cell Text to paste into Find/Replace Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,595
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

The following macro checks the contents of a document against a series of expressions in the second column of the first table in the document, and outputs a count of those matches in the third column of the table. Document contents before the table are not checked (this makes it more flexible for use in a document where you want to exclude the ‘front matter’ from checking). Only minor modifications would be needed to adapt this for Find/Replace purposes.
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim RngDoc As Range, oTbl As Table, strFnd As String, i As Long, j As Long
With ActiveDocument
  Set oTbl = .Tables(1)
  For i = 1 To oTbl.Rows.Count
    strFnd = Split(oTbl.Cell(i, 2).Range.Text, vbCr)(0)
    Set RngDoc = ActiveDocument.Range
    RngDoc.Start = oTbl.Range.End
    With RngDoc
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Format = False
        .Text = strFnd
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchCase = True
        .Execute
      End With
      j = 0
      Do While .Find.Found
        j = j + 1
        .Collapse wdCollapseEnd
        .Find.Execute
      Loop
    End With
    If j > 0 Then oTbl.Cell(i, 3).Range.Text = j
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-16-2017, 04:37 AM
CBarry CBarry is offline Select Cell Text to paste into Find/Replace Windows 10 Select Cell Text to paste into Find/Replace Office 2010 64bit
Novice
Select Cell Text to paste into Find/Replace
 
Join Date: Feb 2017
Posts: 2
CBarry is on a distinguished road
Default

Worked like a charm thank you.
Reply With Quote
Reply

Tags
file location, replace, table

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to find a macro that will copy a cell and paste that value to a specific sheet bryans88 Excel Programming 1 12-23-2015 01:40 PM
Select Cell Text to paste into Find/Replace Find, select, and replace part of text with bold paik1002 Word VBA 4 12-07-2015 11:24 PM
Select Cell Text to paste into Find/Replace VBA Table Search All Tables - Find & Replace Text in Table Cell With Specific Background Color jc491 Word VBA 8 09-30-2015 06:10 AM
Microsoft Word macro to find text, select all text between brackets, and delete helal1990 Word VBA 4 02-05-2015 03:52 PM
Select Cell Text to paste into Find/Replace How to find and select text in a document? mkhuebner Word VBA 8 02-04-2014 08:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:13 AM.


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