VBA - Formula.Local | HERBERS Excel Forum

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 :)

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

VBA - Formula.Local

Forumthread: VBA - Formula.Local

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

VBA-Formeln lokal in Excel verwenden

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)