海运对账里运用VBA实现轻松化

总的代码如下：

思路：

1，复制表1出来一份

2，分别插入2行

3，分别加入表头

4，分别合并出来一行，并给这行修改了字体和字体颜色

Sub 复制工作表和插入行()
    Sheets("sheet1").Copy after:=Sheets(Sheets.Count)
    
    For i = 3 To 100 Step 3
        Range("A" & i).Select
        Selection.EntireRow.Insert
        Selection.EntireRow.Insert
    Next
End Sub

Sub 设置格式()
    For i = 4 To 100 Step 3
        Range("A1:L1").Select
        Selection.Copy
        Range("A" & i).Select
        ActiveSheet.Paste
    Next
    
    For i = 3 To 100 Step 3
        Range(Cells(i, 1), Cells(i, 8)).Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        With Selection
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
        With Selection
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
        Selection.Font.Bold = True
        Selection.Font.Size = 12
        With Selection.Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    Next
End Sub

Sub 总的()
    复制工作表和插入行
    设置格式
End Sub

最后F单对账的话术如下：

1. 打开你的Excel文件并选择需要修改的工作表。
2. 按下Alt + F11打开VBA编辑器。
3. 在左侧的“项目窗口”中找到要修改的工作表（例如“Sheet1”）。
4. 双击该工作表，打开它的代码窗口。
5. 将更新后的代码复制粘贴到代码窗口中。
6. 关闭VBA编辑器。
7. 返回Excel工作表，并尝试双击每隔3行的第一列单元格。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 And Target.Row Mod 3 = 0 Then ' 当点击的单元格在第一列，并且所在行能被3整除时
        Cancel = True ' 取消默认的双击编辑行为
        DisplayChoices Target.Row ' 调用显示选择的函数，并传递单元格所在行号
    End If
End Sub

Sub DisplayChoices(ByVal rowNumber As Long)
    Dim sentences() As String
    Dim sentenceIndex As Long
    Dim choice As Integer
    
    ' 初始化句子数组
    sentences = Split("No F file in OID. Pls help provide~,Already input in AP.Cost# .,Updated F file shows $46.80~so we enter $46.80 not $90 Pls help advise.,Pls help enter F file of PSS$", ",")
    
    ' 显示选择的选项
    choice = MsgBox("请选择要填入第" & rowNumber & "行的句子：" & vbCrLf & _
                    "1. " & sentences(0) & vbCrLf & _
                    "2. " & sentences(1) & vbCrLf & _
                    "3. " & sentences(2) & vbCrLf & _
                    "4. " & sentences(3), vbQuestion + vbYesNoCancel, "选择句子")
    
    ' 根据用户的选择，确定要填入的句子索引
    Select Case choice
        Case vbYes
            sentenceIndex = 0
        Case vbNo
            sentenceIndex = 1
        Case vbCancel
            sentenceIndex = 2
        Case vbIgnore
            sentenceIndex = 3
        Case Else
            MsgBox "无效的选择"
            Exit Sub
    End Select
    
    ' 获取用户选择的句子
    Dim sentence As String
    sentence = sentences(sentenceIndex)
    
    Cells(rowNumber, 1).Value = sentence ' 将句子填入相应的单元格
End Sub

R单的对账的话术如下：

No record of invoice,

Pls help get invoice from ZIM，
Already input in AP.

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 And Target.Row Mod 3 = 0 Then ' 当点击的单元格在第一列，并且所在行能被3整除时
        Cancel = True ' 取消默认的双击编辑行为
        DisplayChoices Target.Row ' 调用显示选择的函数，并传递单元格所在行号
    End If
End Sub

Sub DisplayChoices(ByVal rowNumber As Long)
    Dim sentences() As String
    Dim sentenceIndex As Long
    Dim choice As Integer
    
    ' 初始化句子数组
    ' 初始化句子数组
sentences = Split("No record of invoice Pls help get invoice from ZIM.,Already input in AP.,Update R file of inv# ORFR1120060 shows 5,930.70,so we entered 5,930.70", ",")
    
    ' 显示选择的选项
    choice = MsgBox("请选择要填入第" & rowNumber & "行的句子：" & vbCrLf & _
                    "1. " & sentences(0) & vbCrLf & _
                    "2. " & sentences(1) & vbCrLf & _
                    "3. " & sentences(2) & vbCrLf & _
                    "4. " & sentences(3), vbQuestion + vbYesNoCancel, "选择句子")
    
    ' 根据用户的选择，确定要填入的句子索引
    Select Case choice
        Case vbYes
            sentenceIndex = 0
        Case vbNo
            sentenceIndex = 1
        Case vbCancel
            sentenceIndex = 2
        Case vbIgnore
            sentenceIndex = 3
        Case Else
            MsgBox "无效的选择"
            Exit Sub
    End Select
    
    ' 获取用户选择的句子
    Dim sentence As String
    sentence = sentences(sentenceIndex)
    
    Cells(rowNumber, 1).Value = sentence ' 将句子填入相应的单元格
End Sub