Sub CalculateK3()
Dim stopKeywords As Variant
Dim I4Value As Double
Dim L4Value As String
Dim K3Value As String
'设置停职等关键词
stopKeywords = Array("停职", "事故", "事假", "病假", "违规", "旷工")
'获取I4和L4单元格的值
I4Value = CDbl(Range("I4").Value)
L4Value = Range("L4").Value
'判断是否包含停职等关键词
Dim hasStopKeyword As Boolean
hasStopKeyword = False
For i = LBound(stopKeywords) To UBound(stopKeywords)
If InStr(LCase(L4Value), LCase(stopKeywords(i))) > 0 Then
hasStopKeyword = True
Exit For
End If
Next i
'计算K3的值
If hasStopKeyword Then
K3Value = ""
Else
If IsNumeric(I4Value) Then
If I4Value >= 98 Then
K3Value = "★★★"
ElseIf I4Value >= 95 Then
K3Value = "★★"
ElseIf I4Value >= 90 Then
K3Value = "★"
ElseIf I4Value < 90 Then
K3Value = ""
End If
Else
K3Value = ""
End If
End If
'将计算得到的K3的值写入单元格K3
Range("K3").Value = K3Value
这段VBA代码会根据I4和L4单元格的值,判断是否包含停职等关键词,然后根据I4的数值大小返回相应的星级评价。最后,将计算得到的K3的值写入单元格K3。你可以将这段VBA代码放入Excel文件的VBA编辑器中,运行宏即可实现与Excel函数公式相同的功能。
追问如果根据I5和L5单元格的值,I6和L6单元格的值,分别K4,K5进行判断,怎么进行循环执行呢?另外如果I列含有文本,则对应的K列等于空值。怎么去做呢?期待你的回复。谢谢!