Search range, find value, copy then offset paste across 2 worksheets

  • My first post on here was answered so quickly with a very useful sub so i thought i'd see if anyone can help with another problem im having.


    I am trying to do the following:


    I am trying to write a sub that will
    look at 'Client' column "A" and then search 'Carer' column "A" for the same value. If it finds the same value i want it to copy this value in 'Carer' column "D" (for the row that the searched for value exist on) and paste this into Client column "C" (in the row that the searched for value exists in)


    I then need it to repeat this procedure for 'Client' cells 'A3', 'A4' etc until the end is reached.


    I found it difficult to explain exactly what i want to do here so if this is not clear to you please ask me to elaborate on any area of my query.

  • Re: Search range, find value, copy then offset paste across 2 worksheets


    I am finding it difficult to understand what you actually require?
    Could you post a small example or elaborate a little?

  • Re: Search range, find value, copy then offset paste across 2 worksheets


    Do you really need a macro for this?


    It sounds like something you could do with either VLOOKUP or INDEX/MATCH

  • Re: Search range, find value, copy then offset paste across 2 worksheets


    Ok i have attached some small screenshots that could possibly help explain what i mean.


    Basically id like the sub to:


    Search 'client tab' column A for any client ID that also exists in 'carer tab' column A. When it finds a matching value i want it to copy the value in 'carer' column "D" on the row where the matching value exists, to 'column C' in the client tab on the row of the searched for value.
    I want the sub to do this for all rows of Client tab A that contain data.

  • Re: Search range, find value, copy then offset paste across 2 worksheets


    I'd still go for VLOOKUP


    in C2 place


    =VLOOKUP(A2,Carer!$A$2:$D$1600,4,False)


    and copy down your range. You can then repaste the results as values if you do not wish them to remain linked.

  • Re: Search range, find value, copy then offset paste across 2 worksheets


    Thanks that has done the trick for me!!!!


    For some reason i keep on trying to do everything in VBA.


    :thanx:

  • Re: Search range, find value, copy then offset paste across 2 worksheets


    Quote from voutsy


    For some reason i keep on trying to do everything in VBA.
    :thanx:


    :) That's because it's addictive!! But we shouldn't lose sight of the fact that native Excel functions, where they can be applied, are usually faster.


    ;)

Participate now!

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