代码之家 › 专栏 › 技术社区 › ezeagwulae

VBA基于排名分配值

ranking distribution excel vba

ezeagwulae · 技术社区 · 7 年前

尝试完成一次每3行处理一次的VBA。使用的顺序等级柱分配这个 价值观 相应地,在接下来的三行中,每个单元格都不超过最大值62,并对最高排名进行优先级排序。

样本数据:

以下是我目前掌握的情况:

max_value = 62
For irow = 2 To 80 Step 3

    set_value = .Cells(irow, 2).Value

    'if value less than max, then assign value to highest rank
    If set_value < max_value Then
        toprank_value = .Range(.Cells(irow, 1), .Cells(irow + 3, 1)).Find(what:="1", LookIn:=xlValues).Address

        'assign value to rank of 1
        toprank_value.Offset(0, 2).Value = set_value

        GoTo NextIteration

    'if not, distribute values across next 3 rows based on rank not going over max of 62
    Else

        'NEED HELP FOR CODE HERE
        'NEED HELP FOR CODE HERE

    End If

NextIteration:
    Next

感谢您向正确方向的任何推动,或者如果需要澄清的话。

1 回复 | 直到 7 年前

AmBo 7 年前

假设要分配的值始终位于3行中的第一行。这很难看,但似乎有用。

Sub distrib()

Set R1 = ActiveSheet.UsedRange 'Edit range if other data in sheet
T1 = R1

M = 62

For i = 2 To UBound(T1)
    If T1(i, 2) > 0 Then
        V = T1(i, 2)
        If V <= M Then
            For j = i To i + 2
                If T1(j, 1) = 1 Then
                    T1(j, 3) = V
                Else
                    T1(j, 3) = 0
                End If
            Next j
        Else
            A = M
            V = V - M
            If V > M Then
                B = M
                V = V - M
                If V > M Then
                    C = M
                Else
                    C = V
                End If
            Else
                B = V
                C = 0
            End If
            For j = i To i + 2
                Select Case T1(j, 1)
                    Case Is = 1
                        T1(j, 3) = A
                    Case Is = 2
                        T1(j, 3) = B
                    Case Is = 3
                        T1(j, 3) = C
                End Select
            Next j
        End If
    End If
Next i

For i = 2 To UBound(T1)
    Cells(i, 3) = T1(i, 3)
Next i

End Sub

推荐文章