Macro to Replace characters not working

[bhbsb]

Hello friends, I created a macro to replace some characters. It worked for a while an today, when i tried to run it again, nothing happens! If I got through f8 process it "sees that "p" is >0 but the "Then Mid(temp, i, 1) = Mid(codiB, p, 1)" does not replace anything. Does anybody knows whats going on? The macro is Sub Substituir_Acento() Sheets("Preencher").Select codiA = "àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑabcdefghijklmnopqrstuvwxyz" codiB = "aaaaaeeeeiiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnNABCDEFGHIJKLMNOPQRSTUVWXYZ" For z = 2 To 8 Cells(3, z).Select Do While ActiveCell <> Empty And Row < 10 temp = ActiveCell For i = 1 To Len(temp) p = InStr(codiA, Mid(temp, i, 1)) If p > 0 Then Mid(temp, i, 1) = Mid(codiB, p, 1) Next ActiveCell.Offset(1, 0).Select Loop Next End Sub

 

[bhbsb]

Dont know what happened with the format of the post
Hello friends,
I created a macro to replace some characters. It worked for a while an today, when i tried to run it again, nothing happens!
If I got through f8 process it "sees that "p" is >0 but the "Then Mid(temp, i, 1) = Mid(codiB, p, 1)" does not replace anything.
Does anybody knows whats going on?
The macro is
Sub Substituir_Acento()
Sheets("Preencher").Select

codiA = "àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑabcdefghijklmnopqrstuvwxyz"
codiB = "aaaaaeeeeiiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnNABCDEFGHIJKLMNOPQRSTUVWXYZ"
For z = 2 To 8
Cells(3, z).Select
Do While ActiveCell <> Empty And Row < 10
temp = ActiveCell
For i = 1 To Len(temp)

p = InStr(codiA, Mid(temp, i, 1))
If p > 0 Then Mid(temp, i, 1) = Mid(codiB, p, 1)
Next
ActiveCell.Offset(1, 0).Select

Loop
Next

End Sub

 

[pgc01]

Hi

I posted a macro to remove diacritics in latin letters in posts #8 and #9 here:

http://www.mrexcel.com/forum/excel-questions/348352-convert-symbols-letters.html

Hope it helps

 

[pgc01]

Hi

I posted a macro to remove diacritics in latin letters in posts #8 and #9 here:

http://www.mrexcel.com/forum/excel-questions/348352-convert-symbols-letters.html

Hope it helps

 

[bhbsb]

Hello Pgc01,
Thank you for ur replie.
The point is, the macro I created worked before.. Why should it not work now?

 

[pgc01]

Hi

Sorry, I don't see how your macro could ever have worked.

Are you sure you were using it in vba and not, for ex., vb.net or some other environment?

As you may know, the VBA Editor is old and only accepts ascii characters, and so many of the characters in your statement:

codiA = "àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑabcdefghijklmnopqrstuvwxyz"

would not be accepted.

Please confirm. Copy this statement to a code module and see what is displayed.

 

[bhbsb]

Hello pgc01
I'm sure I'm using VBA, and that the code worked flawless back.
The only thing that I can remember I did after it worked is that I installed office 2007 (but keeping office 2000, which is the one I use for this workbook).
Could the installation screw something?
edited: Nothing happens when I paste the code in a new module

 

[pgc01]

I'm sure I'm using VBA, and that the code worked flawless back.
The only thing that I can remember I did after it worked is that I installed office 2007 (but keeping office 2000, which is the one I use for this workbook).
Could the installation screw something?
edited: Nothing happens when I paste the code in a new module

I'm not home now but I'll try your code tonight (GMT) and post back.

 

[bhbsb]

Up

 

[pgc01]

Hi

Sorry, I forgot to post back.

I looked at your code and I think you may have lost part of it. The way it is it could not work, because you don't have a output statement.
Your code does not write anything in the sheet.

Another problem is that the loop is While Row<10, but the value of Row is not updated inside the loop, which means an infinite loop.

If I understand correctly you want to loop in a range from column 2 to 8 and the row from 3 to the last non empty cell or to row 10.

If this is the case, I amended your code and try:

Sub Substituir_Acento()
Dim codiA As String, codiB As String, sTemp As String
Dim lRow As Long, lCol As Long, i As Long, p As Long

Sheets("Preencher").Select

codiA = "àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑabcdefghijklmnopqrstuvwxyz"
codiB = "aaaaaeeeeiiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnNABCDEFGHIJKLMNOPQRSTUVWXYZ"

For lCol = 2 To 8
    lRow = 3
    Do While Cells(lRow, lCol) <> "" And lRow < 10
        sTemp = Cells(lRow, lCol)
        For i = 1 To Len(sTemp)
            p = InStr(codiA, Mid(sTemp, i, 1))
            If p > 0 Then Mid(sTemp, i, 1) = Mid(codiB, p, 1)
        Next
        Cells(lRow, lCol) = sTemp
        lRow = lRow + 1
    Loop
Next

End Sub