Benutzerdefinierte Excel-VBA-Funktion (Modifiziert SVERWEIS) aus der Zelle bezieht sich auf einen Bereich in der anderen Datei, gibt eine Fehlermeldung
Ersten mal Schreibe ich einen stackOverflow-Frage, also bitte lassen Sie mich wissen, wenn ich etwas Falsches tun.
Ich gesucht habe für ein paar Stunden jetzt und nicht in der Lage gewesen, eine Lösung zu finden für mein problem (in der Regel finde ich die Antwort daher nicht warum das ist meine erste Frage nach der Verwendung von stackoverflow für ein paar Jahre als lurker).
Grundsätzlich bin ich versucht zu schreiben, eine modifizierte Funktion SVERWEIS, die ähnliche Funktionen wie SVERWEIS, außer dass es die Rückkehr der "nächste kleinste größere" Wert anstelle des Standard - "bisher größte kleineren" Wert. Ich bin mir bewusst, dass der index/match-Methode leider würde ich benötigen, um sorgfältig zu ersetzen, die von buchstäblich tausenden von VLOOKUPs manuell, die bereits in der Arbeitsmappe ich bin momentan Reinigung bei der Arbeit. Daher Griff ich zu schreiben ein VLOOKUPnew-Funktion, so konnte ich nur ein "suchen/ersetzen" alle die SVERWEIS mit VLOOKUPnew.
Function VLOOKUPnew(lookup_value As Variant, table_array As Range, _
col_index_num As Integer, Optional exactMatch As Boolean) As Variant
Dim row As Integer
Debug.Print table_array.Address
With Application
On Error GoTo NO_ROW
row = .Match(lookup_value, table_array.Columns(1), 0)
On Error GoTo 0
If row = -1 And Not exactMatch Then
row = .Match(lookup_value, table_array.Columns(1), 1)
row = row + 1
End If
VLOOKUPnew = .index(table_array.Columns(col_index_num), row, 0)
End With
Exit Function
NO_ROW:
row = -1
Resume Next
End Function
Und ich Erfolg in dem schreiben der Funktion, sondern schlagen einen Haken. Da ich als "Matrix" als einen Bereich, vba scheitert zu identifizieren Bereich Verweise auf andere Arbeitsmappen
z.B. "=VLOOKUPnew($A432,'reallyLongFilepath/[filename.xlsx]Tabellenname'!$B$6:$N$35,spalteanzahl,0),FALSE)" wird in einen #WERT-Fehler
Die wirklich seltsame Sache ist, dass wenn ich die Datei öffnen, dann den Dateipfad Tropfen aus der Formel (immer nur "=VLOOKUPnew($A432,'[filename.xlsx]Tabellenname'!$B$6:$N$35,spalteanzahl,0),FALSE)") und dann meine benutzerdefinierte Funktion funktioniert Prima Rücksendung der korrekte Wert.
Also mein problem ist wie löse ich nicht mit zu öffnen der anderen Datei zur Verwendung dieser Arbeitsmappe. Ich bin mir auch nicht sicher, wie Sie Excel übergeben Sie die Adresse oder den Bereich um die benutzerdefinierte Formel, so bin ich besorge, es bricht, wenn der Dateipfad wird in die Produktpalette aufgenommen Referenz. Gibt es eine Möglichkeit zum splitten der Dateipfad, Dateiname, Blatt und Adresse (nachdem er verabschiedet worden)? Oder vielleicht übergeben Sie als string dann einfach aufgeteilt? Oder übergeben Sie es als etwas, das richtig zu erkennen, die den Bereich in der anderen Arbeitsmappe?
Beachten Sie, dass ich versuche zu vermeiden, ändern Sie die Argumente der Funktion, weil ich will die suchen/ersetzen-trick und dass dies für die Arbeit, damit es eine Zurückhaltung auf zu viel ändern im data-layout. Auch, dass die Arbeitsmappe ist für andere Mitarbeiter zu nutzen und ich bin nur setzen Sie es für den Einsatz.
Vielen Dank im Voraus!
Andrew
- vielen Dank für diesen code
Du musst angemeldet sein, um einen Kommentar abzugeben.
Ihr Gesicht ziemlich dilemma hier!
Das root-problem ist, dass, während
VLOOKUP
Aussehen kann in geschlossenen Arbeitsmappen, einRange
parameter in einemUDF
nicht. Der Bereich Referenz-behebt einen Fehler, so schlägt der Funktionsaufruf fehl, mit einem Typenkonflikt. Wenn Sie ändern dietable_array
parameter TypVariant
und legen Sie eine Pause auf der header-Funktion, werden Sie sehen, der Wert des Parameters alsError 2036
.Zwar gibt es Methoden, sich in geschlossenen Arbeitsmappen, die alle (AFAIK) Recht langsam. Da Sie erwähnen
... I would need to carefully replace literally thousands of VLOOKUPs ...
ich vermuten, dass eine Lösung entlang dieser Linien wäre unzumutbar langsam.Meine reccomendation wäre, gehen die
INDEX/MATCH
route, und schreiben Sie eineVBA
makro zu tun, die Formel updates für Sie.