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

Question

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?

Answer 1

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