VBA代码返回字符串作为范围位置的变量

您需要的是快速检查函数,以查看命名范围是否存在。你的测试 Range(LocationRow).Value <> 0 如果未定义该名称,将引发错误。

试试这个

Public Function NameIsDefined(ByVal name As String) As Boolean
    On Error Resume Next
    Dim notUsed As Long
    notUsed = Range(name).Rows.Count

    If Err = 1004 Then
        NameIsDefined = False
        Exit Function
    Else
        NameIsDefined = True
    End If
    On Error GoTo 0
End Function

接下来,您必须检查该范围内的所有单元格以获取数据(可能是从您的示例来看,所有单元格的值都必须大于零):

Public Function CellsHaveData(ByRef dataArea as Range) As Boolean
    CellsHaveData = True
    Dim dataCell As Variant
    For Each dataCell In dataArea
        If dataCell.Value = 0 Then
            '--- if only one cell in the range is not zero, then
            '    we don't have data
            CellsHaveData = False
            Exit Function
        End If           
    Next dataCell
End Function

你自己的潜艇看起来

Private Sub OK_Click()
    Application.ScreenUpdating = False
    Row = ComboBox_Location.Value
    LocationRow = "Location_" & Row
    UPCRow = "UPC_" & Row

    If NameIsDefined(LocationRow) Then
        If Not CellsHaveData(Range(LocationRow)) Then
            Dim answer As VbMsgBoxResult
            answer = MsgBox("This row already has data. Are you sure you want to " & _
                            "clear it and begin with a new UPC?", vbYesNo)
            If answer = vbYes Then
                Range(UPCRow).Value = ComboBox_UPC.Value
                Range(LocationRow).Value = 0
            ElseIf answer = vbNo Then
                'Do Nothing
            End If
        Else
            Range(UPCRow).Value = ComboBox_UPC.Value
            Range(LocationRow).Value = 0
        End If
    Else
        MsgBox "Named Range " & LocationRow & " does not exist.", vbOK
    End If

    ComboBox_Location.Clear
    ComboBox_UPC.Clear
    Corresponding_Material.Value = ""
    Corresponding_Material_Description.Value = ""

    Change_Material.Hide
    Application.ScreenUpdating = True
End Sub