AW: Formel umbauen
04.03.2012 20:39:43
fcs
Hallo Angelika,
wenn man Formeln per VBA einfügt, dann sind 3 Punkte zu beachten:
1. Alle " in der Formel müssen durch "" ersetzt werden.
2. Soll die Formel in der lokalen Schreibweise in VBA verarbeitet werden?
Zellbereich.FormulaLocal = "=FormelText"
oder in der US (internationalen) Synax?
Zellbereich.FormulaR1C1= "=FormelText" oder Zellbereich.Formula= "=FormelText"
Ich persönlich bevorzuge die FormulaR1C1 schreibweise. Diese unabhäng ist von den Systemeinstellungen (Sprache, Trennzeichen etc) und beim Programmieren oft flexibler ist.
3. In der internationalen (USA) VBA-Syntax müssen:
- die entsprechenden englisch-sprachigen Funktionsbezeichnungen verwendet werden
- alle ";" der deutschen Syntax durch "," ersetzt werden.
- bei Zahlen in der Formel der Punkt als Dezimaltrennzeichen verwendet werden.
Für die korrekte VBA-Syntax ist es am einfachsten, die Formeleingabe per Makro aufzuzeichnen.
Noch ein Hinweis:
Damit nach dem Löschen von Zeilen die Formate nach dem Archivieren erhalten bleiben solltest du nur die Zellinhalte löschen. Dann muss du nicht mühsehlig die Formate per VBA wieder erstellen.
Gruß
Franz
Private Sub Archivieren()
' Aus Tabelle Daten1 die zu archivierenden Datensätze in Tabelle Archiv verschieben
Dim bereich As Range, Zeilen As Object, Zähler As Long
Dim Zelle As Range, i As Long, ziel As Range, Arr As Variant
Set Zeilen = CreateObject("Scripting.Dictionary")
Set bereich = Me.Range("Z40:Z" & Me.Range("Z" & Rows.Count).End(xlUp).Row)
For Each Zelle In bereich.Cells
If LCase(Zelle.Text) = LCase("X") Then
Set Zeilen(Zähler) = Zelle.Offset(0, 11 - Zelle.Column).Resize(1, 23)
Zähler = Zähler + 1
End If
Next Zelle
If Zähler = 0 Then Exit Sub
Zähler = Zähler - 1
Set ziel = Sheets("Archiv").Range("A65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
'Werte übertragen
For i = 0 To Zähler
Arr = Zeilen(i).Value
ziel.Resize(1, Zeilen(i).Cells.Count).Value = Arr
Set ziel = ziel.Offset(1, 0)
Next i
'Werte löschen
For i = Zähler To 0 Step -1
Zeilen(i).EntireRow.ClearContents 'angepasst -Formate bleiben _
erhalten!
Next i
Application.EnableEvents = True ' Ereignisbehandlung einschalten
Zeilen.RemoveAll
'sortiert Daten1 ab zeile 40
ActiveSheet.Range("K40:AF60000").Select
Selection.Sort Key1:=ActiveSheet.Range("k40"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'neu-Anfang
i = 0
Application.EnableEvents = False ' Ereignisbehandlung ausschalten
'Anzahl ausgefüllte Zeilen in Spalte K ermitteln
With ActiveSheet
Do Until .Range("K40").Offset(i, 0) = ""
i = i + 1
Loop
If i > 0 Then
'Formeln in Spalte Z einfügen
'Formel hier in der loalen Sprache/Syntax einfügen
.Range(.Range("z40"), .Range("z40").Offset(i - 1, 0)).FormulaLocal = _
"=WENN(UND(R40=""X"";S40="""");""X"";WENN(UND(R40=""X"";W40=""X"");""X"";" _
& "WENN(UND(R40=""X"";S40=""X"");"""";)))"
'oder so - in VBA-Sprache - international US-(englisch) in R1C1-Schreibweise
.Range(.Range("z40"), .Range("z40").Offset(i - 1, 0)).FormulaR1C1 = _
"=IF(AND(RC[-8]=""X"",RC[-7]=""""),""X"",IF(AND(RC[-8]=""X"",RC[-3]=""X"")," _
& """X"",IF(AND(RC[-8]=""X"",RC[-7]=""X""),"""",)))"
'Formeln in Spalte AC einfügen
'.Range(.Range("AC40"), .Range("AC40").Offset(i - 1, 0)).FormulaR1C1 = _
' "=IF(R[0]C[-1]=""X"",Focus1!R23C44, """")"
End If
End With
Application.EnableEvents = True ' Ereignisbehandlung einschalten
'neu-Ende
End Sub