Forum Discussion
Solved
How to find/replace Japanese characters in Excel VBA code
Hi,
Looking for some help please in how to find/replace japanese characters with VBA in Excel. I basically have a monthly report i get in a mixture of Japanese Shift format and English, and I can do a manual Find/Replace in Excel on the common headers (e.g. 決済番号) and convert them to English so I can understand them.
When I try and record that as a macro to automate this process it replaces the japanese with '??' and the code doesn't work as expected. I've got a list of Japanese phrases I'm looking to automatically swap over into the English text for but don't want to do that manually each time so would be great if someone has a solution for how to automate this step !
Thanks,
Isty
Sub japanese() Dim var As Variant Dim i As Long Dim j As Integer Dim maxrow As Long Application.ScreenUpdating = False Range("B:B").Clear maxrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To maxrow If Not IsEmpty(Cells(i, 1)) Then var = Application.Match(Cells(i, 1), Range("E1:E50"), 0) If Not IsError(var) Then j = Application.Match(Cells(i, 1), Range("E1:E50"), 0) Cells(i, 2).Value = Cells(j, 6).Value Else End If Else End If Next i Application.ScreenUpdating = True End SubMaybe with this code. In the attached file you can click the button in cell H2 to run the code. In the example the words and translation are entered in range E1:F50. The vocabulary to be translated is in column A.
Sub japanese() Dim var As Variant Dim i As Long Dim j As Integer Dim maxrow As Long Application.ScreenUpdating = False Range("B:B").Clear maxrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To maxrow If Not IsEmpty(Cells(i, 1)) Then var = Application.Match(Cells(i, 1), Range("E1:E50"), 0) If Not IsError(var) Then j = Application.Match(Cells(i, 1), Range("E1:E50"), 0) Cells(i, 2).Value = Cells(j, 6).Value Else End If Else End If Next i Application.ScreenUpdating = True End SubMaybe with this code. In the attached file you can click the button in cell H2 to run the code. In the example the words and translation are entered in range E1:F50. The vocabulary to be translated is in column A.
- Hi, just wanted to let you know that worked a treat ! Thank you so much for sharing that technique 🙂
Really clever idea - thank you so much - will try that approach ! 🙂
