VBA实现满足条件时向Excel指定列赋值的方法

现有VBA代码可遍历源Excel文件，依据公司名称提取对应销售数据并填充至公司专属文件，目前运行正常。需要优化实现：仅当newuplift值为"Renewal"时，将UpliftValue写入目标工作表第9列，否则写入"0"。

原代码片段 #

For i = 2 To LastRow
       
    If SourceSheet.Cells(i, 21).Value Like "CompanyName goes here*" Then
 
        'change the column numbers to the relevant number
        Product = SourceSheet.Cells(i, 11).Value
        Base Sales Value = SourceSheet.Cells(i, 27).Value 
        Partner = SourceSheet.Cells(i, 21).Value
        EndUser = SourceSheet.Cells(i, 7).Value
        License = SourceSheet.Cells(i, 13).Value
        PostingMonth = SourceSheet.Cells(i, 3).Value
        LicType = SourceSheet.Cells(i, 12).Value
        newuplift = SourceSheet.Cells(i, 15).Value
        UpliftValue = SourceSheet.Cells(i, 28).Value

        erow = DestSheet.Cells(DestSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
             
        'change the column numbers to the relevant number
        DestSheet.Cells(erow, 1).Value = ProdType
        DestSheet.Cells(erow, 2).Value = License
        DestSheet.Cells(erow, 3).Value = Partner
        DestSheet.Cells(erow, 4).Value = EndUser
        DestSheet.Cells(erow, 5).Value = SOValue
        DestSheet.Cells(erow, 6).Value = PostingMonth
        DestSheet.Cells(erow, 7).Value = newuplift
        DestSheet.Cells(erow, 8).Value = LicType
        DestSheet.Cells(erow, 9).Value = UpliftValue 'TRYING TO PLACE A CONDITION HERE - SEE BELOW

        ' If newuplift = "Renewal" then place the Upliftvalue in row 9, otherwise set to "0"
               
    End If
    
Next i

优化后的解决方案 #

你可以通过两种方式实现需求，选择适合自己的写法即可：

方式1：使用IIf函数（简洁写法） #

将原代码中赋值第9列的行替换为：

DestSheet.Cells(erow, 9).Value = IIf(newuplift = "Renewal", UpliftValue, 0)

方式2：使用If...Else结构（逻辑更直观） #

如果需要拆分逻辑展示，也可以写成：

If newuplift = "Renewal" Then
    DestSheet.Cells(erow, 9).Value = UpliftValue
Else
    DestSheet.Cells(erow, 9).Value = 0
End If

完整优化代码示例 #

For i = 2 To LastRow
       
    If SourceSheet.Cells(i, 21).Value Like "CompanyName goes here*" Then
 
        'change the column numbers to the relevant number
        Product = SourceSheet.Cells(i, 11).Value
        BaseSalesValue = SourceSheet.Cells(i, 27).Value '修正：VBA变量名不能含空格，原代码此处会报错
        Partner = SourceSheet.Cells(i, 21).Value
        EndUser = SourceSheet.Cells(i, 7).Value
        License = SourceSheet.Cells(i, 13).Value
        PostingMonth = SourceSheet.Cells(i, 3).Value
        LicType = SourceSheet.Cells(i, 12).Value
        newuplift = SourceSheet.Cells(i, 15).Value
        UpliftValue = SourceSheet.Cells(i, 28).Value

        erow = DestSheet.Cells(DestSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
             
        'change the column numbers to the relevant number
        DestSheet.Cells(erow, 1).Value = ProdType
        DestSheet.Cells(erow, 2).Value = License
        DestSheet.Cells(erow, 3).Value = Partner
        DestSheet.Cells(erow, 4).Value = EndUser
        DestSheet.Cells(erow, 5).Value = SOValue
        DestSheet.Cells(erow, 6).Value = PostingMonth
        DestSheet.Cells(erow, 7).Value = newuplift
        DestSheet.Cells(erow, 8).Value = LicType
        ' 替换为条件赋值逻辑
        DestSheet.Cells(erow, 9).Value = IIf(newuplift = "Renewal", UpliftValue, 0)
               
    End If
    
Next i

额外说明 #

原代码中Base Sales Value是无效变量名（VBA变量名不允许包含空格），已修正为BaseSalesValue，避免运行时出现语法错误。

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