How to replace multiple words in one sheet from a list in another sheet?

[janispencis]

Hello!

I have a workbook of two sheets: (1) text_corpus and (2) words_to_be_replaced.

text_corpus contains multiple columns and multiple rows with a word, symbol or number in each cell.

words_to_be_replaced contains two columns: (a) incorrect_word and (b) corrected_word.

incorrect_word is a list / column of thousands of words which have to be replaced with respective words from a list / column corrected_word in text_corpus.

Could you please advice me the necessary code to get this done?

Thanks,
Janis

 

[WarPigl3t]

sht1 = "text_corpus"
sht2 = "words_to_be_replaced"
lastRowSht1 = Sheets(sht1).Range("A" & Rows.Count).End(xlup).Row
lastRowSht2 = Sheets(sht2).Range("A" & Rows.Count).End(xlup).Row
i = 2
Do Until i > lastRowSht1
     c = 1
     evaluateString = Sheets(sht1).Range(Column(c) & i).value
     Do Until evaluateString = ""
          e = 2
          Do Until e > lastRowSht2
               If evaluateString = Sheets(sht2).Range("A" & e).value Then
                    Sheets(sht1).Range(Column(c) & i).value = Sheets(sht2).Range("B" & e).value
               End If
               e = e + 1
          Loop
          c = c + 1
          evaluateString = Sheets(sht1).Range(Column(c) & i).value
     Loop
     i = i + 1
Loop

I did not test this code. I just wrote it in my head. So you might have to troubleshoot if something goes wrong. Like if you get a syntax error. I'm pretty sure it works though.

 

[janispencis]

Hi WarPiglet

Thanks a lot for quick response! I'll test this later on and let you know how it works.

Janis

 

[janispencis]

Hi WarPiglet

I changed Column to Columns as a Compile error jumped out (sub or function not defined):

Sub replacer()
sht1 = "text_corpus"
sht2 = "words_to_be_replaced"
lastRowSht1 = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
lastRowSht2 = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do Until i > lastRowSht1
c = 1
evaluateString = Sheets(sht1).Range(Columns(c) & i).Value
Do Until evaluateString = ""
e = 2
Do Until e > lastRowSht2
If evaluateString = Sheets(sht2).Range("A" & e).Value Then
Sheets(sht1).Range(Columns(c) & i).Value = Sheets(sht2).Range("B" & e).Value
End If
e = e + 1
Loop
c = c + 1
evaluateString = Sheets(sht1).Range(Columns(c) & i).Value
Loop
i = i + 1
Loop
End Sub



But now it shows Run-Time error '13': Type Mismatch, and indicates Ln9 after Debug:

evaluateString = Sheets(sht1).Range(Columns(c) & i).Value

Could you please advise what should be corrected in the code?

Thanks a lot!

Janis

 

[WarPigl3t]

Yeah it's because when you use Range, you can't use a column number. It was my bad. We can do one of 2 things. We can convert the column number to a letter using an equation, but I don't recommend it because if the columns exceed column Z, it will require more equations to apply the next columns such as AA through ZZ. Instead I recommend using a similar line of code rather than using Range.

[COLOR=#333333]evaluateString = Sheets(sht1).Cells(i, Columns(c)).value[/COLOR]

The syntax might bewrong on the above code. If it is wrong try this...

[COLOR=#333333]evaluateString = Sheets(sht1).Cells(i, Columns(c))[/COLOR]

Notice how I reversed the i and the columns(c) variables. That is why I don't like to use Cells in my code. I only use it when I need to evaluate columns instead of rows. But when I'm evaluating rows, it makes more sense to the human eye to use Range. Using Cells instead of Range allows you to enter a column number rather than a column letter as part of your range. Make sense?

 

[WarPigl3t]

I had some time to troubleshoot the issue. Here is the working code and I highlighted red the areas that I corrected from the original code. Turns out I was using the function "Columns" incorrectly.

sht1 = "text_corpus"
sht2 = "words_to_be_replaced"
lastRowSht1 = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
lastRowSht2 = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do Until i > lastRowSht1
     c = 1
     evaluateString = Sheets(sht1)[COLOR=#ff0000].Cells(i, c)[/COLOR].Value
     Do Until evaluateString = ""
          e = 2
          Do Until e > lastRowSht2
               If evaluateString = Sheets(sht2).Range("A" & e).Value Then
                    Sheets(sht1)[COLOR=#ff0000].Cells(i, c)[/COLOR].Value = Sheets(sht2).Range("B" & e).Value
               End If
               e = e + 1
          Loop
          c = c + 1
          evaluateString = Sheets(sht1)[COLOR=#ff0000].Cells(i, c)[/COLOR].Value
     Loop
     i = i + 1
Loop

 

[janispencis]

hi, now it says "run-time error 440: automation error" and indicates line "Do Until evaluateString = """ in the code.

 

[WarPigl3t]

I don't know why you have that error. I made sure the code worked before I posted it. I did not have this problem. Did you modify the code?

 

[WarPigl3t]

This code I gave you is not complicated and you should easily be able to find out what went wrong. It's called troubleshooting. I'm going to assume you don't know how to troubleshoot. I'm going to teach you. You probably initiated the code using the "run" button or you applied the code to a button and it will run when you click it. Well don't do that. When you troubleshoot, you need to inspect the code line by line to ensure that the output of each line of code is working correctly. The way you do this is with the F8 button on your keyboard. While you are looking at the code in vba, press the F8 button 1 time. the line of code being evaluated by vba will turn the backround yellow. Press the F8 button again. Now the next line of code turned yellow. The code above the yellow backrounded text has just finished processing. Before you press F8 again, hover your mouse over each part of the line of code that was just processed. It will tell you what each variable equals. You must do this for each line of code.
For example: Say you have this code

Sub Test()
test = "hello world"
End Sub

When you press F8, it will highlight yellow "test = "hello world""
It is not processed yet so if you hover your mouse over "test", it will say NULL or ""
but when you press F8 again, it will highlight the next line of code. "test = "hello world"" will not be highlighted anymore. But it has just been processed. Now if you hover your mouse over "test" it will display "hello world" in a pop-up notification.
This is how you look to make sure each line of code is returning what it is suppose to.
Now I gave you working code. Which means you modified it, or you may have accidentally inserted something into the code on accident. Another possability is that your dataset is not how you described it. I created this code to match what you described as your dataset in your first post. Troubleshoot the code and find out if every variable is outputing correctly before it reaches the error. Since the error is located at the Do Until loop, I suggest focusing on the string that instanciates evaluateString and see what it equals before it becomes part of the loop. evaluateString should equal a value or a blank that looks like quotes "". If evaluateString doesn't not equal one of those two things, then replay to this discussion what evaluateString equals. For example: evaluateString equals NULL or error or whatever. It should not equal NULL or error or anything. I would start by copy pasting my code into your vba again to make sure you didn't accidentally add something to the code.

 

[janispencis]

Hi! Finally we've figured out how to get it works:

Sub Replacer()
sht1 = "text_corpus"
sht2 = "words_to_be_replaced"
lastRowSht1 = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
lastRowSht2 = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
i = 1
Do Until i > lastRowSht1
    c = 1
    Do Until c = 14
    evaluateString = Sheets(sht1).Cells(i, c).Value
        e = 1
        Do Until e > lastRowSht2
            If evaluateString = Sheets(sht2).Range("A" & e).Value Then Sheets(sht1).Cells(i, c).Value = Sheets(sht2).Range("B" & e).Value
            e = e + 1
        Loop
        c = c + 1
    Loop
    i = i + 1
Loop
End Sub

I counted the number of columns in the sheet "text_corpus" and indicated it on the code line "Do Until c = 14".

Dear WarPiglet, thank you for patience and advices - this is very valuable for such dummies as me. I spent a bit of time while figuring out how to troubleshoot and do everything on mac. For example, when I pressed F8 first time, it opened iTunes

I must say that the processing of this macro on my mac takes quite a long time. Probably that's because my mac is crap, but maybe that's because it has to compare every cell of the sheet "text_corpus" with 3468 cells of the sheet "words_to_be_replaced". I had approximately 136 000 cells in the initial workbook, and my mac started to growl very loud after the first 10 minutes. Therefore I divided the workbook in 10 smaller workbooks of approximately 14 000 cells. Now it takes around 10 minutes to process the macro on each workbook. I'll try to add a status bar in the code to see the progress on the go.