时间戳

首页 > 解决方案 > Excel在函数VBA中使用列字母

excel - Excel在函数VBA中使用列字母

问题描述

我正在尝试创建一个 VBA 函数，该函数为每一列调用另一个函数。这个想法是，对于 thisRange 的每个元素（它们都将是同一行的元素），我检查 PassableSteps 数量中的所有元素是否等于非空单元格的数量。我的问题是调用以 Range 作为参数的 CountPassableSteps 不起作用并返回 #VALUE！错误。我有一种感觉，这是一个简单的修复，但不知道如何去做。问题可能来自 NbNotEmpty 的计算。

Public Function countPassableSteps(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            myVar = Application.WorksheetFunction.VLookup(rngCell.Value, Worksheets(1).Range("A2:C302"), 3, False)
            If myVar = True Then
                lColorCounter = lColorCounter + 1
            End If
        End If
    Next
    countPassableSteps = lColorCounter
End Function


Public Function countPassableTests(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            Dim ColumnNumber As Long
            Dim ColumnLetter As String
            ColumnNumber = rngCell.column
            ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
            Dim NbStepsPassable As Long
            NbStepsPassable = countPassableSteps(Range("ColumnLetter & 2: ColumnLetter & 105"))
            Dim NbNotEmpty As Long
            NbNotEmpty = WorksheetFunction.CountA(Range("ColumnLetter & 2: ColumnLetter & 105"))
            If NbStepsPassable = NbNotEmpty Then lColorCounter = lColorCounter + 1
        End If
    Next
    countPassableTests = lColorCounter
End Function


Public Function countEmptyTests(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            Dim NbNotEmpty As Long
            NbNotEmpty = WorksheetFunction.CountA(Range("ColumnLetter & 2: ColumnLetter & 105"))
            If NbNotEmpty = 0 Then lColorCounter = lColorCounter + 1
        End If
    Next
    countEmptyTests = lColorCounter
End Function

提前致谢！

标签： excelvbaexcel-formula

解决方案

ColumnLetter不应该在引号内。

所以像这样的事情，

Range("ColumnLetter & 2: ColumnLetter & 105")

应该是这样的。

Range(ColumnLetter & "2:" & ColumnLetter & "105")

顺便说一句，您可以不ColumnLetter使用Intersect.

Intersect(rngCell.EntireColumn, Range("2:105"))

推荐阅读