Search code examples
excelvbarangeselectionstring-conversion

Katakana to Hiragana in Excel VBA

I have an excel file where I have to change Katakana characters to Hiragana. I've been using StrConv + vbHiragana for some time, however, it is really slow since I am using it in a range for a 1000 rows (two times). After searching around, I've found the following code, which seems like a different approach and looks really fast. My only problem is, that it only works if I select the specified row and column (e.g. from A1:A10) and then if I run it, it does the job perfectly. I need to tweak this so it automatically does it in a range and displays the results on a different sheet.　(From A1:A1000 + D1:D1000) Like this:

sample

Here is the code I've found:

Sub Comm()
Dim i, gyos, retus, rwsu As Integer
Dim KATA, HIRA As String


gyos = ActiveCell.Row
retus = ActiveCell.Column
rwsu = Selection.Rows.Count - 1


For n = gyos To gyos + rwsu
KATA = Cells(n, retus)
HIRA = StrConv(KATA, 32)
Cells(n, retus) = HIRA
Next n


End Sub

Anyone knows a way around this?

Thank you!

Solution

  • Like what findwindow suggested, just set your own range and use that in the for loop:

    Dim row as Integer

For row = 1 to 1000
    Worksheets("Sheet2").Range("B" & row).Value = StrConv(Worksheets("Sheet1").Range("B" & row).Value, 32)
    Worksheets("Sheet2").Range("D" & row).Value = StrConv(Worksheets("Sheet1").Range("D" & row).Value, 32)
Next row