I want to find where 'price tag' is on the sheet, and follow that column to select all way down. I have wrote

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

but I got [range method of object _global failed] message. What is wrong with the code, and how can I fix it?

CodePudding user response：

Using the Find Method

• If the searched value is not found, the result will be Nothing, so it is safest to use a range variable with the Find method, and then test the variable against 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

CodePudding user response：

This function will extend the selections for you.

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

Usage

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