Re: What is wrong with this code?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Then you must have the code in the wrong place. Were have you stored it?

BTW, shouldn't you be using the Change event?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ayo" <Ayo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3D25A5D9-8FA6-48E7-B1C6-4167C7C21A09@xxxxxxxxxxxxxxxx
Thanks Dave for the advise. I made the changes you suggested but I'm still
not getting and result or reaction from th e code. Nothing is happening,
at
all.

"Dave Peterson" wrote:

I'd stay away from application.worksheetfunction. If there is no match,
you'll
get a runtime error.

And you have an extra "End If" in your code. That "If .count > 1 ..." is
a
single line. It doesn't need and can't have the "End If" line.

And it's probably not a good idea to leave the sub with .screenupdating
turned
off. Excel/VBA is pretty forgiving, but if I turned something off, I'll
want to
turn it on.

But it doesn't look like there's anything that needs to be hidden. I'd
remove
those .screenupdating lines.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RF_Table As Range
Dim RF As Range

Set RF_Table = Worksheets("Sheet3").Range("H2:J5").Cells
Set RF = Me.Range("P4:P2000")

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(RF, .Cells) Is Nothing Then
If Not IsEmpty(Target) Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Application.VLookup(Target, RF_Table, 2,
False)
End With
Application.EnableEvents = True
End If
End If
End With

End Sub

Ayo wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RF_Table As Range, RF As Range

Set RF_Table = Worksheets("Sheet3").Range("H2:J5").Cells
Set RF = Range("P4:P2000")
Application.ScreenUpdating = False
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(RF, .Cells) Is Nothing Then
If Not IsEmpty(Target) Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value =
Application.WorksheetFunction.VLookup(Target, RF_Table, 2, False)
End With
Application.EnableEvents = True
End If
End If
End If
End With
End Sub

--

Dave Peterson



.

Quantcast