This macro will return the location (row, column or address) of the cell containing the search value.
Function Find_Row_Num(What_2_Find) What_2_Find = "Insert row before this line for PAPDR" Set Which_Worksheet_2_Find = ThisWorkbook.Worksheets("Details") Set Range_2_Find = Which_Worksheet_2_Find.Range("C30:C65000") ' this is a range. Value_or_Formula = xlFormulas 'xlVaues - search the text/value in the cell, 'for cells with formula, it will look at the result. 'xlformulas - search the text/value within formula 'xlformulas works even when cell is hidden. 'It is able to look for value too. Exact_Partial = xlPart 'xlPart - find cells that contains (What_2_Find) 'xlWhole - find cells which contains 'exactly the value placed in (What_2_Find) Match_Capital_Letters = False 'False - when A and a is treated the same ' True when A and a means different things. With Range_2_Find Set c = .Find(What:=What_2_Find, LookIn:=Value_or_Formula, _ LookAt:=Exact_Partial, MatchCase:=Match_Capital_Letters) 'c is the cell that meet your find criteria If Not c Is Nothing Then Find_Row_Num = c.Row ' Change this to c.column if you want the column ' where the text is found. 'change it to c.address if you want the address returned. Else Find_Row_Num = 1 ' Cannot find, so default the address to A1 End If End With End Function