将单个单元格引用转换为整列引用Excel VBA

Question

所以我有一个公式，它考虑了其他单元格(L：L 和 K：K(中的另外两个给定值来确定分数。现在它仅适用于指定的引用单元格(L2、K2、M2(，我正在尝试让它适用于整个列，而不是仅适用于一列。

这是VBA代码：

Sub ExplicitScore()
    If Range("L2").Value = "Healthcare" Then
        Select Case Range("K2").Value
        Case "Executive"
            Range("M2").Value = 60
        Case "IT", "Clinical", "Finance/Revenue Cycle", "Security/Risk/Compliance", "Patient Access/Records"
            Range("M2").Value = 50
        Case "HIM", "Patient Quality/Safety"
            Range("M2").Value = 40
        Case "Pharmacy", "Telecommunications", "Admin"
            Range("M2").Value = 20
        Case Else
            Range("M2").Value = 10
        End Select
    Else
        Select Case Range("K2").Value
         Case "Executive", "IT"
            Range("M2").Value = 10
        Case Else
            Range("M2").Value = 0
        End Select
    End If
End Sub

Answer 1

一个很好的解决方法是创建一个函数，而不是一个子函数。 将条件设置为变量。这是转换后的代码：

Function ExScore(Industry, JobCategory)
    If Industry = "Healthcare" Then
        Select Case JobCategory
        Case "Executive"
            ExScore = 60
        Case "IT", "Clinical", "Finance/Revenue Cycle", "Security/Risk/Compliance", "Patient Access/Records"
            ExScore = 50
        Case "HIM", "Patient Quality/Safety"
            ExScore = 40
        Case "Pharmacy", "Telecommunications", "Admin"
            ExScore = 20
        Case Else
            ExScore = 10
        End Select
    Else
        Select Case JobCategory
         Case "Executive", "IT"
            ExScore = 10
        Case Else
            ExScore = 0
        End Select
    End If
End Function