dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi Folks,
I have a column of data (column H) headed [customer reference]. Customer reference data is populated by users when they book a courier online.
Analysing many invoices, I compiled a find-&-replace table: column A (find) and column B (replace).
For replacing one element within a string e.g., "/" with the hashtag "#", I have the following code that works: 100##1000001/100 becomes 100#1000001#100
Whilst this works, I have found over 100 errors to account for. I would therefore like to use a Find and Replace table for the VBA code to refer to, e.g.,
The problem is as follows: when I run this vba code, the text string I wish to modify goes from:
100##1000001/100dlm becomes #
rather than
100##1000001/100dlm becomes 100#1000001#100dlm
Would anybody be willing to help me modify this VBA to find-and-replace only the element within the string rather than finding the element and replacing the whole string?
Kind regards,
Doug.
I have a column of data (column H) headed [customer reference]. Customer reference data is populated by users when they book a courier online.
- They have to input a client customer number (3 digits), a sales order number and a check digit, each element separated by a hashtag (so later I can text-to-columns on using the # character as delimiter).
e.g., 100#1000001#100
- However, in haste users often make errors such as:
e.g., 100##1000001/100
Analysing many invoices, I compiled a find-&-replace table: column A (find) and column B (replace).
For replacing one element within a string e.g., "/" with the hashtag "#", I have the following code that works: 100##1000001/100 becomes 100#1000001#100
Code:
Sub FindNReplaceForSlashToHash()
Worksheets("Invoice").Activate
Application.Goto Reference:=Range("InvoiceTable[Customer reference]")
Selection.Replace what:="/", replacement:="#", LookAt:=[COLOR=#0000cd][B]xlPart[/B][/COLOR], _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace what:="##", replacement:="#", LookAt:=[COLOR=#0000cd][B]xlPart[/B][/COLOR], _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Whilst this works, I have found over 100 errors to account for. I would therefore like to use a Find and Replace table for the VBA code to refer to, e.g.,
Code:
Sub Multi_FindReplace()
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
[COLOR=#006400]'Create variable to point to your table[/COLOR]
Set tbl = Worksheets("FindNReplace").ListObjects("FindNReplaceTable")
[COLOR=#006400]'Create an Array out of the Table's Data[/COLOR]
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)
[COLOR=#006400]'Designate Columns for Find/Replace data[/COLOR]
fndList = 1
rplcList = 2
[COLOR=#006400]'Loop through each item in Array lists[/COLOR]
For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
Range("InvoiceTable[Customer reference]").Select
Selection.Cells.Replace what:=myArray(fndList, x), replacement:=myArray(rplcList, x), _
LookAt:=[COLOR=#0000cd][B]xlPart[/B][/COLOR], SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next x
End Sub
The problem is as follows: when I run this vba code, the text string I wish to modify goes from:
100##1000001/100dlm becomes #
rather than
100##1000001/100dlm becomes 100#1000001#100dlm
Would anybody be willing to help me modify this VBA to find-and-replace only the element within the string rather than finding the element and replacing the whole string?
Kind regards,
Doug.