PDA

Vollständige Version anzeigen : sverweis - Laufzeitfehler 1004


flumum
16.07.2014, 13:39
Hallo zusammen,

ich versuche ein Makro zu schreiben, das mir einen sverweis automatisiert. Aber bei der Ausführung bekomme ich immer den Laufzeitfehler 1004. Als Anfänger bin ich am Ende meiner Überlegungen, warum der Code nicht läuft. :(

Ich würde mich freuen, wenn mir jemand einen Tipp geben kann. :wisper: :boah:
Die entsprechenden Codezeilen hab ich rot markiert.

Sub Globus_Sverweis()

Dim Spalte_gefunden As Range 'Tabelle 2
Dim Splatenname As String 'Tabelle 2
Dim a As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2
Dim b As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2
Dim c As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2
Dim d As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2
Dim e As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2
Dim f As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2

Dim Letzte_Zeile As Integer 'Tabelle 2
Dim k As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2
Dim l As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim m As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim n As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 2

Dim p As Integer 'Berechnung spaltenindex für sverweis

Dim LetzteSpalte As Integer 'Tabelle 1
Dim LetzteZeile As Integer 'Tabelle 1
Dim u As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim v As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim w As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim x As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim y As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1
Dim z As Integer 'Übergabe Spaltenzahl an Variable ,Tabelle 1


'----->Suche Spaltenname und weise Spaltennummer einer Variablen zu

'Tabellenblatt 2 aktiv setzen
Worksheets("2").Activate

'Ladungsträger (im GP enthalten) in Abschluss Währung
Spaltenname = "Ladungsträger (im GP enthalten) in Abschluss Währung"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
a = Spalte_gefunden.Column
' MsgBox a
Else
MsgBox "Nichts gefunden!"
End If

'Transportkosten bis Auslieferort (im GP enthalten) in Abschluss Währung
Spaltenname = "Transportkosten bis Auslieferort (im GP enthalten) in Abschluss Währung"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
b = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

'Transportkosten bis Verbrauchswerk (im GP enthalten) in Abschluss Währung
Spaltenname = "Transportkosten bis Verbrauchswerk (im GP enthalten) in Abschluss Währung"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
c = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

'Verpackungskosten (im GP enthalten) in Abschluss Währung
Spaltenname = "Verpackungskosten (im GP enthalten) in Abschluss Währung"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
d = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

'JIS / JIT (im GP enthalten) in Abschluss Währung
Spaltenname = "JIS / JIT (im GP enthalten) in Abschluss Währung"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
e = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

'Handling (im GP enthalten) in Abschluss Währung
Spaltenname = "Handling (im GP enthalten) in Abschluss Währung"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
f = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

'SNR Tabelle 2
Spaltenname = "SNR"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
n = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

Letzte_Zeile = Cells.SpecialCells(xlCellTypeLastCell).Row
k = Letzte_Zeile
' MsgBox Letzte_Zeile


'----->Sverweis in Tabellenblatt 1 ausführen

'Tabellenblatt 1 aktiv setzen
Worksheets("1").Activate 'Arbeitsblatt Aktiv setzen

'Ladungsträger (im GP enthalten) in Abschluss Währung
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, LetzteSpalte) = "Ladungsträger (im GP enthalten) in Abschluss Währung"
u = LetzteSpalte
' MsgBox u

'Transportkosten bis Auslieferort (im GP enthalten) in Abschluss Währung
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, LetzteSpalte) = "Transportkosten bis Auslieferort (im GP enthalten) in Abschluss Währung"
v = LetzteSpalte

'Transportkosten bis Verbrauchswerk (im GP enthalten) in Abschluss Währung
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, LetzteSpalte) = "Transportkosten bis Verbrauchswerk (im GP enthalten) in Abschluss Währung"
w = LetzteSpalte

'Verpackungskosten (im GP enthalten) in Abschluss Währung
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, LetzteSpalte) = "Verpackungskosten (im GP enthalten) in Abschluss Währung"
x = LetzteSpalte

'JIS / JIT (im GP enthalten) in Abschluss Währung
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, LetzteSpalte) = "JIS / JIT (im GP enthalten) in Abschluss Währung"
y = LetzteSpalte

'Handling (im GP enthalten) in Abschluss Währung
LetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, LetzteSpalte) = "Handling (im GP enthalten) in Abschluss Währung"
z = LetzteSpalte

'Sachnummer Tabelle 1
Spaltenname = "Sachnummer"
Set Spalte_gefunden = Rows(1).Find(What:=Spaltenname, LookIn:=xlValues, LookAt:=xlWhole)
If Not Spalte_gefunden Is Nothing Then
m = Spalte_gefunden.Column
Else
MsgBox "Nichts gefunden!"
End If

LetzteZeile = Cells.SpecialCells(xlCellTypeLastCell).Row
l = LetzteZeile


' a-f = Spalten Tabelle 1, wo sverweis abgerufen wird
' k = letzte Zeile Tabelle 2
' l = letzte Zeile Tabelle 1
' m = Spalte Tabelle 1, in der die SNR steht
' n = Spalte Tabelle 2, in der die SNR steht
' u-z = Spalten Tabelle 1, wo sverweis eingetragen wird


'Spaltenindex für sverweis errechnen
p = a - n + 1
' MsgBox p

i = 2
Cells(i, u).Select
For i = 2 To l - 1

ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(i, m).Value, Worksheets("2").Cells(k, n), p, False)
ActiveCell.Offset(1, 0).Select
Next i

End Sub




Für jede Lösung bin ich dankbar.

Viele grüße

GMG-CC
16.07.2014, 13:53
Moin,

dein Ansatz für das debuging ist sehr gut. Per MsgBox (oder auch anders) den Inhalt der Variablen ausgeben. Ähnlich mache ich das auch.

Allerdings hilft mir das hier nicht wirklich viel, weil ich die Werte nicht sehe. Vorschlag: Stelle die Mappe mit anonymisierten Werten hier ein, dann lässt sich das besser nachvollziehen. Zumal mich stutzig macht, dass du per SVERWEIS offensichtlich nur in worksheet "2", Zeile k, Spalte n suchst, was IMHO wenig Sinn macht ...

Hasso
16.07.2014, 13:53
Hallo flumum,

Application.WorksheetFunction.VLookup(Cells(i, m).Value, Worksheets("2").Cells(k, n), p, False)
ActiveCell.Offset(1, 0).Select

An dieser Stelle erwartet VLookup eine Matrix, in der der Wert aus Cells(i, m) gefunden werden soll, du gibst aber hier nur eine Zelle an. Das kann nicht funktionieren!

EarlFred
16.07.2014, 14:09
Hallo grüße,

lass mal folgendes weg:
- Select, ActiveCell.Offset(1,0).Select...
- Worksheetfunction

' i = 2
With Worksheets("Nameeintragen!")
For i = 2 To l - 1
.Cells(i, u).Value = Application.VLookup(.Cells(i, m).Value, Worksheets("2").Cells(k, n), p, False)
Next i
End With
Ich nehme an, Du erhältst viele #NV-Fehler.

Nebenbei:
Worksheets("2") -> Arbeitsblatt mit dem Namen "2". Das kann an 18. Stelle stehen
Worksheets(2) -> 2. Arbeitsblatt in der Mappe. Das kann auch "200" oder "Hubert" heißen.

In Deinem Sverweis wird übrigens nur eine einzige Zelle durchsucht. Auch gewollt? Ein Spaltenindex von p> 1 führt also auch zum Fehler.

Grüße
EarlFred

flumum
16.07.2014, 14:10
Hallo Hasso,

vielen Dank für die schnelle Antwort.
Und ja, du hast recht.

Die Matrix soll wie folgt sein: cells(i,n):Cells(k, f).
Aber ich habe das Gefühl, dass ich nicht die richtige Syntax verwende. Leider kann ich keine festen Spalten und Zeilen mit "range" angeben. Die können jedesmal variieren.

Es erscheint immer: Fehler beim Kompilieren.

ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(i, m).Value, Worksheets("2").cells(i,n):Cells(k, f), p, False)


Viele Grüße
Anja

Hasso
16.07.2014, 14:14
Hallo Flumum,ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(i, m).Value, Worksheets("2").Range(cells(i,n),Cells(k, f)), p, False)

Mc Santa
16.07.2014, 14:14
Hallo,

nutze es so:
Worksheets("2").Range(Worksheets("2").Cells(i,n), Worksheets("2").Cells(k, f))

Und auch bei Cells(i, m) solltest du das Tabellenblatt dazu angeben.

Hilft dir das weiter?
VG

@Hasso: auch die Cells innerhalb der Range sollten sich auf das Tabellenblatt beziehen.

EarlFred
16.07.2014, 14:16
Hallo Anja,

oder
Worksheets("2").Cells(i, n).Resize(k - i + 1, f - n + 1)

Grüße
EarlFred

Hasso
16.07.2014, 14:18
Hallo McSanta,@Hasso: auch die Cells innerhalb der Range sollten sich auf das Tabellenblatt beziehen.Du hast Recht - Asche auf mein Haupt!

flumum
18.07.2014, 07:42
Hallo zusammen,

vielen lieben Dank für all eure Vorschläge. Aber irgendwie komme ich dennoch nicht weiter.

Habe mal die unterschiedlichen Lösungen ausprobiert.

Mein momentaner Stand sieht wie folgt aus.

'Spaltenindex für sverweis errechnen
p = a - n + 1

MsgBox a '21
MsgBox k '9799
MsgBox l '21770
MsgBox m '2
MsgBox n '5
MsgBox p '17
MsgBox u '31


i = 2
Cells(i, u).Select

With Worksheets("1")

For i = 2 To l - 1
.Cells(i, u).Value = Application.WorksheetFunction.VLookup(Cells(i, m).Value, Worksheets("2").Range(Worksheets("2").Cells(i, n), Worksheets("2").Cells(k, f)), p, False)
Next i
End With

Bekomme aber immer noch den Laufzeitfehler 1004: Die VLookup-Eigenschaft des WorksheetFunction-Obkektes kann nicht zugeordnet werden.

Bin für jeden Rat dankbar.

EarlFred
18.07.2014, 07:51
Hallo Name?,

Habe mal die unterschiedlichen Lösungen ausprobiert.
warum setzt Du die Ideen aus Beitrag #4 nicht um?

Und wenn Du weitere Hilfe benötigst, stell bitte eine lauffähige Mustermappe (ohne sensible Daten) ein. Ich habe wenig Lust, mir für jeden neuen Test nach 10 Wochen Reaktionszeit Deinerseits eine neue Arbeitsumgebung mit Deinen 20000 verwendeten Variablen zu schaffen.

Grüße
EarlFred

flumum
18.07.2014, 09:16
Hi EarlFred,

danke nochmal für den "netten" Hinweis. Bin dem Beitrag #4 gefolgt.

Leider schreibe ich während meiner Arbeitszeit, die mich entsprechend fordert, nur sehr selten Makros und muss mich immer wieder rantasten. Sorry das ich nicht in sekundenschnelle geantwortet habe.

Das Makro läuft. Danke an alle. Der Fehler 1004 lag dann aber doch an etwas anderem, was mir beim Testen erst aufgefallen ist, da ich die Datengrundlage selber nicht kenne.

Viele Grüße
Anja