Excel changing part of the formula w/o logical cause

  • Hi, experts,
    I have one issue which I can't solve by myself.
    Recently, (I'm new in VBA, but advancing well) got this particular problem:
    while performing a 3D sum in workbook that contains sheets that are named by the week number, I used a macro that removes the sheets named "Start" and "End" to a proper position in the workbook, so I can use the formula: =SUM(Start:End!A1). The sheets DO go to preferred positions and that is not an issue. But for next range, when I change the week range using the macro, the above formula changes, and becomes like =SUM('Start:15'!A1), where 15 is the position of the previous position. Changing to other sheet, doesn't change the formula further, so it stays 15, no matter what.
    I don't know how the changing of the position of the tab affects the formula in a different cell. The macros are not in any touch with the cell that is performing the calculation.
    I made all this, because I understood that 3D sum is not possible using indirect function, so ended up with this solution. Looks fine, but when changed, performs wrong calculation.


    Thank you in advance.

  • Re: Excel changing part of the formula w/o logical cause


    You need to post the code.


    Either paste the code and then highlight it and click the # on the toolbar or press the # on the toolbar and paste the code between the code tags.


    After you save your post, the code will be in this format.

    Code
    1. Sub Test()
    2. End Sub

    Bruce :cool:

  • Re: Excel changing part of the formula w/o logical cause


    Sub MoveEND()
    '


    Dim n As Integer
    Dim x As Integer


    n = InputBox("Äî êî¼à íåäåëà ñå êàëêóëèðà?")
    x = n + 2
    Sheets("End").Select
    Sheets("End").Move Before:=Sheets("Start")
    Sheets("End").Select
    Sheets("End").Move After:=Sheets(x)
    Sheets("Saldo").Select
    Cells(4, 14).Value = n


    End Sub

  • Re: Excel changing part of the formula w/o logical cause


    Sub MoveSTART()




    Dim n As Integer
    Dim x As Integer


    n = InputBox("Îä êî¼à íåäåëà ñå êàëêóëèðà?")
    x = n + 1
    Sheets("Start").Select
    Sheets("Start").Move Before:=Sheets("1")
    Sheets("Start").Select
    Sheets("Start").Move Before:=Sheets(x)
    Sheets("Saldo").Select
    Cells(25, 14).Value = n

    End Sub

  • Re: Excel changing part of the formula w/o logical cause


  • Re: Excel changing part of the formula w/o logical cause