Range(Cell.Find(“价格标签”), Range(Cells.Find(“价格标签”)).End(xlDown))

Question

我想找到“价格标签”在工作表上的位置，然后按照该列一直向下选择。 我写了

Range(Cells.Find("Price tag"), Range(Cells.Find("Price Tag")).End(xlDown))

但我收到了 [range method of object _global failed] 消息。 代码有什么问题，我该如何修复它？

Answer 1

使用 Find 方法

• 如果未找到搜索值，则结果将为 Nothing，因此最安全的做法是在 Find 方法中使用范围变量，然后测试针对 Nothing 的变量。

Option Explicit

Sub Test()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim ColumnRange As Range

    Dim fCell As Range ' simplified due to assuming it is never in cell `A1`
    Set fCell = ws.Cells.Find("Price Tag", , xlFormulas, xlWhole, xlByRows)
    
    ' Decide what to do if found or not.
    If fCell Is Nothing Then
        MsgBox "'Price Tag' not found.", vbCritical
        Exit Sub
    Else
        Set ColumnRange = ws.Range(fCell, fCell.End(xlDown))
        MsgBox "'Price Tag' was found in cell '" & fCell.Address(0, 0) _
            & "' and the address of your range is '" _
            & ColumnRange.Address(0, 0) & "'.", vbInformation
    End If
        
    ' But usually you know in which row...
    With ws.Rows(1)
        Set fCell = .Find("Price Tag", .Cells(.Cells.Count), xlFormulas, xlWhole)
    End With
    
    ' or in which column it is:
    With ws.Columns("A")
        Set fCell = .Find("Price Tag", .Cells(.Cells.Count), xlFormulas, xlWhole)
    End With
    
End Sub

Answer 2

此功能将为您扩展选择范围。

Public Function DataCells(Source As Range) As Range
    Dim ColUsedRange As Range
    Dim Col As Range
    Dim RowCount As Long
    
    For Each Col In Source.Columns
        Set ColUsedRange = Range(Col, Col.EntireColumn.Cells(Source.Parent.Rows.Count).End(xlUp))
        If RowCount < ColUsedRange.Rows.Count Then RowCount = ColUsedRange.Rows.Count
    Next
    
    Set DataCells = Source.Resize(RowCount)
End Function

用法

Sub Test()
    Dim Target As Range
    Set Target = Cells.Find("Price tag")
    
    If Not Target Is Nothing Then
        Set Target = DataCells(Target)
        Application.Goto Target
    End If
End Sub