MokA
10-01-2015, 01:11 AM
Hi,
i hope my english is good enough to explain my Problem.
I have a Worksheet with 6 Tables, 4 of them are dynamic. The 2 static tables have 52 columns ( for every week of the year 1 column ) and the 4 dynamic tables have 54 columns ( 52 for calender weeks and 2 for Name declarations )
In the last Table( static ) I add up all the Tables for Column C-BB without a Problem.
=SUM(geplantInhouse[[#All];[Column3]];geplantResident[[#All];[Column3]];ProjekteInhouse[[#All];[Column3]];ProjekteResident[[#All];[Column3]]
I quickly changed it from english to german hope i didnt make any mistakes there.
Now i want to be able to remove a Row from either .Table("geplantInhouse") or .Table("geplantResident") from the sum without deleting it in the Table.
I thought i might just subtract it again like this:
=SUMME(geplantInhouse[[#Alle];[Spalte3]];geplantResident[[#Alle];[Spalte3]];ProjekteInhouse[[#Alle];[Spalte3]];ProjekteResident[[#Alle];[Spalte3]];-INDEX(geplantInhouse[#Alle];1;3))
But if i want to add it again ( by removing the subtraction ) this Code doesnt work. Neither does my Range(KW(0)) work, nor does my Seach for "INDEX(geplantInhouse[#All],1,3)" work. If i search e.g. C33 it works.
Sub SummeBearbeiten()
Dim KW
Dim geplant
Dim alteFormel As String
Dim neueFormel As String
Dim i As Integer
KW = Array("INDEX(SummeMA[#Alle];3;1)", "INDEX(SummeMA[#Alle];3;2)", "INDEX(SummeMA[#Alle];3;3)", "INDEX(SummeMA[#Alle];3;4)", "INDEX(SummeMA[#Alle];3;5)", "INDEX(SummeMA[#Alle];3;6)", "INDEX(SummeMA[#Alle];3;2)", "INDEX(SummeMA[#Alle];3;3)", "INDEX(SummeMA[#Alle];3;4)", "INDEX(SummeMA[#Alle];3;5)", "INDEX(SummeMA[#Alle];3;6)", "INDEX(SummeMA[#Alle];3;7)", "INDEX(SummeMA[#Alle];3;8)", "INDEX(SummeMA[#Alle];3;9)", "INDEX(SummeMA[#Alle];3;10)", "INDEX(SummeMA[#Alle];3;11)", "INDEX(SummeMA[#Alle];3;12)", "INDEX(SummeMA[#Alle];3;13)", "INDEX(SummeMA[#Alle];3;14)", "INDEX(SummeMA[#Alle];3;15)", "INDEX(SummeMA[#Alle];3;16)", "INDEX(SummeMA[#Alle];3;17)", "INDEX(SummeMA[#Alle];3;18)", "INDEX(SummeMA[#Alle];3;19)", "INDEX(SummeMA[#Alle];3;20)", "INDEX(SummeMA[#Alle];3;21)", "INDEX(SummeMA[#Alle];3;22)", "INDEX(SummeMA[#Alle];3;23)", "INDEX(SummeMA[#Alle];3;24)", "INDEX(SummeMA[#Alle];3;25)", "INDEX(SummeMA[#Alle];3;26)", "INDEX(SummeMA[#Alle];3;27)", "INDEX(SummeMA[#Alle];3;27)", _
"INDEX(SummeMA[#Alle];3;28)", "INDEX(SummeMA[#Alle];3;29)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;36)", "INDEX(SummeMA[#Alle];3;29)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;36)", "INDEX(SummeMA[#Alle];3;37)", "INDEX(SummeMA[#Alle];3;38)", "INDEX(SummeMA[#Alle];3;39)", "INDEX(SummeMA[#Alle];3;40)", "INDEX(SummeMA[#Alle];3;41)", "INDEX(SummeMA[#Alle];3;42)", "INDEX(SummeMA[#Alle];3;43)", _
"INDEX(SummeMA[#Alle];3;44)", "INDEX(SummeMA[#Alle];3;45)", "INDEX(SummeMA[#Alle];3;46)", "INDEX(SummeMA[#Alle];3;47)", "INDEX(SummeMA[#Alle];3;48)", "INDEX(SummeMA[#Alle];3;49)", "INDEX(SummeMA[#Alle];3;50)", "INDEX(SummeMA[#Alle];3;51)", "INDEX(SummeMA[#Alle];3;52)")
geplant = Array(",-INDEX(geplantInhouse[#All],1,3)")
If Range(KW(0)).HasFormula = True And InStr(1, Range(KW(0)).Formula, geplant(0)) <> 0 Then
While i < 52
alteFormel = Range(KW(i)).Formula
neueFormel = Replace(alteFormel, "-INDEX(geplantInhouse[#Alle];1;3)", "")
Range(KW(i)).Formula = neueFormel
' i = i + 1
Wend
Else
While i < 52
alteFormel = Range(KW(i)).Formula
neueFormel = alteFormel + geplant(0)
Wend
End If
End Sub
And the Problem with my Code is that i would have to create way to many Arrays(didnt even finish the geplant() ) to make it work for Every Row in my 2 tables i want to be able to remove and add from the function.
Maybe you have another Idea to solve my Problem. Because i've been stuck on this issue for far to long now.
Greetings
Mo
i hope my english is good enough to explain my Problem.
I have a Worksheet with 6 Tables, 4 of them are dynamic. The 2 static tables have 52 columns ( for every week of the year 1 column ) and the 4 dynamic tables have 54 columns ( 52 for calender weeks and 2 for Name declarations )
In the last Table( static ) I add up all the Tables for Column C-BB without a Problem.
=SUM(geplantInhouse[[#All];[Column3]];geplantResident[[#All];[Column3]];ProjekteInhouse[[#All];[Column3]];ProjekteResident[[#All];[Column3]]
I quickly changed it from english to german hope i didnt make any mistakes there.
Now i want to be able to remove a Row from either .Table("geplantInhouse") or .Table("geplantResident") from the sum without deleting it in the Table.
I thought i might just subtract it again like this:
=SUMME(geplantInhouse[[#Alle];[Spalte3]];geplantResident[[#Alle];[Spalte3]];ProjekteInhouse[[#Alle];[Spalte3]];ProjekteResident[[#Alle];[Spalte3]];-INDEX(geplantInhouse[#Alle];1;3))
But if i want to add it again ( by removing the subtraction ) this Code doesnt work. Neither does my Range(KW(0)) work, nor does my Seach for "INDEX(geplantInhouse[#All],1,3)" work. If i search e.g. C33 it works.
Sub SummeBearbeiten()
Dim KW
Dim geplant
Dim alteFormel As String
Dim neueFormel As String
Dim i As Integer
KW = Array("INDEX(SummeMA[#Alle];3;1)", "INDEX(SummeMA[#Alle];3;2)", "INDEX(SummeMA[#Alle];3;3)", "INDEX(SummeMA[#Alle];3;4)", "INDEX(SummeMA[#Alle];3;5)", "INDEX(SummeMA[#Alle];3;6)", "INDEX(SummeMA[#Alle];3;2)", "INDEX(SummeMA[#Alle];3;3)", "INDEX(SummeMA[#Alle];3;4)", "INDEX(SummeMA[#Alle];3;5)", "INDEX(SummeMA[#Alle];3;6)", "INDEX(SummeMA[#Alle];3;7)", "INDEX(SummeMA[#Alle];3;8)", "INDEX(SummeMA[#Alle];3;9)", "INDEX(SummeMA[#Alle];3;10)", "INDEX(SummeMA[#Alle];3;11)", "INDEX(SummeMA[#Alle];3;12)", "INDEX(SummeMA[#Alle];3;13)", "INDEX(SummeMA[#Alle];3;14)", "INDEX(SummeMA[#Alle];3;15)", "INDEX(SummeMA[#Alle];3;16)", "INDEX(SummeMA[#Alle];3;17)", "INDEX(SummeMA[#Alle];3;18)", "INDEX(SummeMA[#Alle];3;19)", "INDEX(SummeMA[#Alle];3;20)", "INDEX(SummeMA[#Alle];3;21)", "INDEX(SummeMA[#Alle];3;22)", "INDEX(SummeMA[#Alle];3;23)", "INDEX(SummeMA[#Alle];3;24)", "INDEX(SummeMA[#Alle];3;25)", "INDEX(SummeMA[#Alle];3;26)", "INDEX(SummeMA[#Alle];3;27)", "INDEX(SummeMA[#Alle];3;27)", _
"INDEX(SummeMA[#Alle];3;28)", "INDEX(SummeMA[#Alle];3;29)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;36)", "INDEX(SummeMA[#Alle];3;29)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;30)", "INDEX(SummeMA[#Alle];3;31)", "INDEX(SummeMA[#Alle];3;32)", "INDEX(SummeMA[#Alle];3;33)", "INDEX(SummeMA[#Alle];3;34)", "INDEX(SummeMA[#Alle];3;35)", "INDEX(SummeMA[#Alle];3;36)", "INDEX(SummeMA[#Alle];3;37)", "INDEX(SummeMA[#Alle];3;38)", "INDEX(SummeMA[#Alle];3;39)", "INDEX(SummeMA[#Alle];3;40)", "INDEX(SummeMA[#Alle];3;41)", "INDEX(SummeMA[#Alle];3;42)", "INDEX(SummeMA[#Alle];3;43)", _
"INDEX(SummeMA[#Alle];3;44)", "INDEX(SummeMA[#Alle];3;45)", "INDEX(SummeMA[#Alle];3;46)", "INDEX(SummeMA[#Alle];3;47)", "INDEX(SummeMA[#Alle];3;48)", "INDEX(SummeMA[#Alle];3;49)", "INDEX(SummeMA[#Alle];3;50)", "INDEX(SummeMA[#Alle];3;51)", "INDEX(SummeMA[#Alle];3;52)")
geplant = Array(",-INDEX(geplantInhouse[#All],1,3)")
If Range(KW(0)).HasFormula = True And InStr(1, Range(KW(0)).Formula, geplant(0)) <> 0 Then
While i < 52
alteFormel = Range(KW(i)).Formula
neueFormel = Replace(alteFormel, "-INDEX(geplantInhouse[#Alle];1;3)", "")
Range(KW(i)).Formula = neueFormel
' i = i + 1
Wend
Else
While i < 52
alteFormel = Range(KW(i)).Formula
neueFormel = alteFormel + geplant(0)
Wend
End If
End Sub
And the Problem with my Code is that i would have to create way to many Arrays(didnt even finish the geplant() ) to make it work for Every Row in my 2 tables i want to be able to remove and add from the function.
Maybe you have another Idea to solve my Problem. Because i've been stuck on this issue for far to long now.
Greetings
Mo