Excel VBA问题：基于找到的单元格创建命名区域失败求助

Let's walk through what's broken in your current code and get it working to create that named range for the full row where your ISBN match is found:

Key Issues in Your Original Code #

• Invalid property name: Cell.RowIndex doesn't exist in VBA. The correct property to get a cell's row number is Cell.Row — this is almost certainly causing your code to throw an immediate error.
• Missing named range creation: You've identified the matching row and highlighted it, but you haven't added the code to actually create a named range for that row (your core requirement).
• Unclear variable safeguards: Without explicit variable declarations, typos or undefined values can sneak in. Always use Option Explicit at the top of your module to catch these early.

Corrected Full Code #

Option Explicit

Sub CreateISBNNamedRange()
    Dim ISBN_Range As Range
    Dim Cell As Range
    Dim ISBN As String ' Adjust data type if your ISBN uses numbers
    Dim ISBN_Found As Range
    Dim ISBN_Valid As Boolean
    
    ' Customize these values to match your workbook
    ISBN = "9781234567890" ' Replace with your target ISBN
    Set ISBN_Range = Sheet1.Range("A:A") ' Replace with your actual search range
    
    ISBN_Valid = False
    
    For Each Cell In ISBN_Range
        ' Skip empty cells to avoid unnecessary comparisons
        If Not IsEmpty(Cell.Value) And Cell.Value = ISBN Then
            ISBN_Valid = True
            ' Define the full A-E row for the matched cell
            Set ISBN_Found = Sheet1.Range("A" & Cell.Row & ":E" & Cell.Row)
            
            ' Keep your highlight logic (optional)
            ISBN_Found.Interior.ColorIndex = 6
            
            ' Create the named range for the matched row
            ThisWorkbook.Names.Add _
                Name:="MatchedISBNRow", _
                RefersTo:=ISBN_Found, _
                Comment:="Full row containing target ISBN"
                
            Exit For ' Stop searching after the first match
        End If
    Next Cell
    
    ' Optional: Alert if no match was found
    If Not ISBN_Valid Then
        MsgBox "No matching ISBN found in the specified range."
    End If
End Sub

What This Code Does #

• Fixes the row reference: Uses Cell.Row to correctly target the matched cell's row number.
• Adds named range creation: The ThisWorkbook.Names.Add line creates a persistent named range pointing to your matched A-E row.
• Adds error prevention: Skips empty cells to avoid invalid comparisons, and includes a user notification for no matches.
• Enforces clean code: Option Explicit ensures all variables are declared, catching typos before they break your code.

Quick Adjustments for Your Workbook #

• Replace Sheet1 with your actual worksheet's name or code name.
• Update the ISBN value to your target identifier.
• Tweak ISBN_Range to the specific column/range you're searching in.
• Rename MatchedISBNRow to a name that fits your workflow (e.g., OrderRow_9781234567890).

内容的提问来源于stack exchange，提问作者Mike H