+ Reply to Thread
Results 1 to 11 of 11

Sheetoffset - alternatives ?

  1. #1
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Sheetoffset - alternatives ?

    Hello,

    I have macro for creating new sheets, with copy method. After creating sheets I use Sheetoffset UDF function to paste formula which displays value from cells in previous sheet.

    This Sheetoffset function works, but looks like It's a hard-code operation, so my macro takes 10-12 seconds to finish. I'm trying to find a substitute for Sheetoffset function that would perform quicker. Without that function my macro finishes in around 3 seconds.


    Formulas are simple, example fo AJ5 with sheetoffset :
    =sheetoffset(-1;A5)
    Formulas need to be in range of AJ5:AJ318 with step 4, cells which need to be referred are in range A5:318 with same step.


    Anybody have any better ideas ?

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,042

    Re: Sheetoffset - alternatives ?

    Each time one formula is pasted, all the previous sheetoffset formulas recalculate. This can be expensive when a lot of formulas are involved.

    One method to speed things up is to set sheet calculations to manual, paste your formulas, then reset the sheet calculation back to automatic. This will prevent unnecessary recalculations.

    Please Login or Register  to view this content.

    Alternatively, if you only want the values in the cells and not the formulas, you could paste the values directly from your macro. That would be much faster and easier.
    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Sheetoffset - alternatives ?

    hi AlphaFrog,

    thanks for the response. I tried both your suggestions and I like them, but...

    First option works great as you said, the only "bad" thing is that I have 79 inflicting rows so the code doesn't look quite good when It's written.

    The second option, which is exactly what I was searching for, doesn't quite do the job. First of all, I copy 1st sheet, then make 12 new ones (named after months), and then these cell values must be filled from sheet No.3 till last sheet.... So, first problem is that I don't know how to assign that code to perform from sheet 3 to 13, within my macro. And second problem (which I think is bigger) is that It only copies values - If these referenced cells changes value on one sheet, values don't change in next sheet where values were copied....And unfortunately, that is not what I need

    So far I had entered formula on first sheet, copied It to all new sheets, and then delete It on second sheet.

    Looks like I'm gonna stick to your first option, but let me know If you have better solution that would look something like your second option. Here the code of my macro :

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,042

    Re: Sheetoffset - alternatives ?

    Quote Originally Posted by Lukael View Post
    the only "bad" thing is that I have 79 inflicting rows
    I don't understand what that means

    The second option, which is exactly what I was searching for,...then these cell values must be filled from sheet No.3 till last sheet.... So, first problem is that I don't know how to assign that code to perform from sheet 3 to 13, within my macro. And second problem (which I think is bigger) is that It only copies values.
    You can have the code copy links (without the sheetoffset function) instead of just values. Links would be much more efficient than using the sheetoffset function. I don't follow your data configuration; what you want linked.

  5. #5
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Sheetoffset - alternatives ?

    I don't understand what that means
    This means that my code for your 1st option (suggestion) looks like this, example for first few rows (79 rows in total) :

    Please Login or Register  to view this content.
    As you can see, this code is very long, If written for all rows..

    You can have the code copy links (without the sheetoffset function) instead of just values. Links would be much more efficient than using the sheetoffset function. I don't follow your data configuration; what you want linked.
    And I don't understand what this means
    Last edited by Lukael; 03-15-2014 at 12:49 PM.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,643

    Re: Sheetoffset - alternatives ?

    I see your rows are going up in increments of 4 - can't you just put that in a FOR loop with a STEP of 4?

    Hope this helps.

    Pete

  7. #7
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Sheetoffset - alternatives ?

    Quote Originally Posted by Pete_UK View Post
    I see your rows are going up in increments of 4 - can't you just put that in a FOR loop with a STEP of 4?

    Hope this helps.

    Pete
    Hi Pete,

    I am trying to do exactly that, but don't know how. I'm a VBA beginner...

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,042

    Re: Sheetoffset - alternatives ?

    Quote Originally Posted by Lukael View Post
    This means that my code for your 1st option (suggestion) looks like this, example for first few rows (79 rows in total) :


    And I don't understand what this means
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Sheetoffset - alternatives ?

    Code works, just a little bit explanation of the code please :

    Please Login or Register  to view this content.
    1. What exactly does .Adress(External:=True) do ?

    2. Why or will code never be buggy, since you used 2 FOR loops with same variable (k) ?

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,042

    Re: Sheetoffset - alternatives ?

    Quote Originally Posted by Lukael View Post
    Code works, just a little bit explanation of the code please :

    Please Login or Register  to view this content.
    1. What exactly does .Adress(External:=True) do ?

    2. Why or will code never be buggy, since you used 2 FOR loops with same variable (k) ?
    1.) Its returns the full text address (sheet name and cell address) of the range e.g.; January!A5 . Highlight the Address keyword in the code and hit F1 for help on that range property.

    2.) Never buggy. They are separate loops. The variable k can be reused.

  11. #11
    Forum Contributor
    Join Date
    02-09-2014
    Location
    Kamnik, Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    693

    Re: Sheetoffset - alternatives ?

    Thanks,

    never used F1 before in VBA. What a good thing to know

+ 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. Alternatives to EXCEL?
    By highlystrung in forum Excel General
    Replies: 3
    Last Post: 10-01-2012, 05:25 PM
  2. IF statements and their alternatives
    By jacobmcook in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-26-2007, 03:24 PM
  3. Sumproduct alternatives
    By kossdust in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-15-2007, 06:05 PM
  4. Alternatives to GET.CELL and VB?
    By whitehurst in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-24-2006, 03:55 PM
  5. What are the alternatives ???
    By christopherp in forum Excel General
    Replies: 4
    Last Post: 03-19-2006, 10:39 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