VBA_To find a cell value in range_Worksheet change event

  • Dear all,
    Somewhere i got messed up with below code. Below is something what i am trying to achieve.


    Whenever a user enters new value in any cell of column C:C, vba code must check for only that call value already exists in entire column C:C, if its found, then it must set corresponding column I:I cell value "Multiple". If not found, it must set to "Single".


    Challenges faced:
    1) Currently i am looping through entire C:C column which is not correct. I want value for cell in column I:I to be updated for new entries in columns C:C only.
    (I have values update 5000 + rows..So looping slows down the process)
    2) User may enter multiple cell values in one shot (Like, he copies 10 rows of data from some other database and pastes it directly into Excel sheet upto column H:H).


    How to i achieve it..? Please help..Attached is the sample work book and has got code it (Which basically doesn't work).



    Please note: This post is a cross post from below link.
    http://www.excelforum.com/exce…rksheet-change-event.html

  • Re: VBA_To find a cell value in range_Worksheet change event


    How about this. This just counts the number of entries. If the number is 1 then its a single entry else its multiple. The for loop handles if many cells are copied to column C at once


  • Re: VBA_To find a cell value in range_Worksheet change event


    Thanks a lot for your response...But i have one more small issue.
    Your Code targets column 3 only, but i have an situation where in user can paste data from column 1 to column 3 from other database..
    To put it in simple words, we have database where data is available till first 3 columns..Users copy them and paste it till first 3 columns..
    During this situation you code doesnt appear to update 'Single' and "multiple'.


    Please help me to fix this..
    many thanks in advance...

  • Re: VBA_To find a cell value in range_Worksheet change event


    try this one


  • Re: VBA_To find a cell value in range_Worksheet change event


    Great...Works flawlessly..Thanks a lot for your code and Logic...:)
    Thanks for your Tip as well..
    Have a great day sir.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!