VBA实现N/A评分项权重重分配 解决公式不计算及动态引用问题

现有代码无法正常运行，核心问题有3个：

• 语法逻辑完全错误：VBA中And是逻辑比较运算符，不能用来连接多个赋值语句。你写的CatPercentage1 = 0.3 And NumberNA = NumberNA + 1 And CatValue1 = 0本质是在做布尔值判断，根本不会执行NumberNA+1、CatValue1=0这类赋值操作；同时6个列的N/A判断是层层嵌套结构，只要第一列AD不是N/A，后面5列的判断逻辑完全不会触发；另外所有N/A列的权重都错赋值给了CatPercentage1变量，其余5个权重变量全程未被赋值，始终为0，这就是你看到CatPercentage1返回值不符合预期的根本原因。
• 无批量行处理逻辑：所有单元格引用都硬编码写死了行号2，也没有写行循环结构，自然只能计算第2行的数据。
• 计算公式错误：最终求和的第一项重复引用了AE列的值，也没有跳过值为N/A的单元格，直接运算会触发类型不匹配报错。

不需要写几十条If分支判断，用数组存固定权重、逐行循环遍历统计即可，代码逻辑通用，适配任意行数的数据集：

Sub CalculateNewValue()
    ' 按AD-AI列顺序存储对应原始权重
    Dim baseWeights As Variant
    baseWeights = Array(0.3, 0.2, 0.2, 0.1, 0.15, 0.05)
    Dim ws As Worksheet
    Dim lRow As Long, i As Long, j As Long
    Dim naTotalWeight As Single, naCount As Integer, addPerValid As Single
    Dim finalScore As Single, cellVal As Variant
    
    ' 可修改为实际工作表名称，例如 Sheets("评分数据表")
    Set ws = ActiveSheet
    ' 自动识别AD列最后一行数据位置
    lRow = ws.Cells(ws.Rows.Count, "AD").End(xlUp).Row
    
    ' 从第2行开始逐行计算（第1行默认是表头）
    For i = 2 To lRow
        naTotalWeight = 0
        naCount = 0
        finalScore = 0
        
        ' 第一轮遍历当前行6个分类列，统计N/A总权重和N/A列数量
        For j = 0 To 5
            cellVal = ws.Cells(i, "AD").Offset(0, j).Value
            ' 兼容N/A大小写、前后带空格的输入情况
            If UCase(Trim(CStr(cellVal))) = "N/A" Then
                naTotalWeight = naTotalWeight + baseWeights(j)
                naCount = naCount + 1
            End If
        Next j
        
        ' 处理6列全为N/A的极端场景，避免除0错误
        If naCount = 6 Then
            ws.Cells(i, "AJ").Value = "N/A"
            GoTo NextRowLoop
        End If
        
        ' 计算每个有效列需要追加的权重
        addPerValid = naTotalWeight / (6 - naCount)
        
        ' 第二轮遍历计算加权总分
        For j = 0 To 5
            cellVal = ws.Cells(i, "AD").Offset(0, j).Value
            If UCase(Trim(CStr(cellVal))) <> "N/A" Then
                finalScore = finalScore + CSng(cellVal) * (baseWeights(j) + addPerValid)
            End If
        Next j
        
        ' 将最终得分写入AJ列对应行
        ws.Cells(i, "AJ").Value = finalScore
NextRowLoop:
    Next i
    
    MsgBox "计算完成，共处理 " & lRow - 1 & " 行数据"
End Sub

代码说明 #

• 不需要针对N/A的组合写分支判断，无论几列是N/A都能自动按规则平均分配N/A列的权重到剩余有效列
• 自动适配数据行数，1000行甚至更多数据都可以直接运行，不需要调整代码
• 做了输入兼容处理，不会因为N/A大小写、前后有空格导致判断失效
• 预留了全N/A行的处理逻辑，不会触发运行时错误
• 后续如果要调整权重、增减分类列，只需要修改baseWeights数组和对应列引用即可，维护成本很低

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