Thanks Bob, it works like a charm !!!!
much obliged,
Jean-Pierre

Bob Phillips wrote:
So is this closer

Sub GetVal()
Dim var1 As Long
Dim Var2 As Long
Dim rng As Range
Dim cell As Range
Dim res As Variant
Dim rw As Long, result As Variant
var1 = 31 'replace with real value
Var2 = 32 'replace with real value
For Each rng In Range("A20:A30")
res = InStr(1, rng.Value, " -")
If res 0 Then
Set cell = Worksheets("Data").Range("A4:A53"). _
Find(CLng(Left(rng.Value, res - 1)))
If Not cell Is Nothing Then
result = Worksheets("Data").Cells(cell.Row, cell.Column - 58
+ var1 + Var2)
rng.Offset(0, 2).Value = result
End If
End If
Next rng
End Sub

Hi Bob,

[quoted text clipped - 25 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1