+ Reply to Thread
Results 1 to 21 of 21

Range in code too long and makes BUG

  1. #1
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Question Range in code too long and makes BUG

    Hi Forum.

    I had struggled with this problem for some time and hope to get some help.
    There is an explanation on the problem, in the attached workbook, but I'll paste the explanation here too.


    - Above is two Buttons, which change the blue diget in Week 1 - Rows 13 and 25, between Diget with comma and Hours and minutes.
    - The codes in the Buttons only work in Week 1, but there is 16 Weeks all in all.
    - Below in the Textbox is the Range for all 16 Weeks, split up in 4 Weeks at the time.

    The problem is - When I try to make the Range on each Button, to contain the Range for all 16 Weeks, it will make a BUG, because the Range is too long.

    Question - Is it possible to make a Multi.Range Code on each Button, which contain the Range for all 16 Weeks, as I did in the TimeStamp Code below the two Buttons Codes in Sheets("Forside") ?


    Thank you in advance

    Ib
    Attached Files Attached Files

  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: Range in code too long and makes BUG

    Are the ranges for each week separated by 25 rows?

    That's what appears to be the case and if that's right you might be able to use something like this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Hi Norie.

    I can't get your code to work, although it seems as the Rows are separated by 25 rows.

    The first Row in week 1 is Row 13 and the next is Row 25.
    The first Row in week 2 is Row 38 and the next is Row 50.
    The first Row in week 3 is Row 63 and the next is Row 75.
    And so on to week 16.

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

    Re: Range in code too long and makes BUG

    In the workbook it's only the first week range, Q13, T13:U13, V13, Y13, Z13, AB13:AD13, AB25:AD2, that have formulas in the cells.

    So for all the other weeks the expression in the If statement here will evaluate to False.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Hmmm - I can't get the code work.

    Is it possible for you to make the codes for both Buttons in the attached Test Book and resend it to me.

    I need to say I'm not very good in VBA coding.
    Mostly I find codes on the net and re-use the codes and gets some help in different Forums.
    All my skills is trying and learning by doing and trying to understand what's going on in the different codes.
    Last edited by gnaske; 02-19-2019 at 06:03 AM.

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

    Re: Range in code too long and makes BUG

    How is it not working for you?

    It works for me but only for the first week but that's because that's the only week that has formulas in the cells.

  7. #7
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Now I copy/paste week 1 to all the other weeks.

    In week 2 and all the weeks below, is it only working in the blue result Range Q38:S38 -Q63:S63 and so on to week 16.

    The two Buttons looks like this.
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    I need to go to an apointment with the doctor, but will be back in about 1 hour.
    I attached the new Test Book
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    I had just tried the codes in my real workbook and the codes stop at the same spot.

    - Week 1 - Rows 13 and 25 = Change between [h]:mm and Digit with comma.

    - Week 2 - Only the cell Q38 is working.
    - Week 3 - Only the cell Q63 is working.
    - Week 4 - Only the cell Q88 is working.
    - And so on down to week 16.

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

    Re: Range in code too long and makes BUG

    Do you have merged cells in the worksheet?

    If you do then I think that could be the problem.

  11. #11
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Yes, just as shown in the attached worksheet.

    - Merged i Week 1 - Q13:S13, V13:W13, Z13:AA13
    - And the same in the following Weeks.

    But my first code wouldn't work, if I defined the Merge Cells in the Range.
    It would only work if the Merged Cells was defined by the first Cell.
    And that's also why, the Range will be very long and make the BUG.
    Last edited by gnaske; 02-19-2019 at 12:00 PM.

  12. #12
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    I use =SUM(xx:yy) in all the result cells and they are all formatted as "[h]:mm".

    When using the Digit with comma Button it just put Multiply with *24 behind the =SUM(xx:yy) and formate the result cells as "0.00".
    But the code stops after the first Merged cell i every weeks after Week 1.

    The code works in the first Merged cell in week 2-3-4-5 and so on, but stops in the following result cells in every of the week, except in Week 1.
    I'm going to be crazy...

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

    Re: Range in code too long and makes BUG

    The problem is the merged cells - they are throwing the offset off for some reason.

    To show you what I mean try running this code and then opening the Immediate Window (CTRL+G).
    Please Login or Register  to view this content.
    You should see that only the first cell, e.g. Q13, Q28 etc., is correct for each week.

    I'll see if I can figure something out but it might be worth considering losing the merged cells.

  14. #14
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Hmmm - I can't see how I can loose the merge cells in my real workbook.
    The merged cells is a calculation of two or three columns.
    It will destroy the workbook and it can't be changed - It's about 24 MB big and all codes.

    This attached show how 1 weeks looks in my real workbook - I use my own Ribbon.
    If you zoom in the browser, you can zoom in on the picture to see how it looks more clearly.

    Workbook.jpg
    Last edited by gnaske; 02-19-2019 at 01:04 PM.

  15. #15
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Range in code too long and makes BUG

    I may totally misunderstand the purpose of your project.
    However I must ask the question, why are summing times of the day ?
    torachan

  16. #16
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Torachan.
    My real workbook should be used to calculate working hours per week and up to 16 weeks, depending on which shift the employed is working at.
    It also calculate if there is working hours in the evening, in the night, on sundays etc. etc.
    And a lot of other things - I believe it's called "hourly allowance" in English, but not sure.

    All the calculation is in hh:mm.
    But on the pay check it's shown or calculated as digit with comma - That's how they do it in Denmark.

    F.ex if I work 8 hours 30 minuttes (8:30) it will be shown on the pay check as 8,50.
    That's why I need to be able to change between the showing of the result.

    Norie
    I believe I need to make the calculation of the merged cells, in hidden columns with single cells out to the right and then run the codes on these cells.
    And then just update the visible merged cells.
    The real workbook contain a lot of hidden columns out to the right anyway and that's one of the reasons it's 24 Mb all in all.

    I don't have the time to test it right now because it's a bit late in Denmark, but I'll give it a shot, when I got the time tomorrow.
    No matter how it goes, I'll write back with the answer here,

    Kind regards
    Ib
    Last edited by gnaske; 02-19-2019 at 03:46 PM.

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

    Re: Range in code too long and makes BUG

    gnaske

    I think I might be able to come up with something that will work with the sheet as it is, i.e. without the need for hidden cells.

    Mind you that's based on the sample workbook you uploaded so I wouldn't be able to tell if it would work OK with the real thing.

  18. #18
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Hi Norie.

    I'v now tried the hidden cells and of course I can change the value and formatting in the hidden cells.
    It also works in all 16 weeks, which mean it's because of the merged cells, the other code stop in week 2.
    But the formatting in the visible cells will be wrong, when changing between the two values.

    I'll be very happy if you can find a solution that works in the visible and merged cells.

    Thank you in advance.
    Ib
    Last edited by gnaske; 02-20-2019 at 05:55 AM.

  19. #19
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Hi again Norie.

    I guess I manage to make the code working, by making a Multi.Range.
    Test 3 attached.

    Anyway - Thank you for all your help.

    Kind regards
    Ib
    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: Range in code too long and makes BUG

    No problem.

    I was actually thinking of posting something that used Union but looks like you beat me to it.

  21. #21
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Range in code too long and makes BUG

    Hi Norie.

    It was actually a Union-code I was thinking of in the first place.
    I just called it a Multi.Range Code, but as I wrote, I'm not that good in VBA and don't know the different terms.

    In the first question in this thread I also referred to the TimeStamp Code below the two Button Codes in Sheets("Forside"), in which I actually managed to make a Union-code.
    I couldn't just make it for the two codes too

    Anyway - Thank you for all your help.
    It's really fantastic, people around the world, help with codes in Forums like this

    Kind regards
    Ib

+ 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. Replies: 9
    Last Post: 05-21-2018, 04:34 PM
  2. Mistery: Inputbox makes my code 1000 times slower
    By munuelitu in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 02-23-2018, 10:51 AM
  3. Countif Date given makes them within a certain age range
    By RAdams1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-27-2017, 03:41 PM
  4. VBA code to analyse data unless function code makes more sense than VBA,
    By vorabj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2015, 12:38 PM
  5. [SOLVED] Vba code makes workboos slow. Any alternate code ??
    By satputenandkumar0 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-23-2014, 04:30 AM
  6. BeforeSave code makes excel crash every time
    By keb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2011, 03:04 PM
  7. Break long range into 2 code lines
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-13-2011, 01: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