Retrieve Data Using Find Function And Selecting Nearby Cells

[csukenik]

Hi,

I'm trying to retrieve data from worksheets in reports in excel which I will have to do from time to time. The best way I can think to do this is using control + find functions to find the specific data element I am looking for and then to select a nearby cell based on this cell. I am thinking - if my find search takes me to cell A21, then I want to select cell C21. I can't figure out how to do this though. My VBA code looks like below:

Sub
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
End Sub

I know I need to do something with the 'Range("B14").Select' line but don't know what. I've tried 'ActiveCell.Offset(0, 1).Select' but can't get this to work. Any help/advice would be greatly appreciated...

Thanks!!!

[TMS]

Welcome to the forum.

Please take a moment to read the forum rules and add CODE tags to your code example.

It would also be helpful, at least for me, if you would post a sample workbook with some typical data. Then the code can be seen in context.

In theory, no one can/will provide an answer until you add the code tags ...

Regards

[csukenik]

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.

Please Login or Register  to view this content.

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.

[StephenR]

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

Please Login or Register  to view this content.

[csukenik]

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!!!

[StephenR]

Try this:

Please Login or Register  to view this content.

[csukenik]

Thanks for the help. That worded great!