Search cells for text, copy contents adjacent to text

[S4Tyler]

I am working on a macro that will search an imported data file in one tab for a given phrase (Building_length which will always be in column A) then copy the contents of the cell next to it (200 which will always be in column B) and past that value into a given cell on a different tab of the same workbook.

Here is what I have so far:

Sub BuildingLength()
'
' BuildingLength Macro
' Macro recorded 1/11/2010 by TRTurner
'

'
Worksheets("Imported_ORC").Cells("1,1").Select
Cells.Find(What:="Building_Length", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("B1514").Select
Selection.Copy
Sheets("ORC_Data").Select
Range("B5").Select
ActiveSheet.Paste
Sheets("Main").Select
End Sub


The problem is at - Range("B1514").Select -
Rather than always selecting this cell, I need it to select the cell next to the cell containing the search contents.

Once this is working I need to repeat the process for roughly 20 other search critera. Because of this, I am not sure if it would be best to do this with a macro or with some kind of search formula in the desination cell. Either way, I am not really sure how to do this.

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

If you are using VBA code, within Excel, please post in forum707.

Since you have a recorded macro, this seems to be the case.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[S4Tyler]

Post moved.