How to find/replace Japanese characters in Excel VBA code

[Isty_Ahmad]

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

[#2 · Accepted solution]

@Isty_Ahmad 

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 Sub

Maybe 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.

[#3]

@Quadruple_Pawn 

Really clever idea - thank you so much - will try that approach !

[#4]

Hi, just wanted to let you know that worked a treat ! Thank you so much for sharing that technique