Function for finding the row of a cell with a specific text

Question

If I want to find the row number of a cell in column A with the text containing the string "Total Labor", how can I do that?

Another is how can I find the position of the cell containing that text if it can be in any column and row?

This is what I have, but it returns as an Empty. TotalLaborPos is defined as a Variant.

lastrow = Range("A11").End(xlDown)
TotalLaborPos.Value = ActiveSheet.Match("Total Labor", Range("A11:A" & lastrow), 0)

Change `"A11:A" & lastrow)` to `"A:A"`. If a match is found, the result is the row number. — BigBen, Aug 12 '21 at 15:44
@BigBen It still gives me an Empty. Do you know how to fix that? — cd18___, Aug 12 '21 at 15:52
What are you trying to do with `TotalLabePos.Value`? Did you mean for `TotalLaborPos` to be a `Range`? If so, did you `Set` it? — BigBen, Aug 12 '21 at 15:53
@BigBen I thought it would give me the row value since it has the .Value, but I may be wrong with the usage of that. — cd18___, Aug 12 '21 at 15:54
Remove the `.Value`. You also want to test if the result of the `Match` is an error, using `IsError`. — BigBen, Aug 12 '21 at 15:55
Demonstrated [here](https://stackoverflow.com/a/17751568/9245853). — BigBen, Aug 12 '21 at 15:59
It still gives me an empty. I'll try another way. Thanks @BigBen — cd18___, Aug 12 '21 at 16:01
@TimWilliams - welp, I glossed right over that, saw the `A` and moved on. — BigBen, Aug 12 '21 at 16:26

Tim Williams · Accepted Answer · 2021-08-12T16:27:53.090

1

It's Application.Match not ActiveSheet.Match

If it can be in any column/row then use Find()

E.g.

Dim f As Range
Set f = ActiveSheet.Cells.Find("Total Labor",lookat:=xlWhole,lookin:=xlValues)
If not f is nothing then
   debug.print "found", f.address
end if

edited Aug 12 '21 at 16:27

answered Aug 12 '21 at 16:18

Tim Williams

154,628
8
97
125

score 0 · Answer 2 · answered Aug 13 '21 at 00:11

The Worksheet Row of the First Match in a Column

Option Explicit

Sub GetFirstMatchInColumnRowTEST()
    
    Const First As String = "A11"
    Const StringToMatch As String = "Total Labor"
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim fCell As Range: Set fCell = ws.Range(First)
    
    Dim mRow As Long: mRow = GetFirstMatchInColumnRow(fCell, StringToMatch)
    If mRow = 0 Then Exit Sub ' not found
    
    ' Continue with code...
    Debug.Print ws.Cells(mRow, fCell.Column).Address(0, 0)
    
End Sub

Function GetFirstMatchInColumnRow( _
    ByVal rg As Range, _
    ByVal StringToMatch As String) _
As Long
    
    If rg Is Nothing Then Exit Function ' no range
    
    ' Create a reference to the Search (Column) Range ('srg').
    Dim wsrCount As Long: wsrCount = rg.Worksheet.Rows.Count
    Dim fRow As Long: fRow = rg.Row
    Dim srCount As Long: srCount = wsrCount - fRow + 1
    Dim srg As Range: Set srg = rg.Cells(1).Resize(srCount)
    
    ' 1.) Using 'Range.Find'.
    Dim mCell As Range: Set mCell = srg.Find(StringToMatch, _
        srg.Cells(srg.Cells.Count), xlFormulas, xlWhole)
    If mCell Is Nothing Then Exit Function ' not found
    
    GetFirstMatchInColumnRow = mCell.Row
    
'    ' 2.) Using 'Application.Match'                                                                                                                                                                                                                                                                                                                                                                                                                                                 '
'    Dim rIndex As Variant: rIndex = Application.Match(StringToMatch, srg, 0)
'    If IsError(rIndex) Then Exit Function ' not found
'
'    GetFirstMatchInColumnRow = srg.Cells(rIndex).Row
    
End Function

Function for finding the row of a cell with a specific text

2 Answers2

The Worksheet Row of the First Match in a Column