VLookup using VB

[Living the Dream]

Hi all

Was hoping someone could correct my seemingly non-working attempt at the
subject matter.

Dim sMain As Worksheet, sExtra As Worksheet
Dim myDMrng As Range, myDErng As Range
Dim c As Range
Dim Col As Integer

Set sMain = Sheets("Main")
Set sExtra = Sheets("Extra")

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

For Each c In myDMrng
If c.Offset(0, -1) <> "" Then
With c
.Value = WorksheetFunction.VLookup(c.Offset(0,
-1).Value, myDErng, Col, 0)
End With
End If
Next c

I was inserting RC.formula with the Vlookup, then doing a copy/paste
throughout the balance of the range but it takes way too long hence why
I am trying something different to speed things up.

TIA
Mick.

 

[Claus Busch]

Hi Mick,

Am Wed, 10 Oct 2012 20:53:00 +1100 schrieb Living the Dream:

For Each c In myDMrng
If c.Offset(0, -1) <> "" Then
With c
.Value = WorksheetFunction.VLookup(c.Offset(0,
-1).Value, myDErng, Col, 0)
End With
End If
Next c

try:
For Each c In myDMrng
With c
If WorksheetFunction.CountIf(myDErng, .Offset(0, -1)) > 0 Then
.Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, _
myDErng, Col, 0)
End If
End With
Next c


Regards
Claus Busch

 

[Living the Dream]

try:
For Each c In myDMrng
With c
If WorksheetFunction.CountIf(myDErng, .Offset(0, -1)) > 0 Then
.Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, _
myDErng, Col, 0)
End If
End With
Next c


Regards
Claus Busch

Hi Claus

Thank you for that, alas it did not work as it halted.

Probably need to clarify it more.

The offset cell is a text value and not numeric.

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.

Appreciate your time.

Mick.

 

[Claus Busch]

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

 

[Living the Dream]

Hi Mick,

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


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.

 

[Living the Dream]

Hi Claus

And for anyone else interested in how this issue went.

I ended up using the Find/Match approach and then offset to the next
cell which works really well, and fairly quickly also.

Thanks again for your efforts.

Mick.