Solved

Macro to convert replace wrong text with right text

Posted on 2015-01-26
9
65 Views
Last Modified: 2015-02-04
If text is text-Master.xlsx contains those that are unbold in Text.xlsx, then replace them to the one in bold.

See attached for example.

Text-Master.xlsx


Text.xlsx

NOTE:
the text will always have 4 characters, and the characters is alpha-numeric. It can have alphabets first followed by numbers or viceversa. (see text data that i show in "Text.xlsx" row 2 to 18)

the text data that i show in "Text.xlsx" show from row 21 and onwards is just for illustration purposes. The concept of alpha-numeric still follow and that whatever appears before the bold text will be replaced by the bold text. Each grouping is separated by 2 rows. The first grouping is from row 2 to row 12 then row 15 to row 18 and then row 21 to 25 etc.
0
Comment
Question by:ceneiqe
  • 5
  • 3
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40572138
Try this array formula

=REPLACE(A1,10,4,INDEX(OFFSET(Text.xlsx!$A$1,MATCH(MID(A1,10,4),Text.xlsx!$A:$A,0)-1,0,100),MATCH(0,IF(OFFSET(Text.xlsx!$A$1,MATCH(MID(A1,10,4),Text.xlsx!$A:$A,0)-1,0,100)="",0,1),0)-1))
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40572139
Do you want a macro solution or a formula solution ? Although your title says Macro !
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40572177
Here is the code for the macro

Sub SearchReplace()
Dim WS As Worksheet
Dim WBT As Workbook
Dim WST As Worksheet
Dim MaxRow As Long, I As Long, J As Long
Dim cCell As Range
Dim vValues As Variant
Dim sReplace As String, sSearch As String


'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
Set WBT = Workbooks.Open(ActiveWorkbook.Path & "\Text.xlsx")
Set WST = ActiveSheet

For I = 1 To MaxRow
    If WS.Cells(I, "A") <> "" Then
        sSearch = WS.Cells(I, "A")
        vValues = Split(WS.Cells(I, "A"), ";")
        For J = LBound(vValues) To UBound(vValues)
            Set cCell = WST.Range("A:A").Find(what:=vValues(J), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            If Not cCell Is Nothing Then
                sReplace = cCell.End(xlDown).Value
                sSearch = Replace(sSearch, vValues(J), sReplace)
            End If
        Next J
        WS.Cells(I, "G") = sSearch
    End If
Next I

WBT.Close False
Set WBT = Nothing
Set WST = Nothing

MsgBox "Search Replace Done !", vbExclamation
End Sub

Open in new window



You have to pay attention in the text.xlsx you posted line 19,20 looks blank but they are not as it seems some data was there and has been deleted. Just to make sure prior to running the macro just delete line 19 and 20 and re-insert 2 lines blank or else you may get wrong figures. this is not due to macro but coz lines are not blank ! If you have this built in then I can change the behavior of the macro.

The Text.xlsx should be in the same directory as the Text Master.xlsm anyway I am attaching both try these 2 but without deleting 19,20 as I already did.

gowflow
Text-Master.xlsm
Text.xlsx
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:ceneiqe
ID: 40575169
hi saqib,
i tried your formula in column E but doesn't work.

hi gowflow
yes i requested a macro.
but if there is a easier alternative like array formula, i can also try if it works better for me.
Thanks for the macro. i tested and there are errors.
if the data in Text master cannot be found in text, then the data should remain.
let me consolidate the errors and get back to you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40575217
ok for this one, easy. I will wait to see all the possible errors.
gowlfow
0
 

Author Comment

by:ceneiqe
ID: 40588984
For file Text.xlsx:
the following rows are left blank deliberately to act as divider to differentiate each grouping:
rows that are blank
13, 14
19, 20
26, 27
32, 33
37, 38
and so on.
The rows that are blank are always in twos.
Row 2-12 is the first grouping, 15-18 another grouping, 21 to 25 another grouping and so on.

If the data found in Text-Master.xlsm but is not found in Text.xlsx, then the data remain.

If the data is found in Text-Master.xlsm and is also found in Text.xlsx, then ensure that the bold text are replaced.

See attached.
Text.xlsx
Text-Master.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40589062
Here it is Sorry my mistake.

Sub SearchReplace()
Dim WS As Worksheet
Dim WBT As Workbook
Dim WST As Worksheet
Dim MaxRow As Long, I As Long, J As Long, K As Long
Dim cCell As Range
Dim vValues As Variant
Dim sReplace As String, sSearch As String


'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
Set WBT = Workbooks.Open(ActiveWorkbook.Path & "\Text1.xlsx")
Set WST = ActiveSheet

For I = 1 To MaxRow
    If WS.Cells(I, "A") <> "" Then
        sSearch = WS.Cells(I, "A")
        vValues = Split(WS.Cells(I, "A"), ";")
        For J = LBound(vValues) To UBound(vValues)
            Set cCell = WST.Range("A:A").Find(what:=vValues(J), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            If Not cCell Is Nothing Then
                K = 0
                Do While WST.Cells(cCell.Row + K, cCell.Column).Value <> ""
                    K = K + 1
                Loop
                
                sReplace = WST.Cells(cCell.Row + K - 1, cCell.Column)
                sSearch = Replace(sSearch, vValues(J), sReplace)
            End If
        Next J
        WS.Cells(I, "G") = sSearch
    End If
Next I

WS.Range("J:J").EntireColumn.AutoFit
WBT.Close False
Set WBT = Nothing
Set WST = Nothing

MsgBox "Search Replace Done !", vbExclamation
End Sub

Open in new window


Pls see attached.
gowflow
Text-Master1.xlsm
0
 

Author Closing Comment

by:ceneiqe
ID: 40589091
Very fast and accurate !
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40589092
Finally !!!!
Glad we did it.

gowflow
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Countifs formula not counting data points 5 20
conditional formatting 4 41
how to add loop into this VBA 3 27
Select Next Route by Time 4 19
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question