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
明细账分内部和外部
最新推荐文章于 2024-10-18 00:00:00 发布
这段VBA代码用于处理Excel中的明细账数据,它区分内部和外部公司,并在R列标记相应的类别。首先创建两个字典分别存储内部和外部公司的关键字,然后遍历G列数据,删除匹配到的内部公司行,对剩余行检查是否包含外部公司关键字,如果匹配则标记为外部公司。最后,代码冻结首行,设置数字格式,并应用自动筛选。
摘要由CSDN通过智能技术生成