Help with VLOOKUP / VBA code, please...

[Bruise]

Hello!

I received assistance in an earlier post that helped tremendously, but I'm
having an additional problem with the code I received.

Situation: I import a 3 column list into a worksheet in Excel. On several
other sheets, I run a VLOOKUP code in VBA to auto-enter data. When certain
criteria is entered in cell A1, VLOOKUP enters the corresponding results
into the same row into B1 and C1. I'm using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant
If Target.Address = "$A$1" Then
Set rng = Worksheets("Sheet2").Range("A1:C1800")
res = Application.VLookup(Target, rng, 2, False)
If IsError(res) Then
Range("B1:C1").Value = "Manual Entry Required"
Else
Range("B1").Value = res
Range("C1").Value = Application.VLookup(Target, _
rng, 3, False)
End If
End If
End Sub

This code only targets cell A1 on the current worksheet. I need it to
target about 100 rows of column A so if the data entered in cell A55 is
different than the data entered in cell A1, it returns the proper info for
data entered in A55.

I've tried using several Target.Range codes, to no avail.

Any help would greatly be appreciated.

 

[Dave Peterson]

Untested...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant

if target.cells.count > 1 then exit sub
if intersect(target,me.range("a1:A100")) is nothing then exit sub

Set rng = Worksheets("Sheet2").Range("A1:C1800")

res = Application.VLookup(Target, rng, 2, False)

If IsError(res) Then
target.offset(0,1).resize(1,2).Value = "Manual Entry Required"
Else
target.offset(0,1).Value = res
target.offset(0,2).Value = Application.VLookup(Target, rng, 3, False)
End If

End Sub

(Watch for typos!)

 

[Dave Peterson]

Ps. It's probably best to stop the changes the code makes from firing the
event, too:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant

if target.cells.count > 1 then exit sub
if intersect(target,me.range("a1:A100")) is nothing then exit sub

Set rng = Worksheets("Sheet2").Range("A1:C1800")

res = Application.VLookup(Target, rng, 2, False)

application.enableevents = false
If IsError(res) Then
target.offset(0,1).resize(1,2).Value = "Manual Entry Required"
Else
target.offset(0,1).Value = res
target.offset(0,2).Value = Application.VLookup(Target, rng, 3, False)
End If
application.enableevents = true

End Sub

(I added the .enableevents lines.)

 

[Bruise]

Tested...and it works beautifully!

Thank you, Dave!

 

[Bruise]

Out of curiousity, why would I want to do this? What are the consequences
if I don't add this code?

I'm just trying to learn more about this. Thanks.

 

[Dave Peterson]

When you change a cell (or even when the code changes the cell), the event will
fire.

In this case, since you're changing something in column B and C, the code fires,
but exits pretty fast--as soon as that check for the correct column is made.

But you/your code could get into trouble under certain conditions.

Try this in a test worksheet.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Target.Offset(1, 0).Value = "hi there"

End Sub

And make a change to A1. The code will be called over and over and over--until
excel gets tired and gives up. (In theory, the code could go until it runs out
of rows and then blows up when it tries to get to the next one.)

So it's better to stop those things from happening.

Make this change and you'll see a difference--maybe even in speed, since the
routine is doing lots less.

application.enableevents = false
Target.Offset(1, 0).Value = "hi there"
application.enableevents = true

 

[Bruise]

Thanks, Dave. I understand that and I can see how it works. I appreciate
the time out to 'teach' me something. That's what keeps us coming back for
more knowledge...

Bruise