+ Reply to Thread
Results 1 to 15 of 15

Copying data in Cells with macro which adds the data to other parts of workbook $30

  1. #1
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Copying data in Cells with macro which adds the data to other parts of workbook $30

    Hi Everyone,

    I am working on a spreadsheet that is used to collect data and produce interesting charts and information for when you are playing a popular computer game called Rome 2 Total War. If you have never heard of it, it is a game a lot like RISK in which you raise Armies and try to take over the world.

    For the most part the spreadsheet is done. However, to make it as user friendly and inviting to many people who would normally shy away from doing something on excel, I think the easiest way to ensure that is to create a sort of "Master Page" at the start of the spreadsheet that is used to enter in your information after every battle and with the click of a button it adds that information for you to the appropriate cells within the workbook.

    I had a similar project about a year ago. I wish I had picked my helpers brain a bit more, but he at least taught me to give precise instructions so please excuse the wall of text and reference images.

    I am sure this will probably amount to 30-60 minutes of work, so I am willing to compensate $30 if you so wish.


    ________________________________________________


    Here is a snap shot of the main control sheet. It is from this sheet that the user will type in the details of his battles as he plays Rome 2 Total War so that he does not ever have to attempt to put it anywhere else nor should he. What this sheet needs to do is run a script which takes what the user just entered and puts them in their correct places within the workbook so all of the charts I have on the Dashboard sheet can get their information from.


    inputsheet.jpg



    This sheet (Main Control sheet) will need THREE Scripts made. The first (and longest) will be for the farthest left column where you input details about your battle. Everything entered in this Section (albeight one cell that gets changed on the Main Control sheet) gets added to ROW2 of the "Battle History" tab. *Note that as each entry occurs, the newest entry will remain in ROW2 while the old entries get moved down. Their cell reference on where they go are in the image below along with a example to show how I need it to look like for my charts:

    1st SCRIPT NEEDED

    battle_input_area.jpg

    And this is what it should look like with the example image above: (Ignore the cells where there is data like I2 and K2, those are just formulas for that column)

    example_output.jpg



    Lastly, for that first script, the last thing I need to happen when the user hits the "Enter Battle" button to submit his data, I also need the percentage data in F11 moved to J11 in the Main Control page. Unfortunately it cannot just be a simple formula like =F11 because if you notice the conditioning arrow, I want that to show the user if his Kill to Death Ratio improved or not since entering his last battle. So the concept for this remains the same as the image above, the only difference is that it changes a cell not in the "Battle History" sheet, but the same sheet it is already on. So, if you take my example on the first image of this post in which the players K/D Ratio was 44.88%, then say he enters another battle and his K/D Ratio% drops, it should look like this.

    percent.jpg



    2nd SCRIPT NEEDED


    The next Script that needs made is for the upper middle area for Adding Armies. This script needs to take the information provided after hitting the "Add Army" macro (Army Name, Region Created, and Year Created) and add that to H3 (Army Name) J3 (Region Created) and I3 (Region Created). However, unlike the battle history sheet in which the newer entries remained at the top, this script needs to list them in the next available row below.

    add_army.jpg



    3rd SCRIPT NEEDED

    The last script needed deals with the declaring war in the bottom middle. This script needs to control when a war is started and ended. The started part should be easy by now since it is the same thing you have done for everything else so far, however, the "End War" part may be a little difficult (or impossible) because it needs to find the faction first and then ensure that it still remains a blank cell in column D of whatever row it is on before it can enter in the date the war ended. In addition, keep in mind that there can will be times in which a player will be in a on again/off again war with the same faction two, three, or many more times.

    ****If you are fine with the $30 for the first 2 scripts, but are turning away from this request due to this last script, we can leave it out. But if you already know how you would perform this in your head already then it is appreciated!****


    In the image below, it includes an example and what the output would be had that example been used:

    (Note that I ran out of room for attachments, so please refer to the links)
    http://the3rdmarines.com/images/rome...e_example1.jpg


    http://the3rdmarines.com/images/rome...e_example2.jpg




    The references for where the data needs to go is in this image. The data is changed on the tab called "War Phases". Again, disregard other columns that are on there. Those are formulas. Also, newest entries need to be shown from newest to oldest / top down.

    http://the3rdmarines.com/images/rome/war_references.jpg



















    And that is it. Again, I am more than happy to pay $30 for the trouble. This worksheet will put a smile on a lot of peoples faces hopefully. Even though its finished, having someone help make these macros will make it very helpful to people who have never used excel before.





    Here is the File: Rome 2 Campaign Logger
    Last edited by switzd0d; 06-18-2015 at 12:33 AM.

  2. #2
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    I am surprised no one has replied yet interested. I usually see responses on here very quickly. Of course it has only been a day and I'm asking for a decent amount of work.

    Perhaps if you do not have time, you could point me to a easy to understand instruction/example of the type of macro I am trying to create. My problem is, I simply do not know the nomenclature and what to search for.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Looks like a pretty cool project, and I'd be happy to try and give you some time, but won't be able to do so until at least this evening, maybe even the weekend.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    That sounds great Arkadi. I'm getting into beginner tutorials for VBA so hopefully I can learn from your code how it is done. As with all things, its just knowing where to look/start that Im hung up on. Same was for charts, but if you looked at the file, I've gotten the hang of making them a bit. Excel is a wonderful program but at the same time frustrating because it feels like I only get 95% of what I wanted it to do but it is all wrong at the same time! Like when I use the RANK formula for the Top 5 charts, but it results in #DIV problems for duplicates. It can get addicting though because once that formula is perfected it is a good feeling.


    By the way, I'd love to have this ready by this weekend, but its no rush if you cannot get to it until later. It's just a few people following my thread on the games website are looking forward to it and even though it technically is ready to go as is, I would rather release it with this "Master Sheet" to encourage people afraid of walls of data to give it a shot as well.
    Last edited by switzd0d; 06-18-2015 at 04:11 PM.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    I noticed your notes mention a column K formula in battle history, but there are no formulas in column K?

    Also, is the 30% in your example for kill/death ratio supposed to be the change? or simply the kill/death ratio? We can just make F11 have the right icon depending on the situation, so you don't need the extra cell with an arrow?
    Last edited by Arkadi; 06-22-2015 at 10:59 AM.

  6. #6
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Hi,

    I noticed your notes mention a column K formula in battle history, but there are no formulas in column K?

    I do not know if you can add formulas the same way or not with the macro. Can it assign a formula to a cell? Like in our example we have 95% of the cells being changed to the text that was hard coded on the "Main Control Sheet". Could we also have it assign the formulas to columns I and K as well? This may reduce a lot of the size of the file I don't know, because currently I just run those same formulas in those columns down to like line 500 haha

    But yes, You are correct, I forgot to mention I could never figure out that formula to not show an error in any cell in column K. It needs to read the regions name off column J, and use/match the information in A:A in Theater of War Breakdown to B:B in Theater of War Breakdown. If this can be done, be sure to anchor "Theater of War Breakdown" parts of the formula so that when I add/remove more regions (to make one for other games int he Total War series').




    Now back to the Main Control Sheet thing where you input the stuff to add to the battle pages...

    30% in your example for kill/death ratio supposed to be the change? or simply the kill/death ratio?

    I'm looking at all my images to see where you got 30% from and can't find it. I am assuming you mean F and J 11. But yes it is supposed to change. I currently have F 11 as a simple =sum formula to get the kill to death ration %.


    We can just make F11 have the right icon depending on the situation, so you don't need the extra cell with an arrow?

    I knew there had to be a way to! I looked everywhere and I could not find any tutorials on it so I did it that way with the built in conditional formatting to read which value is higher and assign the arrow. If you can do this to show a down arrow (or something green) if the new value is less than the one it just replaced that is what I need. If the value is higher, it needs to show an upward arrow or red icon to show the user that his last battle was worst than his average.
    Last edited by switzd0d; 06-22-2015 at 01:05 PM.

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Sorry the 30% is in the sheet you attached, not the images, but yet you got what I meant.. So if old kill/death was 30% and after the new attack it goes up to 35% you want a red up arrow?

  8. #8
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Yes. Because that represents the player did worse in that battle he just entered than his average. A green arrow down means he did better. I could not find in the built in options for excel a "Green down" arrow and a "Red up arrow". The colors where reversed. So if all else fails, the green and red circle will do just as well.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Took a little while, but I have the first script done.... I think. I'm attaching, if you could check it out with some test data and let me know that'd be great, I checked for errors, but not with actual data. I can't actually upload the file as it is too large, but copy all this code into a module, and save that then try.

    enter_battle should be the macro assigned to the button.

    Please Login or Register  to view this content.
    Last edited by Arkadi; 06-22-2015 at 03:50 PM. Reason: On moving old data, had a mistake with column K formula... it was being pasted in column I.

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Here is the add army button's code:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Forgot to mention... This code takes care of the formulas in I and K as well

  12. #12
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Hi Arkadi,

    I was able to test out the code and am thrilled! It works flawlessly. I only have two "minor" tweaks that I did not think about until I just now entered in 10 battles I had.


    1st Tweak:
    Is there anyway to not have SOME the information placed in the INPUT SHEET cleared when you hit and run "Enter Battle"?

    The options I would like to remain after "ENTER BATTLE" is pressed (so it makes entering in multiple battles faster) are:

    C4 - Faction fought
    C9 - Region battle fought
    C12 - Year


    2nd Tweak: The arrow image for the K/D Ratio stays. Is there a way to delete the old image before pasting the new one when the macro runs?



    3rd Tweak:
    On the Battle History page, the formula used to sum up the total casualties is not following the correct row for some reason when you add in a few battles. No clue why, the code looks good =IF(SUM(G2:H2)=0,"",SUM(G2:H2))




    As for the request, you nailed it flawlessly. Exactly what I needed and think will make this more inviting to people afraid of walls of data.
    Last edited by switzd0d; 06-22-2015 at 10:16 PM.

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Lets try this then.... replace ALL previous code... this is all the code re-posted (including add army, although that should be the same...
    P.S. Sorry about the arrow bit... I knew I had to do it, then forgot to add the code for deletion.
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    06-14-2014
    Location
    Birmingham, USA
    MS-Off Ver
    Office 365 Version 2202
    Posts
    59

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Looks great Arkadi, works flawlessly now. Check your PM box and get back to me. Again, thanks so much for your help. +Rep!

  15. #15
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copying data in Cells with macro which adds the data to other parts of workbook $30

    Thanks for the rep switz0d
    As I mentioned in reply to message, unless you are planning to sell this somehow, I see no reason for you to pay me anything for what is a relatively small task, we take on more complex ones all the time. I appreciate the offer, but again, unless you make a profit, why would I? I will have a look at the 3rd script as well, just need to put aside a bit of time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copying Data from worksheets of workbook to another workbook with macro.
    By peejaygee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-18-2014, 03:11 PM
  2. Replies: 9
    Last Post: 09-10-2013, 03:49 AM
  3. [SOLVED] Copying data adds carriage return to data copied
    By extrapulp in forum Excel General
    Replies: 2
    Last Post: 10-09-2012, 01:08 PM
  4. Replies: 0
    Last Post: 12-22-2011, 03:42 PM
  5. Replies: 1
    Last Post: 04-01-2006, 03:50 PM

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