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?
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
Findmethod, and then test the variable against
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
Answered By – VBasic2008
Answer Checked By – Mildred Charles (AngularFixing Admin)