change function add and remove rows

[MoKaA]

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