+ Reply to Thread
Results 1 to 16 of 16

Combine identical routines into one

  1. #1
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Combine identical routines into one

    Below is 3 SubRoutines (there are 45 altogether) and would like to combine them into one

    Please Login or Register  to view this content.
    In my drop-down list I am calling each one separately and would like combine them into calling one routine and I will name it CopyPuzzle instead of 1,2,3 etc..up to 45. I know how to fix the drop-down but not sure about this combination of routines. I' m sure there would be some if's and else statements to fix, but I tried. The only thing different is the address of the puzzle in the SourceWs.Range. If you need to look at this in the puzzle, then go to the tread where you can post programs for review and you will find it under the module named 'CopyPuzzle'.

    Thanks

    Jerry

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Combine identical routines into one

    Jerry

    What's in the dropdown?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Re: Combine identical routines into one

    Please Login or Register  to view this content.
    There is an End Sub, but something in-between above and end

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    Macro code
    Please Login or Register  to view this content.
    Worksheet event code
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Re: Combine identical routines into one

    kvsrinivasamurthy, I just got a chance to try your suggestion and it works. However it only works for puzzle P1 as the Range("B5:J13") is the address of the P1 puzzle. Below is how I wrote your suggestion and tried to add puzzle 2 & 3, but I may have the procedure wrong. Remember I have 45 of these puzzles I need to add to this one routine. The Worksheet event seems to be working. Also the value of "AKI" is "P" + the puzzle #, but I am thinking about just numbering them from 1 to 45

    Please Login or Register  to view this content.
    I have included (commented out) puzzle 2 & 3 so need to know how to call them based on value of AK1 (I renamed the routine from CopyPuzzle to CopiedPuzzle as I might have to us it if can't get this to work

    Thanks
    Last edited by jevicha2; 06-12-2021 at 02:43 PM.

  6. #6
    Registered User
    Join Date
    03-10-2021
    Location
    Paryzevo
    MS-Off Ver
    2k3
    Posts
    60

    Re: Combine identical routines into one

    Perhaps:
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    Ref: Post#5
    You need not change anything in my code. Depending upon in AK1 value that 'Range' will run.
    If AK1 =P1 range is "B5:J13"
    If AK1 =P2 range is "L5:T13"
    If AK1 =P3 range is "V5:AD13"........ Up to 45
    At a time one range will be selected.
    This is how code wrks. What you actually want I don't know.

  8. #8
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Re: Combine identical routines into one

    kvsrinivasamurth Well, since I re-tried your solution it works like you said, but I forgot to give you the layout of the "Puzzles' tab. Your code looks for puzzles from left to right and 1,2, 3 work, but look at the picture of my puzzles. I have them 3 across and 15 down and in the picture you will see where it is looking for puzzle 4 (the dotted lines) and if I enter puzzle 5 it shows up to the right (1 column over from puzzle 4). So, can you fix you code for the layout I have. If not I can re-arrange them horizontally.
    Attached Images Attached Images
    Last edited by jevicha2; 06-13-2021 at 06:37 PM. Reason: Forgot file to attach

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    Pl upload sample file.
    You want all the tables run at a time or selected one table at a time.
    Last edited by kvsrinivasamurthy; 06-14-2021 at 12:05 AM.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    This code for running selected puzzle.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Re: Combine identical routines into one

    Kvsrinivasamurthy, That picked up puzzle 4 but will not pickup any after that. It behaves like my previous comment. I am not a excel expert and have no clue how Offset(s) work. Without sending the complete puzzle I will try to make a working copy for you to review, so just give me a few days to work on it. This is a Sudoku puzzle which I have converted the numbers to ABC's and I like playing it more than the numbers. Thanks

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    I have corrected the code. Verified working ok.
    Code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Re: Combine identical routines into one

    kvsrinivasaurth, Good morning from here, and this works perfectly. Thanks for you help. I am retired and getting old and this game really helps the brain. I also have the same game using only numbers and another one called Loco Sudoku so will update those. Hope the Covid19 is staying away from you and your family.

    Jerry

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    Thanks very much for your concern about my health in this situation. I am staying with my family happily and healthy. Taken two doses of vaccine hence feel secured. I also sudoku in news paper everyday. Excel is just hobby for me. Hope we chat once again in this forum with some problem.

    Thanks once again.
    KVS
    Last edited by kvsrinivasamurthy; 06-15-2021 at 09:54 AM.

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combine identical routines into one

    P1 was not working. code is modified.
    New Code:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 06-28-2021 at 09:55 AM.

  16. #16
    Forum Contributor
    Join Date
    10-17-2018
    Location
    Knoxville, TN
    MS-Off Ver
    Office 365
    Posts
    214

    Re: Combine identical routines into one

    kvsrinivasamurth, I was in the process of adding this code to two other versions when I found this. The current one is numeric or the regular Sudoku and I have converted it to letters (A,B,C) and the other one is what is call a Loco Sudoku or Jigsaw (there is no 3x3 grids). Since you play Sudoku, pm me with your email and I will send them to you see how mine works. Thanks for your help

+ 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. Combine identical lines to 1 with sum
    By Tegning in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2020, 05:45 AM
  2. [SOLVED] How to combine vba routines?
    By nmsmith in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-28-2015, 08:24 PM
  3. [SOLVED] Combine data with identical name
    By y0brah in forum Excel - New Users/Basics
    Replies: 14
    Last Post: 10-14-2014, 03:46 PM
  4. [SOLVED] Combine identical macros of different worksheets into one.
    By Sarangsood in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-17-2013, 06:26 AM
  5. Trying to combine two spreadsheets with one identical column header
    By Gypsum in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-07-2013, 08:10 AM
  6. combine identical cells and get count
    By cclough27 in forum Excel General
    Replies: 22
    Last Post: 06-11-2008, 04:01 AM
  7. How do I combine multiple whooksheets with identical columns
    By HF in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2005, 09:06 AM

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