Excel VBA check the contents of two cells

Question

Good morning.

I’m trying to create some modules in excel to help me work and one of the modules I created swaps all the accented letters and special characters of a "Cell" for letters without accent and substitutes for the special characters. See example below:

Cell A1= "Essa peça de roupa custa R$59,99 somente este mês."
Cell A2= "Essa peca de roupa custa RS59,99 somente este mes."

I need to create a module that checks that what is in "Cell"A1 is in accordance with "Cell" A2 even without the accents and special characters.

Answer 1

I suggest you create a function with the rules of character replacement with accents. I don’t know how you wrote this code, but I imagined something like this, structured as a function for better handling variables:

Public Function SemAcentos(t As String)

    t = Replace(t, "ç", "c")
    t = Replace(t, "à", "a")
    t = Replace(t, "á", "a")
    t = Replace(t, "ã", "a")
    t = Replace(t, "â", "a")
    t = Replace(t, "é", "e")
    t = Replace(t, "ê", "e")
    t = Replace(t, "í", "i")
    t = Replace(t, "õ", "o")
    t = Replace(t, "ó", "o")
    t = Replace(t, "ú", "u")

    ' ...todas as demais regras de substituição de caracteres especiais!!!

    SemAcentos = t

End Function

That way you can remove the accents from Cell A1 and immediately compare it to Cell A2, something like that:

Sub macro1()

    If SemAcentos(Range("A1").Value) = Range("A2").valuie Then
        MsgBox "Verdadeiro"
    Else
        MsgBox "Falso"
        End If

End Sub

Or, as you created a function, you can use it directly in a spreadsheet cell, using it in combination with the SE formula, for example:

=SE(SemAcentos(A1)=A2;VERDADEIRO;FALSO)