+ Reply to Thread
Results 1 to 8 of 8

Macro which places values from a range in a cell and then copies results in a loop

  1. #1
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    365/2016
    Posts
    4

    Question Macro which places values from a range in a cell and then copies results in a loop

    Hi guys!

    I have a complex VBA task to perform, and I think I'm not experienced enough to solve it.
    So I hope that maybe one of you would be so kind to give me some advice.

    Workbook attached is a tool to analyse performance of a group.
    • Worksheet "database" contains all the results of individuals of different groups. It also containts stats of the groups (columns K:M) and lists participants of the groups.
    • Worksheet "filter" does what it says - it filters the database by a chosen ID ([CHOSEN_ID] in the file).
    • Worksheet "calculation" serves as a place to do the calculations on the filtered data. The results are shown in the range C17:I22, though the number or rows needed vary, hence [NR_ROWS] says how many rows do we need in a given project.
    • Worksheet "group_analysis" is supposed to gather all the results from the range C17:I22 from "calc", copied one by one for a chosen group [CHOSEN_GROUP].

    The macro that I have trouble with should enter into [CHOSEN_ID] (B2 in "filter") all the IDs listed in a column L in "database" for chosen group A in E2 in "filter" [CHOSEN_GROUP] or column M for the group B. The number of IDs in a group is also given in the column.
    Then, after performing each task (that is, after placing each ID in B2 in "filter"), it should copy the range C:I from "calculation" from 17th row till 17th + [NR_ROWS], and paste it into "group_analysis".
    This set of tasks should be in a loop, until the last ID in a group - the pasted results should be also pasted one after another (after [NR_ROWS] number of rows).

    Does anybody has any experience with creating such macros?
    If so, I'll gladly take any advice on how to do it!
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,660

    Re: Macro which places values from a range in a cell and then copies results in a loop

    Hi, I believe second part is much easier, so have left it for you (if it's a problem, let us know), but the first one could be done for instance this way (if you want to see it working make filter sheet active before calling macro):

    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    365/2016
    Posts
    4

    Re: Macro which places values from a range in a cell and then copies results in a loop

    Hi Kaper!

    Thanks for help!
    I have a couple of questions though.
    Especially about this fragment:
    Please Login or Register  to view this content.
    In the original file the ID column is "A", but the group column is "D".
    If i switch it into:
    Please Login or Register  to view this content.
    will it mess up it all?


    Then, in regard to this copying fragment, could you please see if the below makes sense to you?
    Please Login or Register  to view this content.
    Thank you

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,660

    Re: Macro which places values from a range in a cell and then copies results in a loop

    This is fine:
    Please Login or Register  to view this content.
    provided you change all calls to
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    as in original array you had 2 columns, and now there are 4 of them.

    the second part:
    it shall work (some minor changes may be needed).

    My suggestions for improvement are:
    Please Login or Register  to view this content.
    is not needed. (it is just how your actions are registered by macro recorder. Delete this line

    this:
    Please Login or Register  to view this content.
    I'd move to the end of the code in the loop, but before loop I'd add
    Please Login or Register  to view this content.
    or any other row number (may be 2, if above copied data you want some header line) you want first data to be copied
    this 2 lines
    Range("C17:I22").Select
    Selection.Copy
    I'd change into
    Please Login or Register  to view this content.
    this I'd move before the loop (there is a big chance it can be ommitted too).
    Please Login or Register  to view this content.
    and this 2 lines
    Please Login or Register  to view this content.
    could be replaced with:
    Please Login or Register  to view this content.
    I could not test it, because I'm writing it on a tablet without macro capabilities, but the above changes shall work :-)

    PS. As a good practice, I'd add wiersz variable definition to declarations:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: Macro which places values from a range in a cell and then copies results in a loop

    I think this might get the job don without VBA. What I did was make a pivot table that mirrors the data in its entirety. Then I applied slicers to it. I moved the slicers to the Analysis sheet. I "overlaid" the results of the pivot table with named dynamic ranges called rng_group, rng_id, rng_product, rng_X, rng_Y, etc.

    When you change the slicers, the pivot table changes and the formulas are built on the named dynamic ranges.

    Here is more information on dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  6. #6
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    365/2016
    Posts
    4

    Re: Macro which places values from a range in a cell and then copies results in a loop

    Hi Kaper,

    Once again, thank you for valuable feedback!
    I entered your improvements to the original file, hence the ranges and worksheet names will be different from the previous post.
    If that confuses you, please let me know and I'd change them quickly into what they were previously.

    The whole macro you can see below:

    Please Login or Register  to view this content.
    And below my comments/questions:
    Please Login or Register  to view this content.
    When working with formulas, COUNTA and COUNTBLANK don't really work when there is a formula in a cell which gives the result of "".
    Hence I changed the column into C (from A), where there's no formula in it.
    Please Login or Register  to view this content.
    You've mentioned that the line "Sheets("GRUPIK").Select" should be in the beginning of the loop. But why should it be there, if that's the destination of the copied range?

    Please Login or Register  to view this content.
    Ideally I'd like to copy the range E:DD with number of rows given in here: Worksheets("WYLICZENIA").Range("D323").Value, starting from 323rd row.
    How can I implement that change? How should I phrase it?

    Thank you for your time Kaper!

    Dflak,
    That seems to be an interesting concept as well.
    I might need it in other parts of the workbook.
    Thank you for a hint!

  7. #7
    Registered User
    Join Date
    08-10-2018
    Location
    Warsaw, Poland
    MS-Off Ver
    365/2016
    Posts
    4

    Re: Macro which places values from a range in a cell and then copies results in a loop

    Hi Kaper,

    Once again, thank you for valuable feedback!
    I entered your improvements to the original file, hence the ranges and worksheet names will be different from the previous post.
    If that confuses you, please let me know and I'd change them quickly into what they were previously.

    The whole macro you can see below:

    Please Login or Register  to view this content.
    And below my comments/questions:
    Please Login or Register  to view this content.
    When working with formulas, COUNTA and COUNTBLANK don't really work when there is a formula in a cell which gives the result of "".
    Hence I changed the column into C (from A), where there's no formula in it.
    Please Login or Register  to view this content.
    You've mentioned that the line "Sheets("GRUPIK").Select" should be in the beginning of the loop. But why should it be there, if that's the destination of the copied range?

    Please Login or Register  to view this content.
    Ideally I'd like to copy the range E:DD with number of rows given in here: Worksheets("WYLICZENIA").Range("D323").Value, starting from 323rd row.
    How can I implement that change? How should I phrase it?

    In its current shape the macro after running it says that there are no results for the group A.
    I know that hypothetically it's hard to find reason for that, without the actual file, but perhaps you can see some obvious mistake I made?

    Thank you for your time Kaper!

    Dflak,
    That seems to be an interesting concept as well.
    I might need it in other parts of the workbook.
    Thank you for a hint!

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,660

    Re: Macro which places values from a range in a cell and then copies results in a loop

    See
    Please Login or Register  to view this content.
    as for
    Please Login or Register  to view this content.
    you may try to delete it and use
    Please Login or Register  to view this content.
    But probably the best way to do the copying will be (as you do only clearcontents so the format is kept) will be not using copy at all but assigning values, so something like:

    Please Login or Register  to view this content.
    and of course definition of ile_wierszy variable, so
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1