Office: VLookup/Sverweis mit Variablen/Range

jaffacake93 · 18. März 2017

Hallo zusammen,

ich lese jetzt seit ein paar Wochen hier passiv mit, und habe nebenbei an einem kleinen Automatisierungsprojekt für die Arbeit gearbeitet. Soweit funktioniert alles ganz gut, nur stoße ich nun auf ein Problem mit dem Sverweis bzw. der WorksheetFunction VLookup...

Mein Projekt in kurz:

Stand aktuell (ohne Automatisierung):
Es gibt eine Mastertabelle mit den Umsätzen der Kunden, welche von 2013-heute die Monatlichen Umsätze in einer jeweiligen Spalte ausgibt.

Es gibt einen Report, mit welchem momentan die Monatsumsätze über einen Sverweis eingespeist werden. Da die Umsätze jeweils auf Länder und ferner Produktkategorien arbeiten, haben wir eine Hilfszeile in beiden Tabellen, welche zur Verwendung des Sverweis dient.

Dieser Prozess kostet im Moment relativ viel Zeit, daher habe ich bis dato folgendes programmiert:

Ein Userform, welches das Verzeichnis des gespeicherten Monatsreports aufruft diesen Report anschließend öffnet und "Sverweis bereit" macht.

In dem Userform legt man schließlich noch Monat und Jahr fest um die richtige Spalte der Tabelle zu bearbeiten.

Folgend mein Code zum Sverweis:

1. Ye und Mo werden aus zwei Comboboxen ausgelesen
2. Gedanke bei SuchKr war die Anfangszelle für das Suchkriterium als Range Variable zu speichern
3.Selbiges für die Matrix nur hier eben die Ganze Tabelle
4. zwei Range Variablen werden zum bestimmen der Spalte für Ye und Mo definiert und gesucht
5. Ein Loop welcher den Sverweis durchführen soll. Until -> Zelle is Hellblau, dies ist die Zelle in dem alle Länder aufsummiert werden. Wenn Zelle eine Formel hat, wird die Zelle ignoriert (es gibt unterzeilen, welche ein Land aufsummieren). Dann der Sverweis über Vlookup.

Code:

Private Sub CommandButton2_Click()

'-----------Obtains the Value for the Variables Month and Year

Ye = Jahr.Value
Mo = Monat.Value

If Mo = "März" Then
Mo = "Mrz"

Else
Mo = Left(Mo, 3)
End If

Dim SuchKr As Range '--------New Range Variable for the Sverweis
Set SuchKr = ActiveSheet.Range("BU6") '--------Setting the beginning Range for the Sverweis if Columns are added this needs to be adjusted

Dim Matrix As Variant
Workbooks(2).Activate
Set Matrix = Cells.Range("A:Z")
Workbooks(1).Activate

'-----------Finds the Cells that contain the Value for Year and Month

Dim A As Integer
Dim ra As Range
Dim ra2 As Range
Dim Test As String

Set ra = ActiveSheet.Range("A2:BS5").Find(Ye)
If ra Is Nothing Then
MsgBox "Nichts gefunden"
Exit Sub
End If

MsgBox ra.Address

Set ra2 = ra.MergeArea.EntireColumn.Find(Mo)
If ra2 Is Nothing Then
MsgBox "Nichts gefunden"
Exit Sub
End If

'----------Runs the Sverweis until a Cell is filled with Light Blue (this is the Cell which has the Total Sum of all SubSums)

A = 0

Do Until Selection.Interior.Color = RGB(0, 160, 226) '------Condition for Loop

ra2 = ra2.Offset(A, 0).Select '-------Selected Cell is Offset by A in order to "run" through the cells
SuchKr = SuchKr.Offset(A, 0)

If Selection.HasFormula Then '-----Additional Condition: if cell contains a formula, then it will just keep the value

Selection.FormulaLocal = Selection.FormulaLocal
Else

Selection.Value = WorksheetFunction.VLookup(SuchKr, Matrix, 6, 0)

'------here is the Sverweis run, in case any changes need to be done"

End If
A = A + 1
Loop

ra2.Value = Mo '-------unless you re-assign the value for Mo the Cell which contained the Month will contain the Statement "Wahr"

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Habt ihr Ideen, wie ich die Variablen in den Sverweis integrieren kann, ohne Objektdefinierte Fehler zurückzubekommen?

Habe es auch schon mit FormulaR1C1Local und FormulaLocal probiert.

Vielen Dank im Voraus! *Smilie

:)

Office: VLookup/Sverweis mit Variablen/Range

VLookup/Sverweis mit Variablen/Range

VLookup/Sverweis mit Variablen/Range

VLookup/Sverweis mit Variablen/Range - Similar Threads - VLookup Sverweis Variablen

VLOOKUP-Funktionalität in Power Pivot

vlookup macht probleme

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

VBA: vlookup in einer Schleife funktioniert nicht

VLookup mit zwei Kriterien

Problem bei Excel - VBA Makro SVERWEIS funktioniert nicht!

VBA VLookup in geschlossene Datei