Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Macro to find an adjacent cell (Excell 2002)

    Huge picture that caused horizontal scrolling reduced in size - Mod

    Hi

    I think I need a Macro to highlight some cells in a column and then then select a non adjacent cell and place it into another cell.

    Any help would be gratefully appreciated. Please see screen shot.

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to find an adjacent cell (Excell 2002)

    Will there always be an exact match, or do you want to stop as soon as the cumulative sum is at least as large as the value in C2?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Macro to find an adjacent cell (Excell 2002)

    Hi Hans

    I does not need to be an exact match I do want to stop when the sum is at least as large a the value in C2

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to find an adjacent cell (Excell 2002)

    Click on the column letter D.
    Select Insert | Column... (don't worry, we will hide this column later on.)
    In cell D7, enter the formula =C7+0.0000000001
    In cell D8, enter the formula =C8+D7
    Fill cell D8 down as far as needed.
    This creates a cumulative sum in D710 (or as far down as needed.)
    In cell D3, enter the formula =MATCH(C2,D710,1) where D710 is the range you filled in the previous step.
    This formula returns the index of the row we need.
    In cell E3, enter the formula =OFFSET(D7,D3,2)
    This formula looks up the unit price.
    Click on the column letter D.
    Select Format | Column | Hide.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to find an adjacent cell (Excell 2002)

    Hans didn't answer about highlighting the cells. You can do this with conditional formatting:
    Select QS7 to QS whatever
    Format - cond format
    formula is:
    =SUM($QS$7:QS7)<=$C$2
    <format>
    patterns tab (select color)
    <ok><ok>

    If the sum is <= the value in C2 they will be colored.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Macro to find an adjacent cell (Excell 2002)

    Hi Steve / Hans

    I am deeply grateful to you both for your invaluable help.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •