On 11/10/2012 9:45 PM, Claus Busch wrote:
Hi Mick,

Am Thu, 11 Oct 2012 21:31:51 +1100 schrieb Living the Dream:

An interesting anomaly is that it is not reading the values from the
offset(0, -1) cell, rather it is reading offset(0, -5) and I have no
idea why.

I even tried switching myDMrng around.

Set myDMrng = sMain.Range("$J$2:$J$250")
Set myDErng = sExtra.Range("$A$2:$B$50")
Col = 2

For Each c In myDMrng
If Not c = "" Then
With c
.offset(0, 1).Value = WorksheetFunction.VLookup(c, myDErng, Col, 0)
End With
End If
Next c

But that didn't work either, so it's back to inserting the formulas,
unless you have another idea.

if the value you search for isn't available you get #N/A with formula.
With code you get an error and the makro stops. Therefore I looked with
countif that the value exists. I tried my code with text in column J and
it worked.


Regards
Claus Busch

Thanks again Claus

I will have another crack at it when I get to work in the morning.

Cheers
Mick.