代码如何优化

daoxuanren

自己写的代码，水平比较差，太啰嗦了，怎么优化呢？谢谢！

Sub Extext()

Dim I As Long, MaxRow As Long

Application.ScreenUpdating = False

MaxRow = Range("A1").End(xlDown).Row

For I = 1 To MaxRow '行 范围，自己修改

  Range("S" & I & ":W" & I).ClearContents
  
  Range("P1") = "修正代码"
  Range("Q1") = "现金流分类"
  Range("R1") = "现金流项目"
  Range("S1") = "大类"
  Range("T1") = "中类"
  Range("U1") = "小类"
  
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("P2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'----------------------------------------------------------------
If InStr(Range("M" & I), "废品") > 0 Then
    Range("S" & I) = "その他"
    Range("T" & I) = "废品收入"

  ElseIf InStr(Range("M" & I), "货款") > 0 Then
    Range("S" & I) = "営業入金"
    Range("T" & I) = "销售收入"
'----------------------------------------------------------------
ElseIf InStr(Range("M" & I), "直材") > 0 Then
    Range("S" & I) = "材料費"
    Range("T" & I) = "材料采购"
ElseIf InStr(Range("M" & I), "付进口材料") > 0 Then
    Range("S" & I) = "材料費"
    Range("T" & I) = "材料采购"
  '----------------------------------------------------------------
ElseIf InStr(Range("M" & I), "工资") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "实发工资"
ElseIf InStr(Range("M" & I), "生育险") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "实发工资"
    Range("U" & I) = "生育险"
ElseIf InStr(Range("M" & I), "派遣") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "劳务外注"
ElseIf InStr(Range("M" & I), "养老保险") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "法定福利"
    Range("U" & I) = "养老保险"
ElseIf InStr(Range("M" & I), "公积金") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "法定福利"
    Range("U" & I) = "公积金"
ElseIf InStr(Range("M" & I), "社保") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "法定福利"
    Range("U" & I) = "社保费用"
  '----------------------------------------------------------------
ElseIf InStr(Range("M" & I), "体检") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "体检费用"
ElseIf InStr(Range("M" & I), "工会经费") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "工会经费"
ElseIf InStr(Range("M" & I), "食堂") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "员工食堂"
ElseIf InStr(Range("M" & I), "生日") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "员工生日"
  ElseIf InStr(Range("M" & I), "旅游") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "旅游费用"
  ElseIf InStr(Range("M" & I), "开门红") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "开门红"
  ElseIf InStr(Range("M" & I), "鑫欧臣") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "高温饮料"
ElseIf InStr(Range("M" & I), "社长奖") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "社长奖"
  ElseIf InStr(Range("M" & I), "忘年会") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "忘年会"
  ElseIf InStr(Range("M" & I), "尚真服饰") > 0 Then
    Range("S" & I) = "人件費"
    Range("T" & I) = "福利厚生"
    Range("U" & I) = "工作服"
'----------------------------------------------------------------
ElseIf InStr(Range("M" & I), "奖金") > 0 Then
    Range("S" & I) = "賞与"
    Range("T" & I) = "员工奖金"
   
ElseIf InStr(Range("M" & I), "间材") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "间材费用"
  '----------------------------------------------------------------
  ElseIf InStr(Range("M" & I), "天然气") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "水电气费"
  ElseIf InStr(Range("M" & I), "自来水") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "水电气费"
  ElseIf InStr(Range("M" & I), "污水处理费") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "水电气费"
  ElseIf InStr(Range("M" & I), "电费") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "水电气费"
  '----------------------------------------------------------------
  ElseIf InStr(Range("M" & I), "培训") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "教育培训"

  ElseIf InStr(Range("M" & I), "工具") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "间材费用"
  ElseIf InStr(Range("M" & I), "BS货款") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "间材费用"

  ElseIf InStr(Range("M" & I), "入门费") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "入门费"
   
  ElseIf InStr(Range("M" & I), "维修费") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "修缮费"
'----------------------------------------------------------------
  ElseIf InStr(Range("M" & I), "日新") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "物流费"
  ElseIf InStr(Range("M" & I), "日通") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "物流费"
  ElseIf InStr(Range("M" & I), "万友通") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "物流费"
  ElseIf InStr(Range("M" & I), "顺衡") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "物流费"
  ElseIf InStr(Range("M" & I), "万特可") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "物流费"
  '----------------------------------------------------------------
  ElseIf InStr(Range("M" & I), "经济补偿") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "经济补偿"
   
  ElseIf InStr(Range("M" & I), "利息") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "利息"
  ElseIf InStr(Range("M" & I), "手续费") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "手续费"
   
  ElseIf InStr(Range("M" & I), "统括业务") > 0 Then
    Range("S" & I) = "経費"
    Range("T" & I) = "统括费用"
'----------------------------------------------------------------
  ElseIf InStr(Range("M" & I), "企业所得税") > 0 Then
    Range("S" & I) = "税金(法人税等)"
    Range("T" & I) = "税金缴纳"
  ElseIf InStr(Range("M" & I), "环保税") > 0 Then
    Range("S" & I) = "税金(法人税等)"
    Range("T" & I) = "税金缴纳"
  ElseIf InStr(Range("M" & I), "印花税") > 0 Then
    Range("S" & I) = "税金(法人税等)"
    Range("T" & I) = "税金缴纳"
  ElseIf InStr(Range("M" & I), "城建税") > 0 Then
    Range("S" & I) = "税金(法人税等)"
    Range("T" & I) = "税金缴纳"
  ElseIf InStr(Range("M" & I), "教育附加") > 0 Then
    Range("S" & I) = "税金(法人税等)"
    Range("T" & I) = "税金缴纳"
ElseIf InStr(Range("M" & I), "税费") > 0 Then
    Range("S" & I) = "税金(法人税等)"
    Range("T" & I) = "税金缴纳"
'----------------------------------------------------------------
  ElseIf InStr(Range("M" & I), "划款") > 0 Then
    Range("S" & I) = "换算差额"
  ElseIf InStr(Range("M" & I), "拨款") > 0 Then
    Range("S" & I) = "换算差额"
  ElseIf InStr(Range("M" & I), "调拨") > 0 Then
    Range("S" & I) = "换算差额"
   
End If
   Next
   
  ActiveWorkbook.Save
  Application.ScreenUpdating = True

End Sub

kkf5560

感觉这么多if，没什么改的，硬要说的话，做一个参照表逐个判断吧

山菊花

供参考。
提示一下，原第29行“BS货款”应提升到“货款”之前。

ykcbf1100

用三个数组装填里面这些项目更合适。
难是不难，就是费时。

limonet

关键字：left join

limonet

Sub limonet()
    Dim Cn As Object, StrSQL$
    Set Cn = CreateObject("Adodb.Connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
    StrSQL = "Select 大类,中类,小类 From [202312$M:M]a Left Join [Sheet1$]b On a.对方文本 Like '%'+b.对方文本+'%'"
    Range("S2").CopyFromRecordset Cn.Execute(StrSQL)
End Sub

daoxuanren

山菊花 发表于 2024-2-2 12:41
供参考。
提示一下，原第29行“BS货款”应提升到“货款”之前。

谢谢版主！！

daoxuanren

山菊花 发表于 2024-2-2 12:41
供参考。
提示一下，原第29行“BS货款”应提升到“货款”之前。

谢谢版主的回复，感激不尽。。。。。

daoxuanren

limonet 发表于 2024-2-2 14:12
Sub limonet()
    Dim Cn As Object, StrSQL$
    Set Cn = CreateObject("Adodb.Connection")

谢谢高手的回复，学习了。