多条件判断语句运行速度优化

810651146

有一个多条件判断语句，目的是为了在B列填入名字后，在同行的其他列能出现对应的内容
现在这个程序跑起来特别卡，要怎么优化呢？


Private Sub Worksheet_Change(ByVal Target As Range)
Dim RR As Integer
If Target.Row < 2 Or Target.Columns.Count > 1 Or Target.Rows.Count > 1 Or Target.Column > 14 Then Exit Sub
RR = Target.Row
Select Case Target.Value

Case "Kevin"

Range("A" & RR) = Format(Now, "YYYY-MM-DD")
Range("C" & RR) = "CC"
Range("D" & RR).FormulaR1C1 = "=IFS(RIGHT(RC[1],2)=""CA"",""CA"",RIGHT(RC[1],2)=""IT"",""IT"",RIGHT(RC[1],2)=""DE"",""DE"",RIGHT(RC[1],2)=""UK"",""UK"",TRUE,""US"")"
Range("F" & RR) = "未知"
Range("G" & RR) = "未知"
Range("H" & RR).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],VL!C[-5]:C[-4],2,0),"""")"
Range("J" & RR).FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Range("K" & RR) = "未知"
Range("M" & RR).FormulaR1C1 = "=10-WEEKDAY(RC[-1],2)+RC[-1]"


Case "Elisa", "Carl", "Nikki"
Target.Offset(0, 1) = "KT"
Target.Offset(0, 2) = "US"
Target.Offset(0, -1) = Format(Now, "YYYY-MM-DD")
Target.Offset(0, 6).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],VL!C[-5]:C[-4],2,0),"""")"
Target.Offset(0, 8).FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Target.Offset(0, 11).FormulaR1C1 = "=10-WEEKDAY(RC[-1],2)+RC[-1]"

Case "Kelly"
Target.Offset(0, 1) = "OL"
Target.Offset(0, 2) = "US"
Target.Offset(0, -1) = Format(Now, "YYYY-MM-DD")
Target.Offset(0, 6).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],VL!C[-5]:C[-4],2,0),"""")"
Target.Offset(0, 8).FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Target.Offset(0, 11).FormulaR1C1 = "=10-WEEKDAY(RC[-1],2)+RC[-1]"

Case "Jasmine"
Target.Offset(0, 1) = "KT"
Target.Offset(0, 2) = "CA"
Target.Offset(0, -1) = Format(Now, "YYYY-MM-DD")
Target.Offset(0, 6).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],VL!C[-5]:C[-4],2,0),"""")"
Target.Offset(0, 8).FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Target.Offset(0, 11).FormulaR1C1 = "=10-WEEKDAY(RC[-1],2)+RC[-1]"

Case "Allen"
Target.Offset(0, 1) = "GN"
Target.Offset(0, 2) = "US"
Target.Offset(0, -1) = Format(Now, "YYYY-MM-DD")
Target.Offset(0, 6).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],VL!C[-5]:C[-4],2,0),"""")"
Target.Offset(0, 8).FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Target.Offset(0, 11).FormulaR1C1 = "=10-WEEKDAY(RC[-1],2)+RC[-1]"

Case "Joy"
Target.Offset(0, 1) = "KT"
Target.Offset(0, 2).FormulaR1C1 = _
        "=IFS(RIGHT(RC[1],2)=""CA"",""CA"",RIGHT(RC[1],2)=""IT"",""IT"",RIGHT(RC[1],2)=""DE"",""DE"",RIGHT(RC[1],2)=""UK"",""UK"",TRUE,""US"")"
Target.Offset(0, -1) = Format(Now, "YYYY-MM-DD")
Target.Offset(0, 6).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-3],VL!C[-5]:C[-4],2,0),"""")"
Target.Offset(0, 8).FormulaR1C1 = "=IFERROR(RC[-2]*RC[-1],"""")"
Target.Offset(0, 11).FormulaR1C1 = "=10-WEEKDAY(RC[-1],2)+RC[-1]"

End Select

End Sub 运营发货需求表.rar (203.53 KB, 下载次数: 8)

2023-1-6 15:40 上传

点击文件名下载附件

Yalishanda30

改成数组，不用公式

游乐缘

事件开始时加上一句：

1. Application.EnableEvents = False

复制代码

在代码结束前加上一句：

1. Application.EnableEvents = True

复制代码

supermouse24

我觉得你把参数都输入到一个表里面，然后用形成数组或者字典。我推荐是字典。
那些公式是重复的，然后就可以重复输入了。

LIUZHU

这是用VBA往单元格中写入公式吗？

LIUZHU

用VBA写入公式，然后用公式就结果？就好比你在单元格录入数据，用计算器把结果算出来，再录入到单元格里面。

Meteor-渣渣

用vba帮忙写公式？
这vba不学也罢

东亚五金

这个纯脆是个人的习惯与能力有关系的

gwjkkkkk

改成数组，内存在运算，然后赋值。。。

Meteor-渣渣

就算不用数组，最基本的循环单元格都比公式要快