PDA

View Full Version : [SOLVED] change function add and remove rows



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

Tommy
10-01-2015, 12:49 PM
Hi Moka,

I am not sure what you are dong and it is hard for anyone to understand when there is no data and a file to reference. Maybe post a workbook with the confidentiality removed.

Meanwhile, maybe something to look at is subtotals, also review hiding the rows you do not want added. The subtotal should automajically update depending on the "weeks" you want removed/hidden.

No matter how much I love VBA sometimes a formula is better and faster. :(

MokA
10-01-2015, 10:52 PM
Hi Tommy thanks for your reply.

Ok i think i spend to much time with it and was to fixed on myself to be able to explain it properbly, sry for that.

Yesterday Night i just figured another way to do what i want to do. With only one/two Problems.


Sub SelectTest()


Dim helfer As Range
Dim Tabelle As ListObject
Dim i As Integer
i = 3
j = 1
Set helfer = Selection
Set Tabelle = Range("SummeMA").ListObject



If Range(Tabelle).Cells(3, j).HasFormula = True And InStr(1, Range(Tabelle).Cells(3, j).Formula, "-" & Zelle) <> 0 Then


While i < 55

helfer.EntireRow.Cells(1, i).Select
Zelle = ActiveCell.Address(0, 0)

alteFormel = Range(Tabelle).Cells(3, j).Formula
neueFormel = Replace(alteFormel, "-" & Zelle, "")
Range(Tabelle).Cells(3, j).Formula = neueFormel

i = i + 1
j = j + 1

Wend


Else

While i < 55

helfer.EntireRow.Cells(1, i).Select
Zelle = ActiveCell.Address(0, 0)


alteFormel = Range(Tabelle).Cells(3, j).Formula
neueFormel = alteFormel + "-" & Zelle
Range(Tabelle).Cells(3, j).Formula = neueFormel

i = i + 1
j = j + 1
Wend

End If

End Sub

The Only 2 things that still bother me are,

1. That i'm Selecting the row before i am able to change the formula. But im Sure i'll find a way around that ( Im pretty new to VBA and Excel )

helfer.EntireRow.Cells(1, i).Select
Zelle = ActiveCell.Address(0, 0)
2. If i Delete the Row i substracted, i get an error in the SummeMA Table


And i will add my Excel Worksheet so you can see what i am doing.14488

Any advices on my Code would be great.

MokA
10-01-2015, 11:02 PM
Ok That was easer then expectet ^^

Zelle = helfer.EntireRow.Cells(1, i).Address(0, 0)
just works fine

Tommy
10-02-2015, 06:48 AM
I changed this:

Sub SelectTest()
Dim helfer As Range
Dim Tabelle As ListObject
Dim i As Integer
i = 3
j = 1
Set helfer = Selection
Set Tabelle = Range("SummeMA").ListObject

If Range(Tabelle).Cells(3, j).HasFormula = True And InStr(1, Range(Tabelle).Cells(3, j).Formula, "-" & Zelle) <> 0 Then

While i < 55

helfer.EntireRow.Cells(1, i).Select
Zelle = ActiveCell.Address(0, 0)

alteFormel = Range(Tabelle).Cells(3, j).Formula
neueFormel = Replace(alteFormel, "-" & Zelle, "")
Range(Tabelle).Cells(3, j).Formula = neueFormel

i = i + 1
j = j + 1

Wend

Else

While i < 55

helfer.EntireRow.Cells(1, i).Select
Zelle = ActiveCell.Address(0, 0)

alteFormel = Range(Tabelle).Cells(3, j).Formula
neueFormel = alteFormel + "-" & Zelle
Range(Tabelle).Cells(3, j).Formula = neueFormel

i = i + 1
j = j + 1
Wend

End If
End Sub

To this

Sub SelectTest()



Dim helfer As Range
Dim Tabelle As ListObject
Dim i As Integer
i = 3
j = 1
Set helfer = Selection
Set Tabelle = Range("SummeMA").ListObject

If Range(Tabelle).Cells(3, j).HasFormula = True And InStr(1, Range(Tabelle).Cells(3, j).Formula, "-" & Zelle) <> 0 Then

While i < 55

Zelle = helfer.EntireRow.Cells(1, i).Address(0, 0)
Range(Tabelle).Cells(3, j).Formula = Replace(Range(Tabelle).Cells(3, j).Formula, "-" & Zelle, "")

i = i + 1
j = j + 1

Wend

Else

While i < 55

Zelle = helfer.EntireRow.Cells(1, i).Address(0, 0)
Range(Tabelle).Cells(3, j).Formula = Replace(Range(Tabelle).Cells(3, j).Formula, "-" & Zelle, "")

i = i + 1
j = j + 1
Wend

End If
End Sub