Using the "find" method to locate a cell with a particular value

Starting in cell D10, I want to come over two columns to the left as in offset(0,-2) which puts me in Column B. Within column B there are various values and empty cells. I want to find the cell that shows "TOTAL COSTS" under Column B.

After locating TOTAL COSTS in Column B, I want to come over 7 columns to the right which puts me in Column I and take the value in that cell in Column I which is on the same row as the cell for TOTAL COSTS. So from TOTAL COSTS it would be offset(0,7).

I am using an object variable named "c" to represent D10. I tried c.offset(0,-2).find (what:="TOTAL COSTS", xxxx,xxxx,xxxx,xxxx,xxxx). I just can't get the "find" method to work, which would enable me to zero in on the cell containing the text "TOTAL COSTS" which is in the same column (B).

Any suggestions?

You say you want to start in column D, then move 2 columns to the left... I think you're forgetting to explain a step, or why would you not just start in column B?

Hello Excel_vba,

Here is macro that places the value in column "I" into the variable "X" once the string "Total Costs" has been found in column "B".

Sub FindTest()

  Dim RngB As Range
  Dim RngEnd As Range
  Dim TotalCell As Range
  Dim X As Double
  
   'Define the search range from B10 to the last entry in column "B"
    Set RngB = Cells(10, "D")
    Set RngEnd = Cells(Rows.Count, "B").End(xlUp)
    Set RngB = IIf(RngEnd.Row < RngB.Row, RngB, Range(RngB, RngEnd))
    
     'Search column "B" only for the string "Total Costs", case is ignored
      Set TotalCell = RngB.Find("Total Costs", , xlValues, xlWhole, xlByRows, xlNext, False)
      If Not TotalCell Is Nothing Then
         X = TotalCell.Offset(0, 7)
      End If
      
End Sub