Text replacing formula gives out just lowercase, help!

[Halil93]

Hi all,

Since this is my first post ever, i will try my best to describe my problem as understandable and structured as possible.
Feedback is of course highly appreciated.

The objective is to find a word in a range of cells and replace it by another word. No matter if the word which is to be found is capitalized or not.

Example. In cell is word "APPle-Pie", the part "APPle/apple/APPLE" (no matter of big or small) should be replaced by "Apple"

The code:

Sub text_replacement()


Dim sh As Worksheet
Dim i As Integer
Dim j As Integer

Set sh = Workbooks("replacement_naming_guidelines.xlsm").Sheets("Sheet1")


    For i = 1 To 7

        For j = 1 To 3
           
            sh.Cells(i, 1).Value = Replace(LCase(sh.Cells(i, 1).Value), sh.Cells(j, 2).Value, sh.Cells(j, 3).Value)
        
        Next j
        
    Next i

End sub

Idea:
Loop through list Cells (1-7,1), lower the cells with the replace function, see if part of word or entire word matches with Cells(1-3,2), and replace it by Cells(1-3,3).
Everything works fine, besides that even tho the list with the replacements Cells(1-3,3) contains capitalized letters, when replaced for words in Cells (1-7,1) they are often in small letters.
It gets completely strange when sometimes they are big and sometimes small.

I hope you understood the issue and one of you can help me.
Thank you anyway!

Stay tuned!

[hemesh]

welcome to the forum you can try replacing below in your 2nd for Loop

For j = 1 To 3

sh.Cells(i, 1).Value = Replace(sh.Cells(i, 1).Value, sh.Cells(j, 2).Value, sh.Cells(j, 3).Value)

Next j

[Halil93]

hi hemesh,

thank you for your comment.

I have tried it and it is replacing the it perfectly.

But the issue i wanted to solve by adding lcase() to the code is, that the word i want to replace (1-7,1) has to be exactly equal to the word which is in the 2nd list Cells(1-3,2).

Example:
If 1st list exactly equal 2nd list
1st list apple Cells(1-7,1), 2nd list apple Cells(1-3,2), replaced with 3rd list pear Cells(1-3,3) -> apple replaced by pear.

If 1st not exactly equal 2nd list
1st list ApPLe Cells(1-7,1), 2nd list apple Cells(1-3,2), replaced with 3rd list pear Cells(1-3,3) -> ApPLe not replacedby pear. Because ApPLe is not exactly equal to apple.

With lcase i could change every word from 1st list to lower case. In list 2 every word would be in lower case, to always exactly match with 1st list.

I hope that could explain why i used lcase().
The issue i have now is explained in the post.

Kind regards and thank you again for commenting.

Cheers and happy Sunday

[Fluff13]

Hi & welcome to the board.
How about

Sub text_replacement()
   Dim sh As Worksheet
   Dim j As Integer
   
   Set sh = Workbooks("replacement_naming_guidelines.xlsm").Sheets("Sheet1")
   For j = 1 To 3
      sh.Range("A1:A7").Replace sh.Cells(j, 2).Value, sh.Cells(j, 3).Value, xlPart, , False, , False, False
   Next j
End Sub

[Halil93]

Hi Fluff13,

magical!
It is working perfectly.
I didnīt know about binary comparison and text comparison.

That was very helpful and i learned a lot as well.
Thumbs up!

[Fluff13]

You're welcome & thanks for the feedback