Below is the code that does work but I would like a formula (if possible) to find specific text and offset one row and return the value in the offset cell, is that possible?
Thank you for your help
Option ExplicitSub FindAllOnBoard()
'Defining the variables.
Dim GCell, GCell1, GCell2, GCell3, GCell4, GCell5, GCell6, GCell7 As Range
Dim Page$, Txt$, Txt1, Txt2, Txt3, Txt4, Txt5, Txt6, Txt7, MyPath$, MyWB$, MySheet$
Dim DestSh As Worksheet
ThisWorkbook.Sheets("Lookup").Range("B42").ClearContents
Set DestSh = ThisWorkbook.Worksheets("Lookup")
'The text for which to search.
Txt = "Employee ID"
'Turn off screen updating, and then open the target workbook.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Search for the specified text
Set GCell = Sheets("CognosIntermData").Cells.Find(Txt, LookIn:=xlValues, LookAt:=xlWhole) '.Cells.Find(Txt) 'ActiveSheet
'Record the address of the data, along with the date, in the current workbook.
'With ThisWorkbook.Sheets("Lookup Data").Range("B1")
ThisWorkbook.Sheets("CognosIntermData").Activate
If GCell <> "" Then
DestSh.Range("B42").Value = GCell.Offset(1, 0)
End If
End Sub