Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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("P4:P2000"), I want the next cell to reflect the result of the Vlookup. "PCLIVE" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 "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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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)) 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
Thanks Bob. I think that was it.
"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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
Thanks Bob. I think that was it, it was the Change Event.
"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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this code?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What is wrong with the code? | Excel Discussion (Misc queries) | |||
Add chart - what is wrong with the code? | Charts and Charting in Excel | |||
Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with my code ? | Excel Worksheet Functions |