如何在VBA中根据CG列值动态设置AV列的输入内容？

你需要替换代码中固定赋值AV列的行，改为根据CG列的值进行条件判断。以下是修改后的完整代码：

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns(46)) Is Nothing Then
Dim arrHolidays, tmp As Single, rngN As Range, d As Date
Dim cgValue As Variant ' 新增变量存储CG列的值

If Target.Cells(1).Value = "Performed Audit" Then
     'place in an array all national holidays:
     arrHolidays = Array(CLng(CDate("2022-09-05")), CLng(CDate("2022-11-24")), CLng(CDate("2022-11-25")), CLng(CDate("2022-12-23")), _
                                  CLng(CDate("2022-12-26")), CLng(CDate("2022-12-28")), CLng(CDate("2022-12-29")), CLng(CDate("2022-12-30")))
    On Error GoTo SafeExit
    Set rngN = Me.Cells(Target.Row, "N"):  d = rngN.Value
    tmp = CDbl(d) - Int(d) 'place the time in a variable. Workday does not return hours, minutes, seconds...
    
    Application.EnableEvents = False: Application.Calculation = xlCalculationManual
     Me.Cells(Target.Row, "J").Value = "Performed"
     Me.Cells(Target.Row, "K").Value = "Performed"
     Me.Cells(Target.Row, "AS").Value = "Post-Audit"
     Me.Cells(Target.Row, "AU").Value = Format(Me.Cells(Target.Row, "N"), "mm/dd/yyyy HH:mm:ss")
     
     ' 替换原有固定赋值，根据CG列值判断AV列内容
     cgValue = Me.Cells(Target.Row, "CG").Value
     If Val(cgValue) >= 1 Then
         Me.Cells(Target.Row, "AV").Value = "Issue Audit Report With Findings"
     Else
         Me.Cells(Target.Row, "AV").Value = "Issue Audit Report with No Findings/Audit Closure Statement"
     End If
     
     Me.Cells(Target.Row, "AW").Value = Format(WorksheetFunction.WorkDay_Intl(d + 19, 1, 1, arrHolidays) + tmp, "mm/dd/yyyy  HH:mm:ss")
    Me.Cells(Target.Row, "AZ").Value = Format(WorksheetFunction.WorkDay_Intl(d + 19, 1, 1, arrHolidays) + tmp, "mm/dd/yyyy  HH:mm:ss")
    Me.Cells(Target.Row, "BA").Value = Format(WorksheetFunction.WorkDay_Intl(d + 29, 1, 1, arrHolidays) + tmp, "mm/dd/yyyy  HH:mm:ss")
    Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic
End If
SafeExit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

修改说明 #

1. 新增cgValue变量存储当前行CG列的值，提升代码可读性
2. 使用Val(cgValue)处理空白或非数值的情况：
   • 若CG列值为空白，Val返回0，触发Else分支
   • 若CG列值为>=1的数值，触发If分支设置对应文本
3. 补充了SafeExit标签的必要收尾代码，避免因错误导致事件和计算模式无法恢复

内容的提问来源于stack exchange，提问作者fanglies