About Us

Ayo

It works !!! Thanks.

"Dave Peterson" wrote:

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 wrote:

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.

"Rick Rothstein (MVP - VB)" wrote:

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

"Ayo" wrote in message
...
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("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
.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

"Bob Phillips" wrote:

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" wrote in message
...
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

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
What is wrong with this code?	jlclyde	Excel Discussion (Misc queries)	5	January 9th 08 05:12 PM
What is wrong with the code?	Eric	Excel Discussion (Misc queries)	2	September 13th 07 10:36 AM
Add chart - what is wrong with the code?	tskogstrom	Charts and Charting in Excel	3	October 24th 06 05:22 PM
Can someone tell me what is wrong with this code?	Ant	Excel Discussion (Misc queries)	8	November 14th 05 02:53 PM
What's wrong with my code ?	christophe meresse	Excel Worksheet Functions	3	August 2nd 05 05:09 PM

Menu

About Us