Anzeige
Archiv - Navigation
1824to1828
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA - Formula.Local

VBA - Formula.Local
19.04.2021 17:06:35
excelliebe
Hallo zusammen,
leider funktioniert mein Code nicht - Fehlercode: Argumenttyp ByRef unverträglich; markiert wird das erste Date1

Sub Formel()
Dim BestellmFormel As String
Dim Date1 As String
Dim Date4 As String
Dim letztZeile As Integer
letztZeile = Sheets("Nfr.").Cells(Rows.Count, "A").End(xlUp).Row
Date1 = Sheets("Nfr.").Cells(3, SpalteQTY.Column).Offset(, 1).Column
Date4 = Sheets("Nfr.").Cells(3, SpalteQTY.Column).Offset(, 4).Column
Dim SpalteQTY As Range
On Error Resume Next
Set SpalteQTY = Sheets("Nfr.").Rows(3).Find(what:="QTY", LookIn:=xlValues, lookat:=xlWhole)
On Error GoTo 0
BestellmFormel = "=INDEX(Nfr.!" & Buchstaben1(Date1) & "$4:" & Buchstaben4(Date4) & letztZeile & ";XVERGLEICH(Import!A2& _
Import!B2&Import!C2;Nfr.!$R$4:$R" & letztZeile & ");XVERGLEICH(Import!F2; Nfr.!" & Buchstaben1(Date1) & "$4:" & Buchstaben4(Date4) & letztZeile & "))"
Range("G2:G" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaLocal = BestellmFormel
End Sub
Public Function Buchstaben1(Date1 As Long) As String
Buchstaben = Split(Cells(3, Date1).Address, "$")(1)
End Function
Public Function Buchstaben4(Date4 As Long) As String
Buchstaben = Split(Cells(3, Date4).Address, "$")(1)
End Function
Jemand eine Idee für das Problem?
Viele Grüße und danke im Voraus :)

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA - Formula.Local
19.04.2021 17:11:38
MRUTOR
Hallo,
ohne weiter geschaut zu haben. Du dimensionierst und fuellst die Variable SpalteQTY weiter unten, benutzt sie aber schon weiter oben in der von dir beschriebenen Zeile, die markiert wird. Die 2 Bloecke solltest du umdrehen.
Gruss Tor
AW: VBA - Formula.Local
19.04.2021 17:23:09
Guest
Sorry, das hab ich beim Kopieren falsch gemacht.
In der Excel ist es schon richtig rum, d.h. das ist also nicht das Problem. Aber du hast natürlich Recht!
AW: VBA - Formula.Local
19.04.2021 17:25:35
MRUTOR
Hallo,
dann waere es einfacher, wenn du mal die Datei als Beispielkopie hochlaedst. Ist dann einfacher, den Fehler nachzuvollziehen.
Gruss Tor
AW: VBA - Formula.Local
19.04.2021 17:55:18
Daniel
Hi
bei der Parameterübergabe in der Form "byRef" (welche Standard ist, wenn nicht angegeben) müssen die Werte, die du der Funktion übergibst, genau den selben Datentyp haben wie in der Deklarationszeile der Funktion angegeben.
dh wenn du deklarierst: Public Function Buchstaben1(Date1 As Long) As String
dann muss, wenn du diese Funktion aufrufst, auch eine Zahl vom typ LONG der Übergabeparameter sein.
VBA macht hier keine automatische Typumwandlung, das geht nur mit dem Übergabetyp "byVal"
deklariere in der "Sub Formel" die Variablen Date1 und Date4 mit "as Long", dann sollte es funktionieren.
ansonsten arbeitets du etwas umständlich. Du arbeitest hier mit Zahlen, die haben keine Verknüpfung zu einem Tabellenblatt, daher kann man die neuen Spaltennummern einfacher berechnen
außerdem reicht dir hier eine Funktion.
Diese kannst du ja mehrfach mit unterschiedlichen Parametern verwenden, ohne dass man jedesmal die Funktion neu schreiben muss.
das ist ja sinn und zweck der Funktion.
Als Code sollte das reichen (und wahrscheinlich gehts auch noch kürzer)

Sub Formel()
Dim BestellmFormel As String
Dim Date1 As Long
Dim Date4 As Long
Dim letztZeile As Long
Dim SpalteQTY As Range
letztZeile = Sheets("Nfr.").Cells(Rows.Count, "A").End(xlUp).Row
Set SpalteQTY = Sheets("Nfr.").Rows(3).Find(what:="QTY", LookIn:=xlValues, lookat:=xlWhole)
Date1 = SpalteQTY.Column + 1
Date4 = SpalteQTY.Column + 4
BestellmFormel = "=INDEX(Nfr.!" & SpaltenBuchstabe(Date1) & "$4:" & SpaltenBuchstabe(Date4) & letztZeile _
& ";XVERGLEICH(Import!A2&Import!B2&Import!C2;Nfr.!$R$4:$R" & letztZeile _
& ");XVERGLEICH(Import!F2; Nfr.!" & SpaltenBuchstabe(Date1) & "$4:" & SpaltenBuchstabe(Date4) & letztZeile & "))"
Range("G2:G" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaLocal = BestellmFormel
End Sub


Public Function SpaltenBuchstabe(Spalte As Long) As String
Buchstaben = Split(Cells(3, Spalte).Address, "$")(1)
End Function
wenn du dich mal mit der Z1S1-Schreibweise für Zelladressen beschäftigst, dann kannst du das ganze noch weiter kürzen, weil du dann die Spaltennummern direkt in die Formel einsetzen kannst und nicht erst in Spaltenbuchstaben umwandeln musst.
Gruß Daniel
Anzeige
AW: VBA - Formula.Local
20.04.2021 11:29:56
Guest
Lieber Daniel,
erneut großen Dank für deine Hilfe, die ausführliche Erklärung und Lese-Tipps! Es läuft! Z1S1 werde ich mir auf jeden Fall ansehen!
Grad bin ich froh, dass es funktioniert :D
Viele Grüße
AW: VBA - Formula.Local
19.04.2021 18:01:06
onur
1)

Public Function Buchstaben1(Date1 As Long) As String
Buchstaben1 = Split(Cells(3, Date1).Address, "$")(1)
End Function
Public Function Buchstaben4(Date4 As Long) As String
Buchstaben4 = Split(Cells(3, Date4).Address, "$")(1)
End Function
2) .Column ist eine Zahl , aber Date1 und Date4 sind als String deklariert.
Anzeige
AW: VBA - Formula.Local
19.04.2021 19:24:24
Yal
... sogar mit der Ergänzung, dass diese beide Functionen genau dasselbe machen.
Eine reicht (die man mehrmals aufrufen darf):

Public Function Buchstaben(ByVal Col) As String
Buchstaben = Split(Cells(1, CStr(Col)).Address, "$")(1)
End Function

Eine Spezifizierung der Datentyp ist bei komplexe Sub/Function sinnvoll. Bei solchen Einzeiler darf man sich erlauben, sie zu sparen.
Notfalls:

Public Function Buchstaben(ByVal Col As Variant) As String
Buchstaben = Split(Cells(1, CStr(Col)).Address, "$")(1)
End Function

VG
Yal
Anzeige
AW: VBA - Formula.Local
19.04.2021 19:26:31
onur
Und wieso schreibst du mir das?
AW: VBA - Formula.Local
19.04.2021 19:38:26
Yal
.. weil ich Dich mag. Nein, Scherz (was trotzdem nicht das Gegenteil bedeutet ;-)
Ich tendiere dazu, mich am Thread einzureihen, weil ich es als Gedanke-Einreihung statt private Konversation sehe.
Ausserdem macht es den Sprung zum Folgeantwort leichter. Was in den Fall nicht ganz unpassend war, weil ich es als Erweiterung deines Vorschlags gesehen hatte (ich wäre ohne deine Beitrag nicht zu dieser Doppelung gekommen).
Viele Grüße aus Tübingen
Yal
AW: VBA - Formula.Local
19.04.2021 19:41:29
onur
Was ich nicht verstehe: Es soll doch laut Aufgabenbeschreibung nach dem Wort in P7 gesucht werden - Wie kann es dann sein, dass P7 leer ist?
Anzeige
AW: VBA - Formula.Local
19.04.2021 19:57:27
Yal
Onur,
ich fürchte, Du verzettelst dich zwischen den Threads. Die Suche nach dem Wert in "P7" ist dort:
https://www.herber.de/forum/archiv/1824to1828/t1826709.htm
Aber zu deiner Frage:
Mit Range.Find kann man tatsächlich nach einer leeren String suchen und damit alle leere Zelle abklappern. Aber das Problem der endlose Schleife lag, wie Daniel es berichtigt hat, an der Prüfung, dass der nächste Treffer nicht der erste Treffer war. Sonst "same procedure as last Find, Ms Sophie?"
VG
Yal
AW: VBA - Formula.Local
20.04.2021 11:30:53
Guest
Danke für den Tipp :)
Anzeige

190 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige