If the User selects a Product from the DropLists in E6 - E10, Code should find matching product in Col M and copy the Col N value to Col F
Else if the User changes a price in F6 - F10, Code should find matching product in Col M and copy the Col F value to Col N.
For some reason it isn't identifying the relevant Match in Col M.
Please Login or Register to view this content.
Hope someone can see where it's going awry?
All solutions, suggestions and alternatives welcome as ever
Ochimus
Please Login or Register to view this content.
Last edited by sintek; 05-22-2020 at 01:33 PM.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Try this …
The Dim statement does not need here to be at the top of the module for global variables,
must be in the event procedure …
ActiveSheet is useless as a worksheet event raises only on the active sheet !
Some logic errors or no sense after, the reason why the code can't work …
According to your attachment the revamped event procedure :
PHP Code:Private Sub Worksheet_Change(ByVal Target As Range)
Dim R
If Target.CountLarge > 1 Then Exit Sub
If Intersect([E6:F10], Target) Is Nothing Or IsEmpty(Target) Then Exit Sub
R = Application.Match(Target(1, -(Target.Column = 5)), [Product], 0): If IsError(R) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 5 Then Target(1, 2) = Cells(R + 1, 14).Value2 Else Cells(R + 1, 14) = Target.Value2
Application.EnableEvents = True
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 05-22-2020 at 07:29 PM. Reason: optimization according to the named range and IsError is back ‼
This change event (replaces ALL of the code in your current module) seems to work...
Please Login or Register to view this content.
Many thanks to each for the prompt response and solutions,
Can now mark this as Solved.
Ochimus
Re: Code not recognising Target cell
Hi Rick,
according to the initial post you forgot the event on column F …
Tell me Marc, why doesn't this forum have a "bang head" Smiley... it would be so appropriate for me to use right now instead of this one.I completely missed that part of the question. Here is my code approach revised to handle the whole question that the OP asked...
Please Login or Register to view this content.
Re: Code not recognising Target cell
Maybe you could use the existing named range for column M like I did when I amended my code.
What may happen with your last code if a cell in E6:F10 is cleared via the Del key for example ? …
Pleasure...Glad to have contributed...tx for rep +
My head is just somewhere else of late. We are moving next week and most of my time is involved with packing so my volunteer time is done during in-between times which doesn't leave a lot of time for coding and checking code. The OP has your answer and appears happy with it so I'll just leave it there for now.Originally Posted by Marc L
« Bon courage » for your packing and later your moving …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks