如何使用命名范圍作為驗證參考

希望我能在這里得到幫助，我目前在 VBA 代碼中使用 Dim Long，但由于我指的是多列，代碼變得很長。現在，我想嘗試命名范圍參考，但我無法使其作業。這是我當前的代碼：

Dim i As Long
For i = 8 To 500

    'if details is incomplete
    If Range("AA" & i).Value > 0 Then
        If Range("AB" & i).Value = "Error" Or Range("AC" & i).Value = "Error" Or Range("AD" & i).Value = "Error" _
        Or Range("AE" & i).Value = "Error" Or Range("AF" & i).Value = "Error" Or Range("AG" & i).Value = "Error" _
        Or Range("AH" & i).Value = "Error" Or Range("AI" & i).Value = "Error" Or Range("AJ" & i).Value = "Error" _
        Or Range("AK" & i).Value = "Error" Or Range("AL" & i).Value = "Error" Or Range("AM" & i).Value = "Error" _
        Or Range("AN" & i).Value = "Error" Or Range("AO" & i).Value = "Error" Or Range("AP" & i).Value = "Error" _
        Or Range("AQ" & i).Value = "Error" Or Range("AR" & i).Value = "Error" Or Range("AS" & i).Value = "Error" _
        Or Range("AT" & i).Value = "Error" Or Range("AU" & i).Value = "Error" Or Range("AV" & i).Value = "Error" _
        Or Range("AW" & i).Value = "Error" Or Range("AX" & i).Value = "Error" Or Range("AY" & i).Value = "Error" Then
            MsgBox "One of the mandatory field is not provided, please check all cells highlighted in yellow & make sure details is provided."
     End If
Endif

我命名范圍 AA = "Validation" & range AB:AY = "Details" 我如何宣告它并使用命名范圍而不是一一寫入每一列？

uj5u.com熱心網友回復：

正如@Ike 所建議的那樣 - 使用 COUNTIF 公式。可用于作業表或 VBA 中。如果您想回傳每個錯誤的地址，那么Find可能是一個更好的路由。

Sub Test()
    Dim Result As Long
    Result = Errors(Sheet1.Range("AB8:AY500"))
    If Result > 0 Then
        MsgBox "There are " & Result & " errors in the range."
    End If
End Sub

Public Function Errors(Target As Range) As Long
    Errors = WorksheetFunction.CountIf(Target, "Error")
End Function

uj5u.com熱心網友回復：

條件格式可以解決這個問題。我已經證明了一個較小的范圍。隨意將其應用于您所需的范圍。

非 VBA

使用的公式： =AND($AA8>0,AB8="Error")

VBA

您也可以在 VBA 中使用條件格式。

這是你正在嘗試的嗎？我已經評論了代碼。如果您有任何問題，請告訴我。

Option Explicit

Sub Sample()
    Dim i As Long
    Dim ws As Worksheet
    Dim CondTrue As Boolean
    
    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        '~~> Check if there is even one cell which satisfies our condition
        For i = 8 To 500
            If .Evaluate("=AND(AA" & i & ">0,COUNTIF(AB" & i & ":AY" & i & ",""Error"")>0)") = True Then
                CondTrue = True
                Exit For
            End If
        Next i
        
        '~~> If found then apply conditional formatting
        If CondTrue Then
            With .Range("AB8:AY500")
                .FormatConditions.Delete
                
                .FormatConditions.Add Type:=xlExpression, _
                                      Formula1:="=AND($AA8>0,AB8=""Error"")"
                
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                
                With .FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                End With
                .FormatConditions(1).StopIfTrue = False
            End With
            
            '~~> Show message box
            MsgBox "One of the mandatory field is not provided, please check all cells highlighted in yellow & make sure details is provided."
        Else
            MsgBox "All Good!"
        End If
    End With
End Sub

在行動（VBA）

標籤：擅长 vba 验证 范围 命名范围

上一篇：通過條件格式Excel根據值的長度向值添加前綴

下一篇：在列中查找下一個非空單元格