明细账分内部和外部

舟心ty

于 2022-12-23 15:02:39 发布

阅读量99

点赞数

分类专栏：金蝶文章标签： python 开发语言

本文链接：https://blog.youkuaiyun.com/sx01117/article/details/128419864

版权

金蝶专栏收录该内容

6 篇文章

订阅专栏

这段VBA代码用于处理Excel中的明细账数据，它区分内部和外部公司，并在R列标记相应的类别。首先创建两个字典分别存储内部和外部公司的关键字，然后遍历G列数据，删除匹配到的内部公司行，对剩余行检查是否包含外部公司关键字，如果匹配则标记为外部公司。最后，代码冻结首行，设置数字格式，并应用自动筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Sub 明细账分内部和外部()
Dim arr, i, j, k, m, n, hang1, hang2, mrr1, mrr2, beizhu_nb As String, beizhu_wb As String
Dim rng As Range
Dim dic1 As Object
Dim dic2 As Object
Set dic1 = CreateObject("scripting.dictionary")
Set dic2 = CreateObject("scripting.dictionary")
mrr1 = Array("本期合计", "本年累计", "期间合计", "结转本期损益")
mrr2 = Array("瑞意博", "医疗", "矽海", "信息", "上海", "武汉")
beizhu_nb = "可能是内部公司"
beizhu_wb = "外部公司"
Range("R1") = "类别"
For i = 0 To UBound(mrr1, 1)
    dic1(mrr1(i)) = ""
Next i
For j = 0 To UBound(mrr2, 1)
    dic2(mrr2(j)) = ""
Next j

hang1 = Range("G1").End(xlDown).Row
For k = hang1 To 3 Step -1
    If dic1.exists(Range("G" & k).Value) Then
        Range("G" & k).EntireRow.Delete
    Else
        For m = 0 To UBound(mrr2)
            If InStr(1, Range("G" & k), mrr2(m), 1) > 0 Then
                Range("R" & k) = beizhu_nb
                Exit For
            Else
                Range("R" & k) = beizhu_wb
            End If
        Next m
    End If
Next k

hang2 = Range("G1").End(xlDown).Row
Cells(1, 1).Select
Cells(3, 6).Select
ActiveWindow.FreezePanes = True
Range("A1:R" & hang2).AutoFilter
Range("N:T").NumberFormatLocal = "#,##0.00_ "


End Sub