Excel changing part of the formula w/o logical cause

Michael AQ · Apr 17th 2016

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.

skywriter · Apr 17th 2016

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

Sub Test()
End Sub

Michael AQ · Apr 17th 2016

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

Michael AQ · Apr 17th 2016

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

skywriter · Apr 17th 2016

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

Please add the code tags.

I asked you to use code tags and gave you two ways to do that but you seemed to ignore the request.

Michael AQ · Apr 17th 2016

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

Code

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

Display More

Michael AQ · Apr 17th 2016

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

Code

[INDENT] 					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 				
[/INDENT]

Display More

Excel changing part of the formula w/o logical cause

Participate now!

Share