dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi Folks,
I am working on an address book. I have some VBA code to look at any 'selected range' of cells within the address book; it then finds words within text strings that match words stored in another table (the findList)---stored in column A of table 1. If it finds words within the text string that match words in the findList, it replaces that word with the cognate words---stored in column B of table 1 (the replaceList).
E.g., Address database with cells selected (shown in blue)
<tbody>
</tbody>Table 1: find and replace table
<tbody>
</tbody>
My address workbook has some vba working to identify cells in the address database worksheet with greater than 50 characters: it highlights those cells Red. What I would like to do is modify my find and replace VBA code to only search, find and replace text within cells highlighted in Red?
Here is my current search, find and replace VBA code:
Would anybody be willing to help me modify this VBA to only search cells in the address database that are highlighted in Red?
Kind regards,
Doug.
P.S. the worksheet with the address database is called: addDB
I am working on an address book. I have some VBA code to look at any 'selected range' of cells within the address book; it then finds words within text strings that match words stored in another table (the findList)---stored in column A of table 1. If it finds words within the text string that match words in the findList, it replaces that word with the cognate words---stored in column B of table 1 (the replaceList).
E.g., Address database with cells selected (shown in blue)
| Company name | Address line 1 | Address line 2 | Address line 3 etc |
| Chelsea and Westminster Hospital NHS Foundation Trust | Chelsea and Westminster Hospital NHS Foundation Trust | xyz Fulham Road | London |
| Royal Liverpool and Broadgreen University Hospitals NHS Trust | Royal Liverpool and Broadgreen University Hospital Trust | Prescot Street | Liverpool |
<tbody>
</tbody>
| findList | replaceList |
| Royal | ROY |
| University | Uni |
| Hospital | Hosp |
| NHS Foundation Trust | NHS Trust |
<tbody>
</tbody>
My address workbook has some vba working to identify cells in the address database worksheet with greater than 50 characters: it highlights those cells Red. What I would like to do is modify my find and replace VBA code to only search, find and replace text within cells highlighted in Red?
Here is my current search, find and replace VBA code:
Code:
Sub Multi_FindReplace25()
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Application.ScreenUpdating = False
Application.Calculation = xlManual
'Create variable to point to your table
Set tbl = Worksheets("Abbrev").ListObjects("Table25")
'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)
'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2
'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
[COLOR=#006400] [B]Selection.Replace[/B][/COLOR] what:=myArray(fndList, x), replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next x
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End SubWould anybody be willing to help me modify this VBA to only search cells in the address database that are highlighted in Red?
Kind regards,
Doug.
P.S. the worksheet with the address database is called: addDB
