MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 17.01.2018, 12:06   #1
Janspitzi
Neuer Benutzer
Neuer Benutzer
Tipp VBA - SVERWEIS in VBA, das makro funktioniert nicht

Hallo ihr lieben,

ich stehe seit tagen auf einem dicken fetten schlauch und komme nicht mehr runter.

Mein problem:

Ich brauche einen SVERWEIS in VBA,

In Tabelle1 "Sachbearbeiter" stehen die SB mit GKZ diese GKZ steht auch in der 2ten Tabelle "Ziel"

Nun soll er in Tabelle Ziel die erste GKZ nehmen und in der Sachbearbeitertabelle suchen, wenn er was gefunden hat die Angaben die hinter der GKZ in der Tabelle Sachbearbeiter stehen in die Tabelle Ziel kopieren. Dort am besten in die erste Freie Spalte.

Mein Code
Code:

Sub VBAsverweis()
Dim z As Long
Dim lz As Long
Dim s As Integer

lz = Range("A1048576").End(xlUp).Row
If Range("A1048576") <> "" Then lz = 61048576

On Error Resume Next
For z = 1 To lz
  Cells(Sheets("Ziel").Select(z, "H")).Value = WorksheetFunction.VLookup(Cells(z, "G").Value, Range(Sheets("Sachbearbeiter").Select("A:G")), 1, False)

Next z
End Sub
Am ende soll das ganze dann in 2 verschiedenen excel datein gemacht werden aber ich würde mcih freuen wenn das ganze dann erstmal in einer excel Tabellen Datei funktioniert.

Mfg Jan

und schonmal danke für eure hilfe
Angehängte Dateien
Dateityp: xlsm Tabelle A.xlsm (30,4 KB, 6x aufgerufen)
Janspitzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 13:35   #2
Klaus-Dieter
MOF Koryphäe
MOF Koryphäe
Standard

Hallo,

da hast du aber einen Haufen Fehler reingekegelt. Versuche es mal damit:
Sheets("Ziel").Cells(z, "H") = WorksheetFunction.VLookup(Sheets("Ziel").Cells(z, "G"), Sheets("Sachbearbeiter").Range("A2:G82"), 1, False)

__________________


Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Klaus-Dieter ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 13:36   #3
Oge
MOF User
MOF User
Standard

Hallo Jan,

A. Vorab
Warum ein Makro ?

B. Fehler
Cells und Range sind Elemente des Sheet-Objektes

aus "Cells(Sheets("Ziel").Select(z, "H")).Value"
wird "Sheets("Ziel").Cells(z, "H").Value"

und aus "Range(Sheets("Sachbearbeiter").Select("A:G")), 1, False)"
wird "Sheets("Sachbearbeiter").Range("A:G"), 5, False)"

C. If-Anweisung
Die Zeile muss man nicht verstehen, oder?

__________________

helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Oge ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 13:45   #4
jack_D
MOF Koryphäe
MOF Koryphäe
Standard

Moin Moin

Mal als Ansatz.
Wenns mehr wird, solltest auch mal über ein DICTIONARY nachdenken =)
Und du musst sauberer Referenzieren!

Code:

Option Explicit

Sub VBAsverweis()
Dim z As Long
Dim lz As Long, LS As Long
Dim s As Integer

With Worksheets("Ziel")
    lz = .Cells(Rows.Count, 1).End(xlUp).Row
    LS = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
        For z = 1 To lz
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 1, False)
        Next z
End With
End Sub
Grüße Jack

@OGE

Zitat:

C. If-Anweisung
Die Zeile muss man nicht verstehen, oder?

Sieht für mich aus, nach dem Versuch die Zeilenbegrenzung von Excel zu eliminieren ;-D

Geändert von jack_D (17.01.2018 um 13:49 Uhr).
jack_D ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 13:50   #5
Janspitzi
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Zitat: von Klaus-Dieter Beitrag anzeigen

Hallo,

da hast du aber einen Haufen Fehler reingekegelt. Versuche es mal damit:
Sheets("Ziel").Cells(z, "H") = WorksheetFunction.VLookup(Sheets("Ziel").Cells(z, "G"), Sheets("Sachbearbeiter").Range("A2:G82"), 1, False)

Hallo @Klaus-Dieter und @Oge

danke für eure Hilfe und fürs erklären.
Das sind meine ersten schritte in VBA.
Es muss ein Makro sein damit die Daten dauerhaft in eine Tabellle geschrieben werden Sollen, Damit die Tabelle Später verschickt werden kann.

Vielleicht habt ihr ja auch für mein 2tes Problem eine lösung und ich muss mir nicht alles zusammen docktern

Wenn mann jetzt davon ausgeht das die beidne Tabellen nun nicht in einer Excel Datei sind sondern 2 unterschiedliche sind. Wie kann ich da das oben angewante umschreiben?
Janspitzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 14:06   #6
Janspitzi
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Zitat: von jack_D Beitrag anzeigen

Moin Moin

Mal als Ansatz.
Wenns mehr wird, solltest auch mal über ein DICTIONARY nachdenken =)
Und du musst sauberer Referenzieren!

Code:

Option Explicit

Sub VBAsverweis()
Dim z As Long
Dim lz As Long, LS As Long
Dim s As Integer

With Worksheets("Ziel")
    lz = .Cells(Rows.Count, 1).End(xlUp).Row
    LS = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
        For z = 1 To lz
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 1, False)
        Next z
End With
End Sub
Grüße Jack

@OGE

Sieht für mich aus, nach dem Versuch die Zeilenbegrenzung von Excel zu eliminieren ;-D

Hi @jack_D

deinen ansatzt habe ich mal verlängert.

Code:

          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 2, False)
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 3, False)
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 4, False)
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 5, False)
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 6, False)
          Cells(z, LS).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, Worksheets("Sachbearbeiter").Range("A:G"), 7, False)
So sieht der nun aus nur der mittelteil. Wen ich das makro nun ausführe nimmt er komscher weiße nur den letzen wert und kopert ihn rüber wenn ich aber die einzelnen durch gehe in dem ich die andern mit ' rausnehmen geht es nur wenn er alle machen soll nimmt er nur den letzen wert.
Janspitzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 14:09   #7
jack_D
MOF Koryphäe
MOF Koryphäe
Weinen

Durchlaufe das Makro mal mit F8

Und schau dir an, was er macht (in der Tabelle)
Ich bin mir sicher, du kommst drauf ...
jack_D ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 14:23   #8
jack_D
MOF Koryphäe
MOF Koryphäe
Standard

Und hier noch ungetestet die Erweiterung für mehrere Workbooks
(sowie der Eliminierung deines Fehlers)- Du musst natürlich die Spalte beim Eintragen auch erhöhen.- Sonst überschreibst du immer die letzte Spalte.

Und um das ganze bisschen ansehnlicher zu gestalten noch in ner Schleife.
Und nicht jeden Sverweis in ner eigenen Codezeile.

Code:

Option Explicit

Sub VBAsverweis()
Dim z As Long
Dim lz As Long, LS As Long
Dim s As Integer, IntSpalte As Integer

Dim strFilename As String: strFilename = "C:...book1.xslx" 'Hier Pfad und Name eintragen

Dim WBEintragen As Workbook: Set WBEintragen = ThisWorkbook
Dim WBSuche As Workbook: Set WBSuche = Workbooks.Open(Filename:=strFilename)


With WBEintragen.Worksheets("Ziel")
    lz = .Cells(Rows.Count, 1).End(xlUp).Row
    LS = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        For z = 1 To lz
            For IntSpalte = 1 To 6
                .Cells(z, LS + IntSpalte).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, WBSuche.Worksheets("Sachbearbeiter").Range("A:G"), IntSpalte + 1, False)
            Next IntSpalte
        Next z
End With
End Sub
jack_D ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 14:29   #9
Janspitzi
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Konfus

Zitat: von jack_D Beitrag anzeigen

Durchlaufe das Makro mal mit F8

Und schau dir an, was er macht (in der Tabelle)
Ich bin mir sicher, du kommst drauf ...

Excel überschreibt bei jeder zeile den wert der drin steht neu....
aber keine ahnung wie ich das ändere

kannst du mir bitte helfen.... bin leider noch nciht so gut in VBA

um ehrlich zusein verstehe ich das:

Cells(z, LS).Value

nicht.
mfg Jan
Janspitzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.01.2018, 14:38   #10
jack_D
MOF Koryphäe
MOF Koryphäe
Standard

easy =)

Zitat:

Cells(z, LS).Value

Ich verwende bevorzugt die cells(rows,column) schreibweise.

Am Beispiel:
.cells(1,3).value heisst: der Wert der Zelle(1. Zeile, 3. Spalte) also Zelle "C1"

Vorteil dieser Schreibweise (meiner Meinung nach) man kann sie sehr einfach in SChleifen einbinden.

Wenn ich also die ersten 10 Zellen der 3. Spalte durchlaufen möchte, schreibe ich also

Code:

For Zeile = 1 To 10
    cells(Zeile,3)
Next Zeile

Ebenso geht dies für Spalten
Wenn ich also die ersten 3 Zellen der 1. Zeile durchlaufen möchte, schreibe ich also

Code:

For Spalte = 1 To 3
    cells(1,Spalte)
Next Spalte

Und nun die Königsdisziplin die Matrix
Die Alle Zellen im Bereich ("A1:C10")

Code:

For Zeile = 1 To 10
    For Spalte = 1 To 3
        cells(Zeile,Spalte)
    Next Spalte
Next Zeile

Also eigentlich ganz easy =)

Deswegen hat er bei dir die gleiche Spalte immer überschrieben,
weil du zwar die Zeile ( Variable "z") in der Schleife erhöht hast
aber die Spalte (Variable "LZ" immer die gleiche geblieben ist)

Geändert von jack_D (17.01.2018 um 14:40 Uhr).
jack_D ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.01.2018, 07:24   #11
Janspitzi
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Zitat: von jack_D Beitrag anzeigen

Und hier noch ungetestet die Erweiterung für mehrere Workbooks
(sowie der Eliminierung deines Fehlers)- Du musst natürlich die Spalte beim Eintragen auch erhöhen.- Sonst überschreibst du immer die letzte Spalte.

Und um das ganze bisschen ansehnlicher zu gestalten noch in ner Schleife.
Und nicht jeden Sverweis in ner eigenen Codezeile.

Code:

Option Explicit

Sub VBAsverweis()
Dim z As Long
Dim lz As Long, LS As Long
Dim s As Integer, IntSpalte As Integer

Dim strFilename As String: strFilename = "C:...book1.xslx" 'Hier Pfad und Name eintragen

Dim WBEintragen As Workbook: Set WBEintragen = ThisWorkbook
Dim WBSuche As Workbook: Set WBSuche = Workbooks.Open(Filename:=strFilename)


With WBEintragen.Worksheets("Ziel")
    lz = .Cells(Rows.Count, 1).End(xlUp).Row
    LS = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        For z = 1 To lz
            For IntSpalte = 1 To 6
                .Cells(z, LS + IntSpalte).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, WBSuche.Worksheets("Sachbearbeiter").Range("A:G"), IntSpalte + 1, False)
            Next IntSpalte
        Next z
End With
End Sub

erstmal 1000 danke für die geile und schnelle hilfe!!

aber ich hocke nun wieder an einem problem mit dem code:

Code:
Code:

Sub VBAsverweis()
Dim z As Long
Dim lz As Long, LS As Long
Dim s As Integer, IntSpalte As Integer

Dim strFilename As String: strFilename = "K:JanExcelTabelle B.xlsx" 'Hier Pfad und Name eintragen

Dim WBEintragen As Workbook: Set WBEintragen = ThisWorkbook
Dim WBSuche As Workbook: Set WBSuche = Workbooks.Open(Filename:=strFilename)


With WBEintragen.Worksheets("Tabelle1")
    lz = .Cells(Rows.Count, 1).End(xlUp).Row
    LS = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        For z = 1 To lz
            For IntSpalte = 1 To 6
                .Cells(z, LS + IntSpalte).Value = WorksheetFunction.VLookup(.Cells(z, 7).Value, WBSuche.Worksheets("Sachbearbeiter").Range("A:G"), IntSpalte + 1, False)
            Next IntSpalte
        Next z
End With
End Sub
in Tabelle A sind eigentlich nur die Sachbearbeiter und in Tabelle B in Tabelle1 soll das alles geschrieben werden....
würde es auch gehen wenn er die Zelle erst sucht in der die GKZ stehen? (TabelleB) GKZ und F_09 (TabelleA)
Janspitzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 00:47 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.