VLookup using VB
 

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.

VLookup using VB
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

VLookup using VB
 

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.

VLookup using VB
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

VLookup using VB
 

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.

VLookup using VB
 

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.