VBA Fehler im Code (Formeln durch Werte ersetzen)

Question

Hallo zusammen.

Ich hoffe Ihr könnt mir helfen :-)

Ich habe hier einen Ausschnitt meines Codes:

Private Sub CommandButton1_Click()

Range(Cells(3, 1), Cells.SpecialCells(xlCellTypeLastCell)) _
.Columns(21).Formula = "=vlookup(rc2,Basiswerte!R2C1:R723C22,rc16,false)"
.Columns(21).Formula = .Columns(21).Value

MsgBox ("Berechnungen abgeschlossen.")

End Sub

Wenn ich das Makro nun ausführe kommt es bei:

.Columns(21).Formula = .Columns(21).Value

jedoch zu folgendem Fehler:
"Unzulässiger oder nicht ausreichend definierter Bereich"

Was soll der Code tun?
Nun zunächst soll er in der ganzen Spalte eine Formel eintragen (sverweis). Im Anschluss daran soll soll die Formel mit dem Ergebnis (Wert) überschrieben werden.

Ich hoffe es kann mir jemand helfen.

Herzlichen Dank.

SG
Lisa

Answer 1

Hallo zusammen!

Hab den Fehler nun doch selbst gefunden...

Es darf nicht nur "Range" heißen, sondern "With Range" und am Ende dann "End With"

Byebye

Answer 2

Hallo Lisa,

du kannst die Werte auch ohne Umweg mit der Formel einfügen:

Sub formeln()

Dim zeile As Long

For zeile = 3 To ActiveSheet.Cells(Rows.Count, 21).End(xlUp).Row

Cells(zeile, 21) = Application.WorksheetFunction.VLookup(Cells(zeile, 2), Worksheets("Basiswerte").Range("A2:V723"), Cells(zeile, 16).Value, False)

Next zeile

MsgBox ("Berechnungen abgeschlossen.")
End Sub

Richtig müßte dein Code heißen:

Range(Cells(3, 1), Cells.SpecialCells(xlCellTypeLastCell)) _
.Columns(21).Formula = "=vlookup(rc2,Basiswerte!R2C1:R723C22,rc16,false)"

ActiveSheet.Columns(21) = ActiveSheet.Columns(21).Value

Gruß

M.O.