+ Reply to Thread
Results 1 to 15 of 15

macro to match multiple cells against a database and select multiple cells to copy/paste

  1. #1
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    macro to match multiple cells against a database and select multiple cells to copy/paste

    Hi,

    I have an issue that for the life of me I can't solve. I might find it hard to explain but I'll my best.

    I want to be able to create a macro which copies 2 cells (next to each other) and pastes those cells as values (in the same cells) over the formulas that created them as they are only needed once.

    These cells however are related to a name, therefore as the cells are pasted over and the formulas are discarded, I need the macro to be able to match a cell against the name in the data base, select those cells and paste them.

    Now it gets a little bit more complicated. There are 5 different cells which could have names that I want it to be able to match against.

    Is it even possible to match and select more than 1 cell? I also need help with having 5 different cells to match from.

    Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Muckybox; 02-24-2017 at 10:53 AM.

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    I suggest attaching a workbook with a mock up as suggested below.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    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.

  3. #3
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    Hi,

    Thanks for the reply.

    I've tried to explain exactly what I want to accomplish.

    The data is already all test data and this is a home project.

    Regards.

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    Where do you enter in the information for the person's first round? And which are the 5 cells you wish to match?

  5. #5
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20
    Quote Originally Posted by dflak View Post
    Where do you enter in the information for the person's first round? And which are the 5 cells you wish to match?
    Details (names, course and date) are entered on the add scorecard info sheet which then takes you to the actual scorecard where only scores will be entered. The scorecard would be used no matter which number card it would be and then a verification process tells the user which buttons to press to save the data until the process of gaining 3 cards is complete.

    The 5 cells i want to match are d3,j3,p3,v3 and ab3.

    The data itself already matches once entered but i need it to find that data and copy and paste. Maybe its a just a case of using isformula or is number on those columns rather than matching and selecting cells?

    Thanks.

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    I still don't understand how the first round data is collects. I put my name in the form that comes up when selecting the Add Scorecard Info. It added my name and then the code crashed.

    This looks like it crates a scorecard for the week indicated. How do I know how many games any particular player has played?

    Is it that I create a scorecard. The players play. You fill in the scores. Then the magic button is pressed and the program goes out to the player database and fills in the next available slot for that player until the first three slots are filled in. So it may be possible to have a game where a player has all three slots filled in because he's played 172 games. Anther player has two slots filled in and yet another has none filled in. Is that the concept that you want?

    I'm going to disable the part of the code that's crashing. I can see that it is intended to do some sort of error checking. I'll just avoid making errors.

  7. #7
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    I'm sorry, the mock-up may not work properly as I couldn't include all sheets and information and unsure exactly which parts are required for full usage.

    The idea is that a user can add new players to a database that keeps their information, calculates their initial handicap based on there first 3 rounds and then recalculates their handicap as they add more rounds as well as keeps their scorecards digitally rather than keeping old cards.

    The part I'm stuck with is keeping their initial scorecards for to calculate their initial handicap.

    The initial handicap needs to add new cards until all 3 cards are complete and then their initial handicap is calculated and will only be used for reference afterwards however when a new player is entered or say, player 1 has played there first round, and player 2 plays their third round as player 1 plays their 2nd round, whilst player 3 has already played all 3, it needs to be able to calculate constantly. Obviously, I am using a master sheet where all card information is entered then saved elsewhere ready for the next card to be entered so the formulas I have in the cells wouldn't keep the info calculated and would recalculate every time rather than adding 1st, 2nd and 3rd cards.

    At the moment, there is an initial screen that asks whether they wish to add a new scorecard or player, the card is filled in like you say, the buttons are pressed in sequence to calculate everything with the last button saving the card as a new sheet and resetting all data.

    The formulas actually match against the names already so all I need is to somehow copy and paste the formulas that have been calculated whilst leaving the others.

    Apologies for such a late reply but I must have missed the email where you replied.

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    The way I am reading the requirement is that a golfer has no handicap until he/she has played three rounds?

  9. #9
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    yes. I have given the player the ability to add their handicap if they already have one but this tool will calculate one for them. Only if I can find a way past this issue.

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    This application should really be handled by a database rather than Excel. However, Excel can be programmed to act like a database, but it would take a bit of overhauling the organization of the worksheets. If we can get the data "normalized" then we can use pivot tables behind the scenes to do some of the heavy lifting.

    I have some additional questions:

    Do you want to keep a historical data base of games by hole or will the round summary be sufficient? It doesn't matter much.

    On the course data sheet, Column B is the hole number and Column C appears to be Par. What does Column D represent? You are dealing with someone whose knowledge of golf is exceptionally limited.

    I am looking at the scorecard sheet. I am confident I can reverse engineer the formulas, but I may have questions.

    I think it's neat how you overlaid the input boxes on the Add Scorecard Info over the picture.

    Finally, I make no promises. This will take some time to organize (or organise - at least spelling is the only concern. No regional settings to contend with) and I will work on it as I can, so it may be a while.

  11. #11
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    ah, I only have a very limited idea of how to use database, not enough to be able to something as complex and I also don't believe I even have the program at the moment.

    Just to give you a bit of background, I am redoing an earlier workbook that I used for me and my friends only. That being the case, the workbook is essentially complete but I feel like I'm slightly addicted and thought to myself that I could make it better my making it so that any players could be added (as I only had 5 names on my sheet and if I had someone else play, I couldn't keep their info, same being with if we all didn't play, the sheets were just messy). so to answer your first question, by the time I've completed one task I want it to do something else. In the end I would like it to be able to track players progression by using their scorecards and I know this would be easier if they had their own sheets. I have already started working on my own sheet to try and accomplish this.

    I forgot about the hidden sheets to be honest. My mock up was only meant to suggest what I wanted to accomplish for this one task. To answer your second question, column D represents the stroke index which is a rating system of how difficult each hole is deemed to be bye the club. The reason it is needed is to work out the players score when they have a handicap and are playing stableford rules where players gain points per hole, with the aim being to level the playing field between players with better handicaps and those with poorer ones. These scores are represented by the columns after where the players enter their scores on the scorecard sheet.

    This all sounds good and I thank you but wouldn't this all be easier if you had the entire workbook?
    That being said, this is very much a working progress and as I said earlier, I am working backwards from a book I already did so some of it is redundant and some of it is required for future manipulation.

    Thanks

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    I think I can take a shot at this "from the ground up."

    You'll have a form that will allow you to enter new players and this information will "feed" one table.

    You will have the form you have now to set up a round giving the course name and selecting a variable number of players up to 5. this will generate a sheet (or maybe even another form) so you can enter the score by holes. You are correct, it is probably a good idea to have a separate sheet for each player. So after the data entry, the scorecards will get parceled out to the player's sheet and this temporary sheet will go away.

    I will probably have another table that will get maintained automatically after the scorecard entry that will keep track of the golfer's summary information to know how many games were played and what the score so we can calculate handicaps

    I could probably reverse engineer the formulas you use, but it would help a lot if you could explain them to me and what needs to be kept and what could get discarded after the calculations are made.

    I will make several assumptions: each course has 18 holes and no player will play more than one round on a given day. If this isn't correct, let me know.

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

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    I have a very simple question that I hope has an equally simple answer: how do you calculate a handicap based on an average?

  14. #14
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    ah right, go ahead then.

    My initial sort of idea was to have a 'start up' page that will navigate the user but I feel I got carried away with things and just thought i'd go back to it once I've solved the next problem.
    This is so that they can add a new course, new player or new card. Although I've started on new player and scorecard I haven't got round to creating anything for that part.

    By 'go away' do you mean delete or just hide because I would like the cards to be kept as records.

    By all means ask any questions you wish, I'm unsure what is actually included in the mock up now so if there's anything missing it might be confusing, that's all. I'm unsure whether the continuous handicap calculation sheet is on there and there are a lot of formulas on that page that may need explaining.

    Your assumptions would be correct.

  15. #15
    Registered User
    Join Date
    02-22-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: macro to match multiple cells against a database and select multiple cells to copy/pas

    This is what I meant. it's actually all quite complicated.

    I'll try to explain it as best and simply as I can.

    For each hole only scores of par+2 can be counted so each one gets an adjusted score. This can be seen in the hidden columns between each players name on the scorecard. Once you have the total adjusted score you find out the adjusted gross score by taking this figure and deducting the SSS (standard scratch score - the standard score someone with a handicap of 0 or 'scratch' would play all 18 holes in deemed by the club, usually around one less than course par). it is then that when this is done 3 times, you add them together and divide by 3. if this is a number above 28.5 and you're male, your handicap would be 28.5 as that is the highest, females are 36 and it's also different for juniors but I'm unsure exactly about their highest handicaps. The playing handicap is just the rounded number but as 28.5 is the highest, that is rounded down where as all others are rounded normally.

    The points are used for ongoing calculations with is probably more complicated where if you have certain criteria, you go up 0.1 (only amount by what handicap can increase) and can go down up to 0.5 based on other criteria. May need explaining later.

    I believe I may have only included males in my formulas as was only originally intended for me and my friends rather than any new players I might play with.
    Last edited by Muckybox; 04-19-2017 at 06:52 AM. Reason: annoying spelling error :D

+ 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. Coding problem please help.
    By TKash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2016, 07:38 AM
  2. Check multi level username with password
    By Shunwen82 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-27-2015, 11:00 AM
  3. Coding Problem
    By skin.uk1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 11:38 AM
  4. [SOLVED] If, Then, Else Coding Problem
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-30-2006, 03:30 PM
  5. [SOLVED] problem with coding
    By Subs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-26-2005, 11:05 AM
  6. [SOLVED] Please help: Coding Problem
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-28-2005, 01:05 AM

Tags for this Thread

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