Retrieve Data Using Find Function And Selecting Nearby Cells

Thanks a lot for the guidance. Is this how you are supposed to post the code? Sorry, I am new at this and really appreciate the help.

    ActiveSheet.Next.Select
    Cells.Find(What:="name and address", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("B14").Select
    Selection.Copy
    ActiveSheet.Previous.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select

I've also added the attachment. I had to delete the actual data but if you are trying to pull information from cells in column B or C based on their relation to the cell with the control find. Hopefully that gives you a more clear picture.

Again thanks for the help and let me know if I'm missing anything.

I don't know where you are searching or where you want to paste the found values, but perhaps this will start you off:

Sub AHDUpdate()

Dim rFind As Range

With Sheets("Profile").Columns(1)
    Set rFind = .Find(What:="name and address", LookAt:=xlPart, _
                      MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        rFind.Offset(, 2).Copy
        Sheets("Financial Statements").Range ("A1")
    End If
End With

End Sub

Hi Stephen,
That is a good start, unfortunately I’m afraid I’m not able to expand upon the initial code you provided. I’ll be a bit more explicit in what I am trying to do and that should help. I’m looking to do the following steps (actually I’ll be doing a bit more than this but figure if you show me a few I can figure out the rest)

1) Find “name and address” on the profile tab in column A, copy the value in the adjacent cell (to the right) and paste this on cell B2 of the Compiled Data tab (this cell is fixed and will not change / this is a new tab I inserted in the workbook).
2) Next find “name and address” on the profile tab in column A, copy the value in the cell which is one to the right and one down and paste special this value in cell B3 of the Compiled Data tab (this cell is fixed and will not change).
3) Next find “period ending date” on the Financial statements tab and copy the value in the adjacent cell (one to the right) and past this value on B4 of the compiled data tab (again this cell is fixed)
4) Next search for “Health Care System” in column A on the profile tab and copy the value in the adjacent cell and paste it on cell B5 of Compiled data. If this search does not find anything, do not paste any value.

Hopefully that should be enough detail. Any help on this would be very appreciated. Also, how would I end the code, is there anything special I would need to do. If you can’t tell I’m pretty new to VBA and really appreciate the help.

Thanks!!!

Try this:

Sub AHDUpdate()

Dim rFind As Range

With Sheets("Profile").Columns(1)
    Set rFind = .Find(What:="name and address", LookAt:=xlPart, _
                      MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        rFind.Offset(, 1).Copy Sheets("Compiled Data").Range("B2")
        rFind.Offset(1, 1).Copy Sheets("Compiled Data").Range("B3")
    End If
End With

Set rFind = Nothing

With Sheets("Financial Statements").Columns(1)
    Set rFind = .Find(What:="period ending date", LookAt:=xlPart, _
                      MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        rFind.Offset(, 1).Copy Sheets("Compiled Data").Range("B4")
    End If
End With

Set rFind = Nothing

With Sheets("Profile").Columns(1)
    Set rFind = .Find(What:="Health Care System", LookAt:=xlPart, _
                      MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        rFind.Offset(, 1).Copy Sheets("Compiled Data").Range("B5")
    End If
End With

End Sub

Thanks for the help. That worded great!