SVerweis in VBA

[alkt94]

Hallo zusammen,

ich habe ein kleines Makro geschrieben, das das manuelle Kopieren vom SVerweis überflüssig machen soll.
Ich weiß leider nicht wie man die SVerweis Alternative in VBA abbildet.
Weiß jemand wie ich die rot markierten Zeile so schreiben kann, dass sie in Makros genau so funktionieren wie in VBA und hierbei auch Zeilenweise über die Loop abgebildet werden, d.h. statt A2 Cells(Zeile,1) mit Zeile= Zeile +1 in Loop?

Danke vielmals vorab! 

Sub test()
    Dim Eingabewert As String, z As Integer
    Eingabewert = MsgBox("Möchten Sie die Plant und Produkfamilie sehen?", vbYesNo)
    Zeile = 2
    Do While (Cells(Zeile, 1) <> "")    ' Schleife
        If Eingabewert = vbYes Then     ' Einlesen JA/NEIN
            Cells(Zeile,1) = SVERWEIS(A2,[MASTER.xls]Sheet1!$A:$E,5,0)
            Cells(Zeile, 1)= SVERWEIS(A2, [MASTER.xls]Sheet1!$A$2:$I$881,8,0)
            
            Zeile = Zeile + 1
            
        End If
    Loop

End Sub

[MisterBurns]

Hallo,

du kannst mit FormulaLocal arbeiten:

Code:

Cells(Zeile, 1).FormulaLocal = "=SVERWEIS(A" & Zeile & "[MASTER.xls]Sheet1!$A:$E;5;0)"

Beachte allerdings, dass du die Formel in Spalte A hinein- und damit deinen Suchwert überschreibst. Das kann so nicht gewollt sein. Willst du den SVERWEIS also in Spalte B schreiben, brauchst du

Code:

Cells(Zeile, 2)

[Klaus-Dieter]

Hallo,

ich habe ein kleines Makro geschrieben, das das manuelle Kopieren vom SVerweis überflüssig machen soll.

warum willst du die Formel(n) denn kopieren?

[alkt94]

Hallo,

es geht um ein Datenblatt, das von einem unternehmenseigenen Tool wöchentlich generiert wird und das gesamte Produktportfolio abbildet.
Nun ist es allerdings so, dass im Rahmen einer strukturellen Umgestaltung die Verantwortlichkeiten für Produkte/Produktgruppen aufgeteilt werden.

Ich habe hierzu eine Masterdatei erstellt, die für alle wöchentlichen Reports hinhalten soll und aus der sich später die Unternehmensbereiche generieren lassen, um danach filtern zu können, sprich wenn Prod.Nr. 1/2/3 --> Bereich X/Y/Z und der Rest kann ausgeblendet werden.
Da es sich um eine sehr breites Produktportfolio handelt macht das durchaus Sinn.

Ich wollte das ganze durch VBA "automatisieren", sodass man sich nicht lästig SVerweise kopieren muss. :)

[MisterBurns]

Und gibt es auch eine Rückmeldung zu meinem Vorschlag?

[Klaus-Dieter]

Hallo,

Ich wollte das ganze durch VBA "automatisieren", sodass man sich nicht lästig SVerweise kopieren muss. :)

das hatte ich schon verstanden, ich wollte den Aufbau der Dateien sehen, weil ich sicher bin, dass es da einen besseren Weg gibt, als das Kopieren von Formeln per VBA.

[alkt94]

Hallo und vielen Dank!
Ich war noch ein wenig am Propieren, habe allerdings einen Laufzeitfehler 424 angezeigt bekommen 
Da in O2 mit dem Schreiben begonnen wird, habe ich Spalte 15 gewählt. Ich nutze Komma statt Semikolon, weil mein System scheinbar amerikanisch eingestellt ist.

Zeile = 2
    
    Do While (Cells(Zeile, 1) <> "")
        If Eingabewert = vbYes Then
            
            Cells(Zeile, 15).Formula.local "=SVERWEIS(A" & Zeile & ",[MASTER.xls]Sheet1!$A:$E,5,0)"
            ...
            
            Zeile = Zeile + 1
        End If

---

Hallo,

ich konnte leider kein Bild einfügen, weshalb ich versucht habe das Ganze schemenhaft darzustellen (sorry).
Die Produktnummer ist wie gesagt in der wöchentlichen Download-Datei. Angefügt werden sollen die beiden Spalten O und M, die sich aus der Master-Datei mit Bezug auf Produktnummer die Produktfamilie und den Bereich ziehen.

A              ...        O                                M  
Produkt    ...        Produktfamilie          Bereich            
11111      ...        X                                 Fertigung
22222      ...        X                                 Verpackung
33333      ...        Z                                 Distribution

[MisterBurns]

Du solltest zumindest richtig kopieren. Wie kommst du auf den Punkt zwischen "Formula" und "Local"?

Ich nutze Komma statt Semikolon, weil mein System scheinbar amerikanisch eingestellt ist

Und du kannst eine deutsche Formel mit Kommata verwenden? Man lernt ja nie aus.

[maninweb]

Hallo,

ich würde ja eher empfehlen, grundsätzlich die englische Formel zu nehmen, um sprachunabhängig zu sein.

Code:

Cells(Zeile, 15).Formula = "=VLOOKUP(A" & Zeile & ",[MASTER.xls]Sheet1!$A:$E,5,0)"

@MisterBurns, man kann auch ein beliebiges Zeichen nehmen (nicht sinnvoll), Listentrennzeichen
in den Windows-Regionseinstellungen ändern.

Gruß

[alkt94]

Danke für den Hinweis, aber selbst, wenn ich deine Formel 1:1 kopiere, sowohl mit Komma als auch mit Semikolon, funktioniert es nicht und wirft einen Laufzeitfehler aus.
Vom Grundprinzip und Verständnis müsste es stimmen, ich weiß echt nicht woran es liegt...
Und ja: Der SVerweis funktioniert bei mir tatsächlich mit Komma, gerade nochmal im "normalen Excel" probiert.

So sieht mein Code nun komplett aus: 

Code:

Sub Plantbezug()

   Dim Eingabewert As String, zeile As Integer
   
   
   Eingabewert = MsgBox("Möchten die Plant angezeigt bekommen?", vbYesNo)
   
   Zeile = 2
   
   Do While (Cells(Zeile, 1) <> "")
       If Eingabewert = vbYes Then

           Cells(Zeile, 15).FormulaLocal = "=SVERWEIS(A" & Zeile & "[MASTER.xls]Sheet1!$A:$E; 5; 0)"
           Debug.Print Cells(Zeile, 1)
           
           Zeile = Zeile + 1
       End If
   Loop
End Sub

Die Fehlermeldung tritt bei der Zeile Cells(zeile,...) auf.