|
Hallo Angelika,
der Nährwert der "Formel(n)" bleibt mir verborgen - aber wenigstens mault der VBA Editor/Interpreter net!
Gruß
Option Explicit
Sub test()
Dim LetzteZeile As Long
Dim str As String, fstr As String
str = "=WENN(A2=" & Chr(34) & "Bezirk" & Chr(34) & ";" & "XYZ" & ";" & String(4, Chr(34)) & ")"
LetzteZeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'
Range("E2").FormulaLocal = Replace(str, "XYZ", "F2+G2")
Range("E2").AutoFill Destination:=Range("E2:E" & LetzteZeile)
Range("F2").FormulaLocal = Replace(str, "XYZ", "KÜRZEN(J2/(C2*K2))")
Range("F2").AutoFill Destination:=Range("F2:F" & LetzteZeile)
Range("G2").FormulaLocal = Replace(str, "XYZ", "WENN(H2<=(C2*K2)/4;0;1)")
Range("G2").AutoFill Destination:=Range("G2:G" & LetzteZeile)
Range("H2").FormulaLocal = Replace(str, "XYZ", "J2-(C2*K2)*F2")
Range("H2").AutoFill Destination:=Range("H2:H" & LetzteZeile)
Range("I2").FormulaLocal = Replace(str, "XYZ", "L2*J2")
Range("I2").AutoFill Destination:=Range("I2:I" & LetzteZeile)
Range("D2").FormulaLocal = Replace(str, "XYZ", "D2*G2")
Range("D2").AutoFill Destination:=Range("D2:D" & LetzteZeile)
'
'Test
Dim x As Long, y As Long
For x = 2 To LetzteZeile
For y = 4 To 9
Debug.Print Cells(x, y).FormulaLocal, Cells(x, y).Value, Cells(x, y).Text
Next y
Next x
End Sub
|