Code not recognising Target cell

[Ochimus]

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.

Please Login or Register  to view this content.

For some reason it isn't identifying the relevant Match in Col M.

Hope someone can see where it's going awry?

All solutions, suggestions and alternatives welcome as ever

Ochimus

[Sintek]

Please Login or Register  to view this content.

[Marc L]

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 :

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 » ! ◄ ◄

[Rick Rothstein]

This change event (replaces ALL of the code in your current module) seems to work...

Please Login or Register  to view this content.

[Ochimus]

Many thanks to each for the prompt response and solutions,

Can now mark this as Solved.

Ochimus

[Marc L]

Hi Rick,

according to the initial post you forgot the event on column F …

[Rick Rothstein]

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.

[Marc L]

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 ? …

[Sintek]

Pleasure...Glad to have contributed...tx for rep +

[Rick Rothstein]

What may happen with your last code if a cell in E6:F10 is cleared via the Del key for example ? …

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.

[Marc L]

« Bon courage » for your packing and later your moving …