Office: wennfehler in VBA Code implementieren

Hallo zusammen,

ich möchte gerne in meinen VBA-Code die klassische wennfehler-Funktion implementieren (geschachtelt mit einem VLOOKUP). Beim Starten des Codes gibt's aber die Fehlermeldung

"Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden"

der Ausschnitt des Codes sieht wie folgt aus:

DABC.Activate
x = 16
Do Until DABC.Cells(x, 1).Value = ""

DABC.Cells(x, 3).Value = WorksheetFunction.IfError(WorksheetFunction.VLookup(DABC.Cells(x, 1).Value, DBED.Range("A2").CurrentRegion, 368, False), 0)

x = x + 1
Loop


Ich habe leider keinen hilfreichen Beitrag gefunden darum hoffe ich als VBA-Anfänger wieder einmal auf eure Expertise!
Hat jemand einen Vorschlag?

Vielen herzlichen Dank!

LG
Rainer

:)

 

Hallo Rainer,

nicht nur VBA kann da etwas nicht zuordnen. Ich auch nicht, denn es fehlen entscheidende Passagen des Codes. Das fängt damit an, dass ich nicht weiß, was DABC ist. Ganz davon abgesehen, dass ich mich frage, wozu das erst Aktiviert werden muss ...

 

Hallo Günther,
DABC ist ein von mir definiertes Tabellenblatt und es muss natürlich nicht aktiviert werden, jedoch habe ich das vorerst nur so angegeben, damit ich beim Testen des Skriptes mitschauen kann... würde nach Fertigstellen gelöscht werden und ist zugegebenermaßen verwirrend, das hier so ins Forum zu stellen. Hier nochmal ein genauerer Auszug zum besseren Verständnis. Hoffentlich ist dann mein Anliegen klarer:

Sub AnalyseLager()

Dim DISPO As Workbook
Set DISPO = Workbooks("GPF-T (Berechnung Dispoparameter)")
Dim DBED As Worksheet
Set DBED = DISPO.Worksheets("Bedarfe")
Dim DABC As Worksheet
Set DABC = DISPO.Worksheets("ABC-Analyse")
Dim x As Long

'SVERWEISE FÜR DABC DURCHFÜHREN (Spalte A in DABC enthält bereits Materialnummern, Spalte B beinhaltet bereits Bezeichnung des Materials, in Spalte C sollen nun aus dem Tabellenblatt DBED die Bedarfe aus Spalte 368 übernommen werden FALLS vorhanden)

x = 16
Do Until DABC.Cells(x, 1).Value = ""
DABC.Cells(x, 3).Value = WorksheetFunction.IfError(WorksheetFunction.VLookup(DABC.Cells(x, 1).Value, DBED.Range("A2").CurrentRegion, 368, False), 0)
x=x+1
Loop



Bemerkung: x=16 weil der Tabellenbereich in DABC ab Zeile 16 beginnt bzw. ab dieser Zeile der Sverweis mittels VBA beginnen soll

Ich hoffe es ist nun besser verständlich oder fehlen weitere Erläuterungen?

LG

 

Also, eine (anonymisierte) Musterdatei wäre schon hilfreich, denn ich steige da nicht so wirklich durch ... Die kann auch weniger Zeilen haben, Hauptsache die entscheidenden sind drin.

Und: Muss es eine Funktion sein, die eingetragen wird? Oder eichen die einmalig durch VBA berechneten Werte?

 

Hi,

nicht getestet!

Code:

 

Hi,

Super! das funktioniert genauso wie ich mir das vorgestellt hatte!
Spätestens morgen werde ich mir das ganze dann nochmal in aller Ruhe ansehen um es auch im Detail zu verstehen (bin VBA-Anfänger)

Da ich bereits eine Bsp-Datei zusammengestellt habe stelle ich die Datei online sobald ich zuhause bin (von der Firma wird mir das geblockt). Dann kann sich das jeder der interessiert ist genauer ansehen

PS: in der Bsp-Datei ist auch noch eine kleine Frage integriert zum Thema "Implementierung von Matrixformeln in VBA-Codes".
Falls mir da jemand Hilfestellung geben will bin ich natürlcih dankbar. Das Problem ist im Skript ganz verständlich beschrieben und ist in Kombination mit der Datei auch einfach verständlich. Wie gesagt, wird upgeloadet sobald ich heim komme...

Vielen Dank nochmal und ein Hoch auf dieses Forum !! *Smilie

LG
Rainer

 

Guten Morgen,

hier wie angekündigt die Datei mit dem Implementierten Code von Chris.
Um Hilfestellungen bezüglich der Matrixformel-Implementierung wäre ich sehr dankbar!

Beste Grüße,
Rainer

 

Hi,
ich habe mir die Datei angesehen, warum ein Indirekt drinnen ist erschließt sich mir noch nicht

ich denke mal das hat was mit den Nullwerten zu tun. Kannst du die Formel im Threat kurz beschreiben, was diese machen sollten.
Bsp. Nimm den Bereich von x bis y wenn …

Ich denke mal da gibt es eine einfachere Lösung, als die bestehende und diese wäre wiederum leichter zu implementieren.

 

Hallo,

In der Spalte NG "Anzahl Bedarfstage für kalk. Jahresbedarf" soll folgendes berechnet werden:

Suche das ERSTE Datum im Betrachtungszeitraum mit Bedarf sowie das LETZTE Datum im Betrachtungszeitraum mit Bedarf und zähle die Anzahl der Tage dazwischen (auch jene Tage, welche innerhalb dieser Periode keinen Bedarf aufweisen)

Ziel dieser Berechnung ist es in einem weiteren Schritt einen aufgerechneten/kalkulatorischen Jahresbedarf zu ermitteln nach der Denkweise:

Ich habe innerhalb eines Jahres (=Betrachtungszeitraum) ein Material welches beispielsweise am Tag 30 ins Portfolio aufgenommen wird und für die Dauer von 3 Monaten benötigt wird (das Material ist also sagen wir 3*30=90 Tage "aktiv").
Für die Berechnung einer optimalen Bestellmenge benötige ich einen Jahresbedarf. Einfach die Summe der Bedarfe im Betrachtungszeitraum als Jahresbedarf zu nehmen, währe jedoch verfälschend. Deshalb errechne ich mir einen kalkulatorischen Jahresbedarf wie folgt aus:

Jahresbedarf kalk.=(SUMME BEDARFE DES BETRACHTUNGSZEITRAUMS / ANZAHL BEDARFSTAGE)*365

Deshalb benötige ich diese Spalte und die darin enthaltene Formel war mein Ansatz und liefert auch das richtige Ergebnis... Für "einfachere" oder sinnvollere Lösungen bin ich jedoch natürlich immer gerne offen!

Ich hoffe meine Erklärung war halbwegs nachvollziehbar

LG,
Rainer

 

Hallo Rainer,

Ich meinte eigentlich etwas anderes^^
Aber ich habe mir jetzt noch mal dein Konstrukt angeschaut.
Da wird mit Kanonen auf Spatzen geschossen.
Damit dein Bereich richtig erkannt wird:

1. sollten Nullwerte ignoriert werden wenn damit die Zahlenreihe begingt
2. Nullwerte innerhalb von Zahleneinträgen sollen genommen werden
3. Nullwerte wo keine Zahl mehr Links in den Zeilen vorkommt sollten ignoriert werden.

darum die Matrix-Operation inklusive Indirekt.
Der Nachteil dabei ist das genau dieses beiden die größten Bremsen sind die Excel bietet. ^^ bei größeren Datenmengen geht da Excel leicht in die Knie.

Ich würde diese "Nuller" einfach entfernen, dann wären die Formeln bzw. die VBA Lösung ein Klacks.
Aber wie entstehen diese Nullwerte ist das ein Import, Verknüpfung oder werden diese mit der Hand eingegeben.

vgl. einmal die Ergebnisse in der Datei und siehe Dir einmal die Kurzform der Formel an.
Aber die Frage ist eben wie entstehen diese NULLER...

 

Hallo Chris,

sorry wenn ich das vorher falsch verstanden habe.
Genau wie du schon geschrieben hast, sollen alle Nullwerte ignoriert werden, außer jene, welche sich in der Zahlenreihe befinden.

Die Bedarfszahlen werden aus einer anderen Mappe importiert. In dieser Mappe werden über die Stückliste für alle Fertigteile welche im Unternehmen produziert werden, die Bedarfe an Halbfabrikaten, Zukaufteilen, Rohstoffen und Hilfsstoffen berechnet und in einer Sammelmatrix dargestellt. dies Matrix kann man sich ca so vorstellen:


................1.1.2014.....2.1.2014.....3.1.2014.............31.12.2014
Material1.......35...............67..............23........................87
Material2.......120............154..............0........................214
Material3.......68...............74..............64.......................54
...


Es gibt auf unterster Stüli-Ebene ca. 700 Materialien und 365 Tage im Jahr... auf jeder Stücklistenebene werden in eigenen Tabellenblättern ähnliche Matrizen aufgebaut und dann eben in einem GESAMTÜBERSICHTSBLATT zusammengeführt (Man kann sich vorstellen wie riesig diese Datei ist)


Anschließend wird diese Übersicht (diese enthält wie gesagt alle Bedarfe an HF, RS, HS, ZKT) in meine Arbeitsmappe kopiert. Dann weren jene Materialien ohne Bedarf gleich einmal rausgeschnitten um die Matrix nicht größer als unbedingt nötig zu machen. Das Ganze soll noch mit VBA programmiert werden, sodass auch dann keine Formeln mehr in der Matrix stehen, sondern nur noch Zahlenwerte, so wie es im Bsp-file der Fall ist.

Du meinst, wenn man die Nullwerte durch leere Zellen ersetzt, dann wäre es einfach zu bewerkstelligen, die benötigten Zahlen zu berechnen ohne Indirekt und Matrixformeln? Das sollte doch relativ einfach möglich sein oder? Indem man zB im range "Matrizenbereich" als erstes alle Nullwerte per Makro .clear setzt?

Bin ich auf dem richtigen Dampfer? *Smilie

LG

 

Hi,

ja, genau!

die Dateigröße schrumpft, Berechnungen sind einfacher, Programmlaufzeiten werden geringer!

 

Ok, das Löschen der Nullwerte habe ich jetzt mal mit diesem Ansatz gelöst:

Sub NullwerteLoeschen()
Dim wbNWL As Workbook
Set wbNWL = Workbooks("Nullwerte aus Matrix löschen.xlsm")
Dim wsNWL As Worksheet
Set wsNWL = wbNWL.Worksheets("NWL")
Dim rngBED As Range
Set rngBED = NWL.Range("C2:Q23")

rngBED.SpecialCells(xlCellTypeConstants, xlNumbers).Replace 0, "", xlWhole

End Sub


Ich hoffe der Ansatz macht Sinn (Bin VBA Anfänger und gerade erst dabei mir selbst Sachen beizubringen)

Ich hoffe ich komm dann auch noch auf die vereinfachten Formeln in den Spalten wo ich zZ diese Matrixformeln habe. Wenn ich das richtig verstanden habe hast du ja bereits in meiner BSP-Datei diesbezüglich was vorgenommen. Leider wird mir der download und upload von Files von der Firma aus aber wie gesagt geblockt, weshalb ich mir das erst daheim ansehen kann heute Abend.


Aber vielen Dank erst Mal für deine Eingebungen *wink.gif*

Beste Grüße,
Rainer

 

HI,

die Formeln, ja die sind dann einfacher ^^

*Smilie

für die Nullwerte muss aber noch ein anderer Code her...
denn die Nullwerte innerhalb der Matrix sollen ja erhalten bleiben.

Wenn ich dazukomme werde ich da was basteln, was steht den in Original drinnen, wirklich nur 0 oder =verknüpfte Zelle

 

Super, danke dir!

Im Original wird im ersten Schritt die komplette Matrix "Bedarfe gesammelt" aus dem der Bedarfsmappe (das Riesen Ding mit den Stücklisten) kopiert und eingefügt. Aber nur die Werte, nicht die Formeln. Das sollte doch auch mit VBA möglich sein...

Dann wird die riesen Bedarfsmappe geschlossen um die Geschwindigkeit der Excel-Berechnungen bzw. Ausführungen nicht negativ zu beeinflussen (denn diese Quell-Mappe wo die Bedarfe laut Stüli berechnet werden hat knapp 23 MB^^).

Es stehen also in der Matrix mit den Bedarfen vom Analysefile (so wie im BSP-File) nur 0 drinnen, keine Verknüpfungen oder Bezüge mehr.

Sobald dieser Zustand hergestellt ist soll dann mit den verschiedenen Berechnungen erst begonnen werden...



LG