Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1360to1364
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

Hilfe bei Makro

Hilfe bei Makro
27.05.2014 22:47:49
Sandra
Guten Abend Allerseits,
ich habe folgendes Makro entdeckt, welches ich gerne für mein Vorhaben verwenden wollte. Leider komm ich mit diesem nicht wirklich klar?
Der Vba Code lautet wie folgt:
------------------------------------------------------------------------------------
Function SverweisMit2Kriterien(Blatt As Worksheet, SuchKriterium1 As String, SuchSpalte1 As  _
Long, SuchKriterium2 As String, SuchSpalte2 As Long, ErgebnisSpalte As Long) As Variant
Dim ZeileMax As Long
Dim Wsf As Worksheet

Function
Set Wsf = Application.Worksheet

Function
With Blatt
ZeileMax = Wsf.Max(.Cells(.Rows.Count, SuchSpalte1).End(xlUp).Row, .Cells(.Rows.Count,  _
SuchSpalte2).End(xlUp).Row)
strAdresseSpalte1 = Blatt.Name & "!" & Replace(.Range(.Cells(2, SuchSpalte1), .Cells(ZeileMax,   _
_
SuchSpalte1)).Address, "$", "")
strAdresseSpalte2 = Blatt.Name & "!" & Replace(.Range(.Cells(2, SuchSpalte2), .Cells(ZeileMax,   _
_
SuchSpalte2)).Address, "$", "")
strAdresseErgebnisSpalte = Blatt.Name & "!" & Replace(.Range(.Cells(2, ErgebnisSpalte), .Cells(  _
_
ZeileMax, ErgebnisSpalte)).Address, "$", "")
SverweisMit2Kriterien = Evaluate("=index(" & strAdresseErgebnisSpalte & _
",match(""" & SuchKriterium1 & SuchKriterium2 & """," & strAdresseSpalte1 & "&" & _
strAdresseSpalte2 & ",0))")
End With
End 

Function
Wie, wo und was muss ich eintragen?
Ich habe in "Sheet2" / Zelle A1 "=SverweisMit2Kriterien("Sheet3!";"4";1;"grün";2;3)"  _
eingetragen.
In Sheet3 sind die Such- und Ausgabekriterien, allerdings wir WERT# ausgegeben.
Was mache ich Falsch?
Danke und schönen Grüße, Sandra
Anbei das Sheet:
https://www.herber.de/bbs/user/90873.xlsm
_

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hilfe bei Makro
27.05.2014 22:58:10
Sandra
Anbei noch mal der Code:
Function SverweisMit2Kriterien(Blatt As Worksheet, SuchKriterium1 As String, SuchSpalte1 As  _
Long, SuchKriterium2 As String, SuchSpalte2 As Long, ErgebnisSpalte As Long) As Variant
Dim ZeileMax As Long
Dim Wsf As Worksheet

Function
Set Wsf = Application.Worksheet

Function
With Blatt
ZeileMax = Wsf.Max(.Cells(.Rows.Count, SuchSpalte1).End(xlUp).Row, .Cells(.Rows.Count,  _
SuchSpalte2).End(xlUp).Row)
strAdresseSpalte1 = Blatt.Name & "!" & Replace(.Range(.Cells(2, SuchSpalte1), .Cells(ZeileMax,  _
SuchSpalte1)).Address, "$", "")
strAdresseSpalte2 = Blatt.Name & "!" & Replace(.Range(.Cells(2, SuchSpalte2), .Cells(ZeileMax,  _
SuchSpalte2)).Address, "$", "")
strAdresseErgebnisSpalte = Blatt.Name & "!" & Replace(.Range(.Cells(2, ErgebnisSpalte), .Cells( _
ZeileMax, ErgebnisSpalte)).Address, "$", "")
SverweisMit2Kriterien = Evaluate("=index(" & strAdresseErgebnisSpalte & _
",match(""" & SuchKriterium1 & SuchKriterium2 & """," & strAdresseSpalte1 & "&" & _
strAdresseSpalte2 & ",0))")
End With
End 

Function

Anzeige
Dein Posting ist ganz schön verquer, ...
28.05.2014 01:05:00
Luc:-?
…Sandra,
weil du nicht die HTML-Code-Tags (pre) benutzt hast. So hat die Forumssoftware die gesetzt, was hier alles durcheinanderbringt, weil das nur bei sehr simplen PgmCodes zufriedenstellend fktioniert!
Außerdem ist diese Fkt eine typische, wenn auch unprofessionelle PgmiererFkt, die offensichtlich für den Einsatz in einer Subroutine gedacht ist. Unprofessionell ist sie schon deshalb, weil zwar ein separates ReferenzObjekt für WorksheetFunction angelegt wird, was man selten sieht, aber gleichzeitig trotz des With-Konstrukts für/mit der ObjektVariablen Blatt diese innerhalb des Konstrukts verwendet wird (Blatt.Name), obwohl sie da logischerweise stets fehlen darf (der führende Pkt ersetzt sie dann, wie bei .Range). Blatt ist dann auch das Argument, an dem alles scheitert - die Fkt wird gar nicht erst durchlaufen -, denn hier wird ein Arbeitsblatt-Objekt verlangt, was bei Argumentierung der Fkt aus einer Subprozedur heraus kein Problem ist, aber offensichtlich in einer ZellFml!
Du verwendest die Fkt also als sog UDF in einer ZellFml. Hier wäre es zweckmäßig, das 1.Argument als Text anzugeben, was du auch getan hast (aber bitte ohne !). Nur ist hier eben kein Text verlangt, was sofort den StandardFehler #WERT! verursacht! Ändere also die Deklaration dieses Arguments im FktsKopf auf As String! Die nächsten PgmZeilen sollten dann so aussehen:
Dim ZeileMax As Long
Dim Wsf As WorksheetFunction, Wsh As Worksheet
Set Wsf = WorksheetFunction: Set Wsh = Worksheets(Blatt)
With Wsh
Nun sollte die Fkt auch als UDF in einer ZellFml fktionieren, obwohl noch mehr Stümperhaftes enthalten ist wie dieses äußerst überflüssige Replace. Wenn man eine relative A1-Adresse braucht, schreibt man .Address(0, 0) und löscht nicht nachträglich die $-Zeichen!
Blatt.Name durch .Name ersetzen nicht vergessen und natürlich noch die TextZahl im 2.FktsArgument deiner Fml durch eine echte Zahl ersetzen ("4" → 4), weil in deiner Tabelle ja auch echte Zahlen stehen und keine TextZahlen (wird sonst nicht gefunden)!
Ansonsten übernehme ich auch für das richtig korrigierte Produkt keinerlei Garantie — meine UDFs sehen anders aus! ;-]
Gruß Luc :-?

Anzeige
AW: Hilfe bei Makro
28.05.2014 02:01:53
Daniel
Hi
der Grundfehler ist dass das Makro das Tabellenblatt als Worksheet-Objekt erwartet:
Function SverweisMit2Kriterien(Blatt As Worksheet,
dh du kannst diese Funktion so gar nicht in einer Excelzelle nutzen, weil du in der Formel kein Worksheet-Objekt übergeben kannst.
Du verschst es mit einem Textstring, und das klappt natürlich nicht.
die Einfachste Lösung wäre, hier das Blatt auch als Textstring in die Formel aufzunehmen:
Function SverweisMit2Kriterien(BlattName As String, SuchKriterium1 As String, SuchSpalte1 As  _
Long, SuchKriterium2 As String, SuchSpalte2 As Long, ErgebnisSpalte As Long) As Variant
Dim ZeileMax As Long
Dim Wsf As WorksheetFunction
Dim Blatt As Worksheet
Set Wsf = Application.WorksheetFunction
Set Blatt = Sheets(BlattName)
With Blatt
.... dann unverändert weiter
in der Formel musst du dann den Blattnamen vom Reiter (ohne das Ausrufezeichen) als Parameter angeben:
=SverweisMit2Kriterien("Sheet3";"4";1;"grün";2;3)
allerdings hat deine Funktion einen ganz gravierenden Nachteil wenn du sie so in einer Excelzelle verweden willst:
Dadurch, dass du keine Zellbezüge verwendest, wird deine Funktion nur einmalig bei der Eingabe neu berechnet und nicht mehr bei Änderungen im Tabellenblatt.
Daher ist die Funktion nur gut für die Verwendung innerhalb von Makros, aber für Excelformeln ist sie ungeignet.
Gruß Daniel

Anzeige
Und welche NEUEN Erkenntnisse waren es nun! owT
28.05.2014 04:45:22
Luc:-?
:-?

AW: Und welche NEUEN Erkenntnisse waren es nun! owT
28.05.2014 06:30:59
Daniel
ich habe nach den ersten zwei Zeilen deines Beitrags aufgegeben, das zu lesen und zu veruschen zu verstehen.
nach deiner Einleitung bin ich nicht davon ausgeganten, dass da noch was sinnvolles kommt
von daher dachte ich mir, ich beschreibe das Problem mal kurz und verständlich und mit einem lesbasren Codebeispiel und ohne schwer verständilche Abkürzungen, so dass auch der Laie das ohne schwierigkeiten lesen kann.

Aha, hast du also für DICH geschrieben und ...
28.05.2014 14:31:20
Luc:-?
…dabei leider noch was vergessen, Daniel… ;->
Im Ggsatz zu deinen Orthografie-„Verrutschern“ sind meine Abkk Absicht und meist relativ leicht erschließbar. Lies mal deinen letzten Text noch mal in aller Ruhe…! :->>
Mein Text war übrigens von Anfang an „sinnvoll“ (wie allermeist), aber das muss sich ja einem zum „Übersetzer“ resp Lektor/Korrektor Selbstberufenen nicht unbedingt erschließen.
Luc :-?

Anzeige
AW: Hilfe bei Makro
28.05.2014 17:30:57
Sandra
Hi Daniel,
habe soeben mit deinen Anmerkungen das Makro ausgebessert und versteh jetzt was du mit der Aussage meinst "Dadurch, dass du keine Zellbezüge verwendest, wird deine Funktion nur einmalig bei der Eingabe neu berechnet und nicht mehr bei Änderungen im Tabellenblatt".
Wie kann ich die Funktion so abändern das diese auch als "dynamische"-Funktion zu verwenden ist.
Danke dir schon mal im Voraus
Gruß Sandra

AW: Hilfe bei Makro
28.05.2014 09:08:01
Sandra
Guten Morgen Jungs,
vielen Dank für eure Tipps (und die konstruktive Kritik).
Werde heute Nachmittag den Code gleich mal ausbessern. Da ich in den Antworten herauslese das, dass Makro noch Verbesserungspotenzial hat, könnt Ihr mir gerne euer Code zusenden.
Da die Tabelle eine sehr große Matrix durchsuchen muss, wäre es super wenn man unnötige Schleifen etc. eliminieren würde.
Wie gesagt vielen Dank noch mal und schöne Grüße
Sandra

Anzeige
Im "Makro" ist (fast) nichts Unnötiges, nur ...
28.05.2014 14:09:15
Luc:-?
…Ungeschicktes und Unpassendes für die Art, in der du es als UDF einsetzen willst, Sandra,
schon gar keine Schleifen. Aber man kann ja noch ein „Schleifchen“ rumbinden, quasi zum Verschenken. Musst du nur noch seine Farbe wählen… ;-))
Änderungsmöglichkeiten wurden dir benannt!
Gruß Luc :-?

Übrigens benötigst du das Makro gar nicht, ...
28.05.2014 21:07:40
Luc:-?
…Sandra,
da du ja ohnehin mit Formeln arbeiten willst. SUMMENPRODUKT macht alles, was du erreichen willst:
=SUMMENPRODUKT((Sheet3!A1:A10=4)*(Sheet3!B1:B10="grün")*Sheet3!C1:C10)
Das setzt allerdings voraus, dass die gesuchte KriterienKombi immer nur 1× auftritt. Das ist bei deinem Bsp zwar der Fall, aber es ist trotzdem schlecht gewählt, falls die Werte/Begriffe in A:B mehrfach in ihrer Spalte auftreten können. So, wie es ist, benötigt die Auswertung desselben weder dein Makro noch die o.g. SUMMENPRODUKT-Fml. Sollte es allerdings im Original wesentlich komplizierter zugehen, so dass auch SUMMENPRODUKT nicht greift, müsstest du hier mal ein aussagekräftigeres Bsp einstellen.
Übrigens kann man die nach meinen (bzw Daniels) Vorgaben geänderte UDFkt auch so in einer Fml verwenden, dass relevante Änderungen eine Neuberechnung veranlassen:
=SverweisMit2Kriterien("Sheet3";4;SPALTE(Sheet3!A:A);"grün";SPALTE(Sheet3!B:B);SPALTE(Sheet3!C:C))
Deshalb ist Daniels diesbzgl Argument irrelevant (wenn auch ich diese so nicht geschrieben hätte)! ;->
Gruß Luc :-?
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige