Live-Forum - Die aktuellen Beiträge
Datum
Titel
15.07.2024 16:00:57
15.07.2024 15:41:38
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Lange Formel in Zelle eintragen lassen

Lange Formel in Zelle eintragen lassen
21.03.2017 17:23:48
Denis
Hallo,
ich bin neu hier und habe durch dieses Forum schon einige meiner Probleme in VBA durch lesen beheben können. Dafür erstmal Danke!!!
Ich habe jetzt ein Problem, das ich nicht beheben kann...
Ich möchte aus Excel-"Leerblatt" die Excel-"Übersicht" öffnen (wenn nicht offen - wird geöffnet) und in dieser "Übersicht" in die nächste freie Spalte eine Formel in Abhängigkeit vom Namen des Tabellenblatt aus Leerblatt eintragen lassen. Das funktioniert soweit, bis die Formel eingetragen wird. Da bringt er Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler
Hier das Makro:
Sub Tabelleumbenennen()
ThisWorkbook.Sheets(1).Name = Replace(ThisWorkbook.Name, ".xlsm", "")
Dim Range As Range
Dim sPfad As String
Dim x As Integer
Dim z As Integer
y = ThisWorkbook.Sheets(1).Name
sPfad = "Z:\Förster\Förster Stücklisten\Förster Übersicht.xlsm"
ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
If DateiGeoeffnet(sPfad) = False Then
Workbooks.Open Filename:=sPfad
z = Workbooks("Förster Übersicht.xlsm").Sheets("Übersicht").UsedRange.Columns.Count
x = Workbooks("Förster Übersicht.xlsm").Sheets("Übersicht").UsedRange.Columns.Count + 1
Sheets("Übersicht").Select
Sheets("Übersicht").Range(Cells(2, z), Cells(3, z)).Select
Selection.Copy
Sheets("Übersicht").Select
Sheets("Übersicht").Range(Cells(2, x), Cells(3, x)).Select
ActiveSheet.Paste
Else
Workbooks("Förster Übersicht.xlsm").Activate
z = Workbooks("Förster Übersicht.xlsm").Sheets("Übersicht").UsedRange.Columns.Count
x = Workbooks("Förster Übersicht.xlsm").Sheets("Übersicht").UsedRange.Columns.Count + 1
Sheets("Übersicht").Select
Sheets("Übersicht").Range(Cells(2, z), Cells(3, z)).Select
Selection.Copy
Sheets("Übersicht").Select
Sheets("Übersicht").Range(Cells(2, x), Cells(3, x)).Select
ActiveSheet.Paste
End If
Cells(2, x) = "Abgerufen von " & y
Cells(3, x).FormulaLocal = "=WENN(ISTNV(SVERWEIS([@[Teilenr.]],'Z:\Förster\Förster Stücklisten\[" & y & ".xlsm]" & y & "'!$C$6:$T$200,17,0)),""" - """,SVERWEIS([@[Teilenr.]],'Z:\Förster\Förster Stücklisten\[" & y & "]" & y & "'!$C$6:$T$200,17,0))"
With ActiveWorkbook.VBProject
.VBComponents.Remove .VBComponents("Modul2")
End With
End Sub
Private Function DateiGeoeffnet(DerPfad As String) As Boolean
On Error Resume Next
Open DerPfad For Binary Access Read Lock Read As #1
Close #1
If Err.Number  0 Then
DateiGeoeffnet = True
Err.Clear
End If
End Function
Vielen Dank.

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Lange Formel in Zelle eintragen lassen
21.03.2017 17:44:18
Daniel
Hi
check mal deine Formel.
bei einer ungeraden Anzahl von Anführungszeichen (1 oder 3) müsste eigentlich auch ein Verkettungszeichen "&" stehen und das ist bei dir vor dem ersten SVerweis nicht der Fall.
Weiterhin fällt auf, dass du beim zweiten SVerweis beim Dateinamen die Dateierweiterung nicht hinzufügst.
für die bessere Übersicht würde ich die Formel erstmal als ganzes in eine Variable schreiben und an der Stelle, an der eine Variable eingefügt werden soll, einen Dummy-Text hinschreiben.
Dieser DummyText wird dann durch den Variablenwert ersetzt. (der Dummytext sollte eine Zeichenfolge sein, die ansonsten in der Formel nicht vorkommt, z.B. "yyy")
Vorteil ist, dass die Formel im Code nicht zerrissen wird und du Fehler im Formeltext schneller erkennst:
Dim Fo as string
Fo =  "=WENN(ISTNV(SVERWEIS([@[Teilenr.]],'Z:\Förster\Förster Stücklisten\[yyy.xlsm]yyy'!$C$6:$ _
T$200,17,0)),"" - "",SVERWEIS([@[Teilenr.]],'Z:\Förster\Förster Stücklisten\[yyy.xlsm]yyy'!$C$6:$T$200,17,0))"
FO = Replace(FO, "yyy", y)
cells(x, 3).FormulaLocal = Fo
noch ein kleiner Tip:
Schau dir mal die Funktion WENNFEHLER an (neu ab Excel 2007)
das klassische Konstrukt:
=Wenn(IstNV(SVerweis(...));"---";SVerweis(...)
hat den Nachteil, dass der SVerweis 2x berechnet werden muss (für die Prüfung und für die Wertermittlung)
mit WennFehler siehts dann so aus:
=WennFehler(SVerweis(...);"---")
das hat den Vorteil, dass die Formel nicht nur kürzer und übersichtlicher ist, sondern auch schneller in der Berechnung, weil der SVerweis nur 1x berechnet wird.
gruß Daniel
Anzeige
AW: Lange Formel in Zelle eintragen lassen
22.03.2017 09:03:34
Denis
Hallo Daniel,
Danke für den Tip, werde ich mir mal anschauen.
Habe den Fehler in meiner Formel gefunden... VBA kommt mit dem "[@[Teilenr.]]" nicht klar.
Sobald das durch A3 ersetzt wird, wird die Formel in die Zelle geschrieben und richtig berechnet.
Gruß Denis

342 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige