1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel MACRO - Copy Range depending on 2 different variables.

Discussion in 'Business Applications' started by bootoine, Apr 27, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. bootoine

    bootoine Thread Starter

    Joined:
    Apr 27, 2010
    Messages:
    2
    Hello

    I am currently working on an excel file where I want to collect some data from a sheet (sheet B) to another sheet (sheet A)

    Please see doc attached in my second message right below.


    example:
    1. Item AB3005 is Cat. 1 starting prod in Jan 2011
    2. Would like to go and get range of data in Sheet B that correspond to Cat 1.
    3. Then would to paste this range of data to Sheet 1.
    4. Range of data should be pasted so that data under N in Sheet 2 corresponds to data pasted under start date in sheet 1


    Sheet A
    Item Category Start Date May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10AB3005 Cat. 1 Aug-10AD3307 Cat. 2 Jul-10CD2520 Cat. 1 Sep-10

    Sheet B
    Categories -2 -1 N+1 +2 Cat. 1 B C C D D Cat. 2 B C C D E Cat. 3 A C C F F

    Results from Example:
    Item Category Start Date May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10AB3005 Cat. 1 Aug-10B C C D D AD3307 Cat. 2 Jul-10B C C D E CD2520 Cat. 1 Sep-10B C C D D

    I tried few times already and have been able to put hte right range from sheet B in front of the right category in sheet A. But I have not been able to adjust the range so that they are aligned with the start dates.

    Would you have any idea how to do that?

    Thank you for the support

    Bootoine
     
  2. bootoine

    bootoine Thread Starter

    Joined:
    Apr 27, 2010
    Messages:
    2
    I realised my tables are not very clear.
    Please find attached the excel document

    Thank you
     

    Attached Files:

  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I have taken a look at your example but honestly can't make anything of it.
    I must have misundestood your explanation, but what is the meaning of the numbers in the 'Categories of Activities' sheet?
    First the headers -2, -1, N, +1, +2, Wouldn't it be simpler to replace the N with 0
    Is that what -1 -2 or -0?
    Your solution sheet doesn't give me enough information to check your vba code and see if I can help.:confused:
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try it and see if you can work it out,
    There is a text box with a short explanation of how I did it.


    Forget this one see the next note!
     

    Attached Files:

  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I editted the code so it also tests if the range -2 -1 0 +1 +2 falls within the target
     

    Attached Files:

  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919526

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice