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,

Rather than having a macro designed to search for a single value, how about a function that will return the entire cell value, using the referenced value to find it?

Function ReturnAll(rng As Range)
'
    Dim rFound As Range
        
    Set rFound = Worksheets("Imported_ORC").Cells.Find(rng.Value)
    If Not rFound Is Nothing Then
        ReturnAll = rFound.Value
    End If
    
End Function

Paste this function into a MODULE in your VB Project.

Use the function like you would any other spreadsheet function on your sheet, for instance, in the adjacent cell to your lookup value, referencing your lookup value.

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