Value Lookup across spreadsheets - Macros

[PossiblyNot]

I've been asked to try and manipulate some awkward data into MatLab to plot as along side a Chronostratigraphy Line. (almost identical to this http://nhm2.uio.no/norges/atlas/fig31.jpg)
The issue is all of the data consists the first and last seen Palaeontological zones of various Foraminifera (the vertical black lines) usually expressed as an alphanumeric code, (e.g. "P22" or "Tf3") or as a geological Epoch/Period/Era etc... but to plot the data, I need to convert the data into plotable numerical ages (e.g. 22.4 Mya).

The easiest way to fix this is to simply write in the dates beside every data set, however there are hundreds of the entries, in dozens of files, and this is a very common plot we make, so I've been asked to try and automate it if possible using MatLab to plot. However, the data needs pre-processing to make it usable in MatLab.

What I hope to do is, using a macro, to process the data file, consisting of a start zone, end zone and name per row, with a macro looking up and substituting the start and end zones with the equivalent dates stored in a seperate spreadsheet that consists of two columns, the first listing the zone names (the start and end date of each zone is a different entry to simplify matters) and the second listing the dates.

I'd imagine the process would be to: run the macro from the spreadsheet containing the main data (call it spreadsheet 1); selecting the first zone entry; loading the external zone reference spreadsheet (call it spreadsheet 2); using the zone entry value (in spr. 1) as a search value; finding the cell (in spr. 2) and returning the numerical value value to the right of it; overwriting the alphanumerical entry originally selected in Spr. 1; not overwriting if the search fails; then automatically selecting the next value down the table until it reaches an empty cell; terminating.

an example of spreadsheet 1,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Test1[/TD]
[TD]N4-start[/TD]
[TD]N5-end[/TD]
[/TR]
[TR]
[TD]Test2[/TD]
[TD]Th-start[/TD]
[TD]Tf3-end[/TD]
[/TR]
[TR]
[TD]Test3[/TD]
[TD]Rupellian-start[/TD]
[TD]Lutetian-end[/TD]
[/TR]
[TR]
[TD]Test4[/TD]
[TD]Paleogene-start[/TD]
[TD]Paleogene-end[/TD]
[/TR]
</tbody>[/TABLE]

and of spreadsheet 2

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Zone[/TD]
[TD]Date (Mya)[/TD]
[/TR]
[TR]
[TD]N5-start[/TD]
[TD]21.00[/TD]
[/TR]
[TR]
[TD]N4-end[/TD]
[TD]23.00[/TD]
[/TR]
[TR]
[TD]Th-start[/TD]
[TD]11.60[/TD]
[/TR]
[TR]
[TD]Tf3-end[/TD]
[TD]12.80[/TD]
[/TR]
[TR]
[TD]Rupellian-start[/TD]
[TD]28.40[/TD]
[/TR]
[TR]
[TD]Lutetian-end[/TD]
[TD]47.80[/TD]
[/TR]
[TR]
[TD]Paleogene-start[/TD]
[TD]23.00[/TD]
[/TR]
[TR]
[TD]Paleogene-end[/TD]
[TD]65.40[/TD]
[/TR]
</tbody>[/TABLE]














I'm a little wet behind the ears with using Macros, so even after checking the forum I have been unsuccessful in finding a way to cannibalise what similar solutions I have found here (so apologies for posting such a similar question)

Any help would be immensely appreciated, as I've been wandering about in circles about this for days

Cheers!

 

[hippiehacker]

Sub possiblynot()
Dim rFound As Range
For Each cell In Sheets("Sheet1").Range("B2:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row)
    On Error Resume Next
    With Sheets("Sheet2")
        Set rFound = .Columns(1).Find(What:=cell, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    On Error GoTo 0
        If Not rFound Is Nothing Then
         cell.Value = rFound.Offset(0, 1).Value
        End If
    End With
 Next cell
End Sub

 

[PossiblyNot]

Cheers Hippiehacker for the response.
I've switched the data back to the old format like it is in the OP, and the code isn't kicking out any errors. Frustratingly however, the code doesn't seem to do anything :/ It'll run and terminate and I'll still be seeing the same table I started with.
Regardless, I appreciate the time for the code. Its much cleaner and efficient than something I could have thought up.

Cheers

-PossiblyNot