Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Recalculation (97/2000 Latest SP)

    Hi Excel Gurus,

    Being an Access-man, I'm trying to write a simple function in Excel.
    In the attached file, I write a function to shift data a number of cells to the left or to the right. Use positive or negative numbers in C4.
    The function works well each time I change C4.
    When C4 is 0, I can change any column in row 2 and row 10 is updated (or recalculate).
    With any number other than 0 in C4, when I change something in row 2, row 10 is not updated.
    Any suggestion ?
    TIA
    Francois

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Hello Francois,

    As an example, enter 3 in cell C4. Let's look at cell K10. Its formula is <code>=moverow(Sheet1!K2,Sheet1!$C$4)</code>. As you see, the formula refers to cells K2 and C4. The function makes K10 display the value of H2. But changing the value of H2 doesn't trigger a recalculation of K10 since it does not refer to H2 - the formula evaluator cannot look inside the VBA code to see if H2 is referred to indirectly.

    One solution is to insert a line <code>Application.Volatile</code> at the beginning of the MoveRow function. This forces the function to be recalculated whenever the worksheet changes, not only if the cells referred to change.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Hi Hans,

    Fast answer and work like a charme.
    Still a lot of stuff to learn in Excel.
    This function has to be integrated in a big application (not my work) and I hope that this Application.Volatile function will not slow down the input of the data.
    I'll give it a try. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Francois

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Hans,

    2 problems when I insert the Moverow function in the main application:
    - The help file mention that Application.volatile only works when the changes are make on the same sheet. I have to refer to data on another sheet. Could that be the reason why I receive #Value! or should the second point be the problem ?
    - When I insert application.volatile in the Moverow function, another function stop after opening a second workbook. Removing Application.volatile in the Moverow function and the other function run without problem.
    Hope I'm not asking to much.
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Francois,

    Would a formula using only built-in functions be OK? You could use

    <code>=INDEX(Sheet2!$A$2:$L$2,MOD(COLUMN(A10)-$C$4-1,12)+1)</code>

    in cell A10, and fill right to L10. See attached workbook.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Of course a formula is ok. I was trying a user function because I don't know formulas very well.
    I'll try it tomorrow as I have to spent my day at a customer site.
    Thanks man.
    Francois

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Some comments, in general:
    Formulas are easier to use
    They run much faster than user-defined functions
    They will not generate the "macro-warning"
    Do not require any coding.

    Steve

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Hans,

    Your formula is exactly what I need.
    I take me some time to realize that -1 after $C$4 the number of first column that has to shift.
    Manny thanks.
    Francois

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Recalculation (97/2000 Latest SP)

    Steve,

    Thanks for the advice. I already read that in J. Walkenbach book Excel 2002 Power Programming With VBA, but like I say in my previous post, still a lot to learn about Excel.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •