What is wrong with this code?

[Ayo]

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("P42000")
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

 

[PCLIVE]

It is usually best to tell us what is happening rather than just asking
what's wrong with code. You should give any errors, where/when it occurs,
etc.

I'm guessing that there may be an issue with "If .Count..."
I could be wrong, but Target.Count seems to produce an error.
There may be other problems, but look into that.

Regards,
Paul

 

[Ayo]

I have a range in Sheet3: Worksheets("Sheet3").Range("H2:J5").Cells and I
want to use it for a Vlookup on sheet1. So if I change the value in coulmn P,
Range("P42000"), I want the next cell to reflect the result of the Vlookup.

 

[Dave Peterson]

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("P42000")

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

It would probably help if you post the code you have now, after making those
changes, so we can see exactly what you are now working with.

Rick

 

[Bob Phillips]

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)

 

[Dave Peterson]

Just to add to Bob's question...

The subroutine's declaration would look like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

And instead of using an event macro, is there a reason not to use a formula:

=if(p4="","",vlookup(p4,sheet3!$H$2:$J$5,2,false))

 

[Ayo]

Thanks Bob. I think that was it.

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]

Thanks Bob. I think that was it, it was the Change Event.

 

[Ayo]

Soory Bob.
Now it's not working again:

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

Set RF_Table = Sheet3.Range("H2:J5").Cells
Set RF = Me.Range("P42000")

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

 

[Rick Rothstein \(MVP - VB\)]

It is usually a good idea to describe what you mean by "it's not working"
rather than make us guess (in other words, tell us what you thought was
going to happen and what you actually saw happen). With that said, and
acknowledging that I haven't looked closely at your code (as I am about to
go out for the evening), I do note that you split the location for your
EnableEvents statements. You are turning events off in the Then block of
your If-Then-Else code and turning events on in the Else block. At a
minimum, you should turn events off immediately before the If-Then statement
and turn them back on immediately after the End If statement.

Rick

Soory Bob.
Now it's not working again:

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

Set RF_Table = Sheet3.Range("H2:J5").Cells
Set RF = Me.Range("P42000")

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

 

[Ayo]

When I change the value in a cell in column P, column Q and R where supposed
to be populated using column P's value in a Vlookup function. It did work for
about 2 minutes and then all of a sudden, nothing is happening.

 

[Dave Peterson]

If you close excel and reopen the file, does the code start working?

If it does, my bet is that you were playing around with the code and stopped it
when .enableevents were turned off.

If you do it again (or you could try it now), you can:
open the VBE
hit ctrl-g
type this and hit enter:
application.enableevents = true

Then back to excel to test it out.

 

[Ayo]

It works !!! Thanks.

If you close excel and reopen the file, does the code start working?

If it does, my bet is that you were playing around with the code and stopped it
when .enableevents were turned off.

If you do it again (or you could try it now), you can:
open the VBE
hit ctrl-g
type this and hit enter:
application.enableevents = true

Then back to excel to test it out.