Anzeige
Archiv - Navigation
1600to1604
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

VLOOKUP funktioniert nur auf dem ersten Sheet

VLOOKUP funktioniert nur auf dem ersten Sheet
12.01.2018 09:55:59
Matthias
Hallo zusammen,
ich habe die folgende Funktion in einem meiner Worksheets (alle identisch aufgebaut). Dieses exemplarische Worksheet heißt "Sales", die Daten für VLOOKUP stehen in "Basic Data" und ändern sich nicht.
Bei Doppelkick auf B7 - funktioniert die Funktion auch ohne Probleme. Kopiere ich die gleiche Funktion jedoch in den VBA Bereich der anderen Worksheets (...natürlich passe ich den Namen der Daten des Ziel Worksheets entsprechend an) tut sich bei Doppelklick auf den absoluten Bezuug B7 überhaupt nichts.
Ich habe auch schon versucht die Funktion in ein Modul auszulagern und dann die Ziel Namen mit einer Variablen anzupassen - immer mit dem gleichen Ergebnis, dass die Funktion für das Sheet "Sales" funktioniert und für alle weiteren nicht. Auch habe ich schon versucht für jedes Worksheet jeweisl andere Variablen zu definieren - leider bisher ohne Erfolg.
Wo ist mein gravierender Denkfehler, was mache ich verkehrt? Bei "Sales" funktioniert es doch einwandfrei...
Für Eure Tipps und Hilfe wäre ich sehr dankbar!!!
Viele Grüße
Matthias
Option Explicit
Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Address = "$B$7" Then
Dim i As Long, letzteZeile As Long
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet
Dim Ziel As Worksheet
Dim Bereich As Range
Dim WsF As WorksheetFunction
Dim Value As String
Set Arbeitsmappe = ThisWorkbook
Set Datenbasis = Arbeitsmappe.Worksheets("Basic Data")
Set Ziel = ThisWorkbook.Worksheets("Sales")
letzteZeile = Datenbasis.Range("B" & Rows.Count).End(xlUp).Row
Set Bereich = Datenbasis.Range("B7:C" & letzteZeile)
Set WsF = Application.WorksheetFunction
For i = 7 To Ziel.Range("C" & Rows.Count).End(xlUp).Row
On Error Resume Next
Ziel.Range("C" & i).Value = WsF.VLOOKUP(Ziel.Range("B" & i).Value, Bereich, 2, False)
Next i
End If
End Sub

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VLOOKUP funktioniert nur auf dem ersten Sheet
12.01.2018 10:30:41
Sepp
Hallo Matthias,
Deklarationen gehören an den Beginn einer Prozedur!
Probier mal ohne 'On Error...', vielleicht kommst du dann auf den Fehler.
' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim i As Long, letzteZeile As Long
Dim Datenbasis As Worksheet
Dim Ziel As Worksheet
Dim Bereich As Range
Dim WsF As WorksheetFunction

If Target.Address = "$B$7" Then
  Set Datenbasis = Worksheets("Basic Data")
  Set Ziel = Worksheets("Sales")
  Set WsF = Application.WorksheetFunction
  
  With Datenbasis
    letzteZeile = .Range("B" & .Rows.Count).End(xlUp).Row
    Set Bereich = .Range("B7:C" & letzteZeile)
  End With
  
  'On Error Resume Next
  
  With Ziel
    For i = 7 To .Range("C" & .Rows.Count).End(xlUp).Row
      .Range("C" & i).Value = WsF.VLookup(.Range("B" & i).Value, Bereich, 2, False)
    Next i
  End With
  
  On Error GoTo 0
End If

Set Datenbasis = Nothing
Set Ziel = Nothing
Set Bereich = Nothing
Set WsF = Nothing
End Sub

Gruß Sepp

Anzeige
Und wenn 'Ziel' die Tabelle ist...
12.01.2018 10:36:37
Sepp
... in der der Code läuft, brauchst du es nicht extra definieren, denn der Code bezieht sich immer auf das Sheet auf dem der Doppelklick erfolgt!
' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim i As Long, letzteZeile As Long
Dim Datenbasis As Worksheet
Dim Bereich As Range
Dim WsF As WorksheetFunction

If Target.Address = "$B$7" Then
  Cancel = True
  Set Datenbasis = Worksheets("Basic Data")
  Set WsF = Application.WorksheetFunction
  
  With Datenbasis
    letzteZeile = .Range("B" & .Rows.Count).End(xlUp).Row
    Set Bereich = .Range("B7:C" & letzteZeile)
  End With
  
  'On Error Resume Next
  
  For i = 7 To Range("C" & Rows.Count).End(xlUp).Row
    Range("C" & i).Value = WsF.VLookup(Range("B" & i).Value, Bereich, 2, False)
  Next i
  
  On Error GoTo 0
End If

Set Datenbasis = Nothing
Set Bereich = Nothing
Set WsF = Nothing
End Sub

Gruß Sepp

Anzeige
AW: Und wenn 'Ziel' die Tabelle ist...
12.01.2018 10:53:35
Matthias
Hallo Sepp,
vielen Dank schon mal für die Hilfe. habe gerade mal meinen Code auskommentiert und Deinen verwendet - den ersten, mit der abgeschalteten Error Version. Die Funktion läuft zwar auf dem Sales Sheet, danach bekomme ich allerdings einen Laufzeitfehler 1004 "Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden."
Ich sehe mir jetzt Dein weiteres Code Beispiel mal genauer an.
Mit Deklarationen meinst Du das, was Du am Beginn auskommentiert hattest - richtig?
Lieben Dank nochmal!
matthias
AW: VLOOKUP funktioniert nur auf dem ersten Sheet
12.01.2018 11:05:55
fcs
Hallo Mathias,
ich vermute: auf den anderen Blättern als "Sales" stehen in Spalte C noch keine Daten.
Du muss in Zeile
For i = 7 To Ziel.Range("C" & Rows.Count).End(xlUp).Row

Das "C" durch "B" ersetzen.
Weitere Möglichkeit:
Auf den anderen Blättern passt der Datentyp in Spalte B nicht zum Datentyp im Blatt "Basic Data" Spalte B. z.B. ineinemder Blätter stehen Zahlen als Text imanderenals richtige Zahlen.
Ich würde hier das Makro wie folgt aufbauen:
'Makro unter Tabellenblatt-Code-Modul
Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Address = "$B$7" Then
Dim i As Long, letzteZeile As Long
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet
Dim Bereich As Range
Dim varWert
Set Arbeitsmappe = ThisWorkbook
Set Datenbasis = Arbeitsmappe.Worksheets("Basic Data")
Cancel = True
With Datenbasis
letzteZeile = .Cells(.Rows.Count, 2).End(xlUp).Row 'in Spalte B
Set Bereich = .Range(.Cells(7, 2), .Cells(letzteZeile, 3)) 'Spalten B:C
End With
With Me '= Stellvertreter für das Tabellenblatt in dessen Code-Modul der Code steht
letzteZeile = .Cells(.Rows.Count, 2).End(xlUp).Row ' '"C" in "B" geändert
'alte Werte in Spalte C löschen
.Range(.Cells(7, 3), .Cells(letzteZeile, 3)).ClearContents
For i = 7 To letzteZeile
varWert = Application.VLookup(.Cells(i, 2).Value, Bereich, 2, False)
If IsError(varWert) Then
.Cells(i, 3).Value = "#NV" 'oder Zeile weglassen
Else
.Cells(i, 3).Value = varWert
End If
Next i
End With
End If
End Sub
Wenn der gleiche Code in vielen gleich aufgebauten Tabellenblättern bei Doppelklich in B7 ausgeführt werden soll, dann kann man das Makro in den Tabellenblättern weglassen und unter "DieseArbeitsmappe" wie folgt einbauen:
'Makro unter Arbeitsmappen-Code-Modul
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel  _
As Boolean)
Select Case Sh.Name
Case "Basic Data", "Tabelle XYZ"
'in diesen Blättern nichts machen
Case Else
If Target.Address = "$B$7" Then
Dim i As Long, letzteZeile As Long
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet
Dim Bereich As Range
Dim varWert
Set Arbeitsmappe = ThisWorkbook
Set Datenbasis = Arbeitsmappe.Worksheets("Basic Data")
Cancel = True
With Datenbasis
letzteZeile = .Cells(.Rows.Count, 2).End(xlUp).Row 'in Spalte B
Set Bereich = .Range(.Cells(7, 2), .Cells(letzteZeile, 3)) 'Spalten B:C
End With
With Sh '= Stellvertreter für das Blatt in dem Doppelgeklickt wurde
letzteZeile = .Cells(.Rows.Count, 2).End(xlUp).Row ' '"C" in "B" geändert
'alte Werte in Spalte C löschen
.Range(.Cells(7, 3), .Cells(letzteZeile, 3)).ClearContents
For i = 7 To letzteZeile
varWert = Application.VLookup(.Cells(i, 2).Value, Bereich, 2, False)
If IsError(varWert) Then
.Cells(i, 3).Value = "#NV" 'oder Zeile weglassen
Else
.Cells(i, 3).Value = varWert
End If
Next i
End With
End If
End Select
End Sub
Gruß
Franz
Anzeige
AW: VLOOKUP funktioniert nur auf dem ersten Sheet
12.01.2018 11:27:30
Matthias
Hallo Franz,
allerbesten Dank für Deine Mühe! Ich habe das "C" durch das "B" ersetzt - und nun funktioniert der Code auch in den anderen Sheets, diesmal sogar ohne Laufzeitfehler und mit auskommentiertem "On Error Resume Next" ;-).
Es lag ganz offensichtlich daran, das "C" durch das "B" zu ersetzen. Das sehe ich mir nun noch mal ganz in Ruhe an, warum ich darauf nicht selbst kommen konnte. Die Idee mit in "DieseArbeitsmappe" hatte ich auch schon versucht - aber dann mangels Erfolg in den nicht funktionierenden Sheets verworfen.
Ganz herzlichen Dank an Dich Franz und natürlich auch an Sepp für diese wirklich schnelle und kompetente Hilfestellung. Ihr habt mir damit sehr weitergeholfen und mein Wochenende gerettet!!!
Habe mir extra das Excel VBA Kompendium von Bernd Held gebraucht zugelegt, um etwas tiefer in die Materie einzusteigen, ist soweit auch ganz brauchbar - aber was hier in diesem Forum für geballtes Fachwissen zu finden ist, das ist echt der Hammer.
Nochmals, allerbesten dank für Eure unbürokratische Hilfe - ist nicht selbstverständlich heutzutage!
Ein schönes Wochenende wünscht Euch
Matthias
Anzeige

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige