AW: Formel durch VBA ersetzen
19.12.2012 08:39:21
Klaus
Hallo Tom,
offtopic: versuche, auf .Select und .Selection zu verzichten sowie die "richtigen" Erweiterungen zu nutzen. Also statt deinem:
Sub start_uebernahme()
' start_uebernahme Makro
Sheets("Zusammenfassung").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "1"
End Sub
lieber
Sub start_uebernahme()
' start_uebernahme Makro
Sheets("Zusammenfassung").Range("D1").Value = "1"
End Sub
halb ontopic: Deine Formeln haben ein ()-Paar zuviel. Das macht zwar nichts, ist aber unschön und erschwert eventuell eine Fehlersuche.
statt (Beispiel D9)
=IFERROR(IF($D$1=1;(VLOOKUP($A9;Eingaben!$1:$1048576;3;FALSE));D9);D9)
lieber
=IFERROR(IF($D$1=1;VLOOKUP($A9;Eingaben!$1:$1048576;3;FALSE);D9);D9)
ontopic:
Als nichtwissender User machst du das so: den Rekorder an, eine Zelle mit der kopierbaren Formel selektieren (zb D9), du drückst F2 (Zelle aktiviern), ENTER (Zelle bestätigen) und machst den Rekorder wieder aus. Der aufgezeichnete Code sieht dann so aus:
Sub Macro2()
' Macro2 Macro
Range("D9").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
Range("D10").Select
End Sub
das ganze wird zunächst zusammengefahren, um die select loszuwerden. Dann siehts so aus:
Sub Macro2()
Range("D9").FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
End Sub
(alles unnötige rausgeworfen).
Im nächsten Schritt schaust du dir an, WO diese Formel überall hin soll. In deinem Beispiel ist das D9:D28, also das Makro angepasst:
Sub Macro2()
Range("D9:D28").FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
End Sub
dann kommt in deiner Tabelle eine Leerzeile, dann der Bereich D30:D49. Wieder angepasst:
Sub Macro2()
Range("D9:D28").FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
Range("D30:D49").FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
End Sub
an dieser Stelle überlegst du dir vielleicht einen schönerern Tabellenaufbau ohne Leerzeilen :-) Oder du greifst dir gleich das ganze Range ("D9:D28").
Wenn du keine Formeln willst, sondern Werte, überschreib die Inhalte der Zellen mit sich selbst
Sub Macro2()
Range("D9:D28").FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
Range("D30:D49").FormulaR1C1 = _
"=IFERROR(IF(R1C4=1,VLOOKUP(RC1,Eingaben!R1:R1048576,3,FALSE),RC),RC)"
Range("D9:D28").value = Range("D9:D28").value
Range("D30:D49").value = Range("D30:D49").value
End Sub
Ich könnte jetzt noch viel sagen, zB dass du die Formeln nach rechst kopierbar machen könntest (indem du den Spaltenindex des SVERWEIS in eine Zelle oberhalb auslagerst) und dass das tauschen von Formeln gegen VBA dein Zirkelproblem nicht lösen wird (der Formel ist egal, ob du sie schreibst oder VBA sie schreibt), aber spiel erstmal mit diesen Sachen rum :-)
Viel Erfolg!
Grüße,
Klaus M.vdT.