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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi Bob,

Sorry,

I tried to use the code but it all got mixed up.
Probably because i didn't use it in the right way. I got an error message
each time i tried the code, spent hours to try to adjust it but it did not
work at all.
Part of the problem, i think was the activecell part in your code, because
cell A20 is not the active cell when the code starts to run.
furthermore i get an error message "wrong procedure call or invalid
argument"
at the 'set cell' line of your code.
Also i forgot to tell you that i needed the code to run several times
(A20:
A30) and put the result of each calculation in (c20:c30)

Sorry to bother you again with more or less the same question but i just
cannot seem to get i right.
Please help again ?

Pierre

Bob Phillips wrote:
Tom and I have already tried to help you with this problem. What is wrong
with the offerings so far?

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre


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