Copy a Cell from above whenver a word appears in Colunm A

  • I would like a script (if someone could help me) in Excel VBA - wherever there is the word 'KEEPIT' in column A in column B it copies the number from above on to the same row - so in my example below we would see 3649 in Column B on the same row as KEEPIT - 4921 on the same row and 5501 - and so on ....


    [TABLE="width: 128"]

    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]A
    [/TD]
    [TD="class: xl65, width: 64, bgcolor: transparent"]B
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"][/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]3649
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl68, bgcolor: transparent"]KEEPIT
    [/TD]
    [TD="class: xl67, bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"][/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]4921
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl68, bgcolor: transparent"]KEEPIT
    [/TD]
    [TD="class: xl67, bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"][/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]5501
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"][/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]5501
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl68, bgcolor: transparent"]KEEPIT
    [/TD]
    [TD="class: xl67, bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"][/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]5575
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"][/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]5575
    [/TD]

    [/tr]


    [/TABLE]


    Marc

  • Re: Copy a Cell from above whenver a word appears in Colunm A


    If they are always blank in col B you can do this without a macro. Select column B > F5 > Special > Blanks and then enter =B1 in the formula box and press Ctrl+Enter.

  • Re: Copy a Cell from above whenver a word appears in Colunm A


    Thank you for responding - I have managed to tweak this information using =OFFSET(B2-1,0) instead of =B! and it works - thank you

Participate now!

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