Excel VBA/利阿賀拿

wiki左右列改上下列

Option Explicit

Sub SplitEnglishAndChinese()         '本程序把英文和中文左並列的資料，改為上下分列
    Dim sht1 As Worksheet   '《英中左右》
    Dim sht2 As Worksheet   '《英中上下》
    Dim rng1 As Range       '《英中左右》表的儲存格參考點
    Dim rng2 As Range       '《英中上下》表的儲存格參考點
    Dim i1 As Integer       '《英中左右》表的列指標
    Dim i2 As Integer       '《英中上下》表的列指標


' 以下是程式邏輯
    Application.ScreenUpdating = False
    
    Set sht1 = Worksheets("英中左右")   'set 是將變數連結一個物件
    Set rng1 = sht1.Range("a1")
    Set sht2 = Sheets.Add(after:=Worksheets(Worksheets.Count))
    sht2.Name = "英中上下"
    Set rng2 = sht2.Range("a1")
    
    i1 = 0
    i2 = 0
    Do Until rng1.Offset(i1, 0).Value = ""
    '每一列在此迴圈中處理
        rng2.Offset(i2, 0).Value = rng1.Offset(i1, 0)   '讀入左列，寫出英文列
        i2 = i2 + 1                                     '輸出列加一
        rng2.Offset(i2, 0).Value = rng1.Offset(i1, 1)   '讀入右列，寫出中文列
        i2 = i2 + 1                                     '輸出列再加一
        i1 = i1 + 1                                     '輸入列加一
    Loop
    Application.ScreenUpdating = True
    

End Sub
    

wiki句子編號

Option Explicit
Sub setSentenceNumber()     '本程序設定總會大會英文對照的句子編號
    Dim sht1 As Worksheet   '《英中上下》
    Dim sht2 As Worksheet   '《句子編號》
    Dim rng1 As Range       '《英中上下》表的儲存格參考點
    Dim rng2 As Range       '《句子編號》表的儲存格參考點
    Dim i As Integer        '《英中上下》表的列指標
    Dim k As Integer        '欄位的指標
    Dim cntSentence As Integer '句子編號
    
    Const col_No = 0        '序號的欄位移
    Const col_TagNText = 1  '序號與本文的欄位移
    Const col_TagHead = 4   '維基標記頭的欄位移
    Const col_Text = 5      '本文的欄位移
    Const col_TagTail = 6   '維基標記尾的欄位移


' 以下是程式邏輯
    Application.ScreenUpdating = False
    
    Set sht1 = Worksheets("英中上下")   'set 是將變數連結一個物件
    Set rng1 = sht1.Range("a1")
    Set sht2 = Sheets.Add(after:=Worksheets(Worksheets.Count))
    sht2.Name = "句子編號"
    Set rng2 = sht2.Range("a1")
    
    cntSentence = 0
    
    i = 0
    Do Until rng1.Offset(i, 0).Value = ""
    '每一列在此迴圈中處理
        Select Case rng1.Offset(i, col_TagHead).Value
            Case "<p class='english'>"
                cntSentence = cntSentence + 1
                rng2.Offset(i, col_No).Value = rng1.Offset(i, col_No).Value
                rng2.Offset(i, col_TagNText).Value = rng1.Offset(i, col_TagHead).Value & _
                                                     "<span class='englishVerse'>" & cntSentence & "</span>" & " " & _
                                                     rng1.Offset(i, col_Text).Value & _
                                                     rng1.Offset(i, col_TagTail).Value
            Case "<p class='chinese'>"
                rng2.Offset(i, col_No).Value = rng1.Offset(i, col_No).Value
                rng2.Offset(i, col_TagNText).Value = rng1.Offset(i, col_TagHead).Value & _
                                                     "<span class='chineseVerse'>" & cntSentence & "</span>" & " " & _
                                                     rng1.Offset(i, col_Text).Value & _
                                                     rng1.Offset(i, col_TagTail).Value
            Case Else
                rng2.Offset(i, col_No).Value = rng1.Offset(i, col_No).Value
                rng2.Offset(i, col_TagNText).Value = rng1.Offset(i, col_TagNText).Value
        End Select
            
        i = i + 1
    Loop
    Application.ScreenUpdating = True
    

End Sub