Sverweis Formulalocal und Variable

Forumthread: Sverweis Formulalocal und Variable

25.06.2018 10:30:34

Bastian

Hallo zusammen,
ich stehe vor einer Aufgabe, die ich nicht bewältigt bekommen.
Ich habe eine Bestandstabelle mit fast 20000 Einträgen, ein Arbeitsblatt, welches ab Zeile 25 Daten per SVerweis aus der Bestandstabelle einpflegt. Diese werden aber vorab gefiltert, sodass nur jeweils 50 - 70 Einträge bearbeitet werden. Es funktioniert, bis auf den Sverweis.
Ich schaffe es aber nicht eine Variable in den SVerweis zu bringen.
Hier mein Beispiel natürlich ohne den Zähler eingesetzt. Es soll A12 durch A & i ersetzt werden, _
mir fehlt dazu aber die Syntax:


Public Sub getdata()
Dim last As Integer
Dim i As Integer
last = ThisWorkbook.Sheets("Datenbestand").Cells(20000, 1).End(xlUp).Row
On Error Resume Next
For i = 25 To last
If Sheets("Arbeitsblatt").Rows(i).Hidden = False Then
ThisWorkbook.Sheets("Arbeitsblatt").Range("I" & i).FormulaLocal = "=SVERWEIS( _
A12;Datenbestand!A:L;11;FALSCH)" 'Alternativ über VLookup, bringt aber auch ohne Zähler    _
nur eine leere Zeile
' ThisWorkbook.Sheets("Arbeitsblatt").Range("I" & i) = Worksheet.Function. _
VLookup([a12], Sheets("Datenbestand").[A:L], 11, False)
End If
Next i
End Sub

Vielen Dank im Voraus.

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Sverweis Formulalocal und Variable

25.06.2018 11:01:46

Daniel
AW: Sverweis Formulalocal und Variable

26.06.2018 12:02:05

Bastian
AW: Sverweis Formulalocal und Variable

26.06.2018 12:29:51

Daniel

AW: Sverweis Formulalocal und Variable

25.06.2018 11:01:46

Daniel

Hi
solltest du die letzte Zeile für die Formel nicht besser im Blatt "Arbeitsblatt" ermitteln?
beim Eintragen der Formel kann man sich das Einfügen der Variablen sehr oft ersparen, wenn man auf die Z1S1-Schreibweise für Zellbezüge zurückgreift.
außerdem sollte man ausnutzen, dann man die Formeln in alle betroffenen Zellen in einem Schritt eintragen kann und es nicht notwendig ist, per Schleife jede Zelle einzeln zu befüllen.

dim last as long
with Thisworkbook.Sheets("Arbeitsblatt")
last = .Cells(20000, 1).End(xlUp).Row
.Range("I25:I" & last).SpecialCells(xlcelltypevisible).FormulaR1C1Local = _
"=SVerweis(ZS1;Datenbestand!S1:S12;11;FALSCH)"
end with

wenn du die Werte als "harte" Werte eintragen willst, dann so.
die []-Schreibweise für Zellbezüge sieht zwar elegant aus, ist aber unpraktisch, weil sie keine Verwendung von Variablen zulässt. Besser es ist, Einzelzellen mit Cells() und Zellbereiche mit Range() anzusprechen:

...
With thisworkbook.Sheets("Arbeitsblatt")
on error resume next
for i = 25 to last
If Sheets("Arbeitsblatt").Rows(i).Hidden = False Then
.Cells(i, 9).value = worksheetfunction.VlookUp(.Cells(i, 1), _
ThisWorkbook.Sheets("Datenbestand").Range("A:L"), 11, False)
end if
next
end with
on error goto 0

Gruß Daniel

AW: Sverweis Formulalocal und Variable

26.06.2018 12:02:05

Bastian

Danke Daniel,
aber leider führen beide Varianten nicht zum Erfolg.
Die erste Variante mit FormulaR1C1... zwingt meinen PC in die Knie. Beide Kerne sind zu ca. 50 % Ausgelastet und nach ungefähr einer Stunde habe ich es abgebrochen. Er hat zwar einige Zeilen bearbeitet, auch richtig, aber leider nicht praxistauglich.
Und die zweite Variante schreibt einfach nichts in die Zelle.
MfG

AW: Sverweis Formulalocal und Variable

26.06.2018 12:29:51

Daniel

Hi
Um dir da weiterhelfen zu können, müsste ich deine Dateien, deren Aufbau und den Code besser kennen.
Wieviele Formeln müssen denn eingetragen werden und wie groß ist der Datenbereich des Sverweises?
Bei großen Datenmengen kann es sinnvoll sein, die Sverweistabelle nach der ersten Spalte zu sortieren und den SVerweis mit 4. Parameter = Wahr zu verwenden. Der ist deutlich schneller.
Gruß Daniel

Infobox / Tutorial

SVerweis in Excel VBA mit Variablen erfolgreich nutzen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um den SVerweis in Excel VBA mit Variablen erfolgreich zu implementieren, befolge diese Schritte:

Öffne den VBA-Editor:
- Drücke ALT + F11, um den VBA-Editor zu öffnen.
Erstelle ein neues Modul:
- Klicke im Menü auf Einfügen > Modul.

Füge den folgenden Code ein:

Public Sub getdata()
   Dim last As Long
   Dim i As Long
   With ThisWorkbook.Sheets("Datenbestand")
       last = .Cells(.Rows.Count, 1).End(xlUp).Row
   End With
   With ThisWorkbook.Sheets("Arbeitsblatt")
       For i = 25 To last
           If .Rows(i).Hidden = False Then
               .Cells(i, 9).FormulaLocal = "=SVERWEIS(A" & i & ";Datenbestand!A:L;11;FALSCH)"
           End If
       Next i
   End With
End Sub

Ersetze die Formel nach Bedarf:
- Du kannst die Formel anpassen, um die gewünschten Spalten oder Werte zu nutzen, indem du die Parameter der Funktion veränderst.
Führe das Makro aus:
- Drücke F5 oder wähle Run > Run Sub/UserForm, um das Makro auszuführen.

Häufige Fehler und Lösungen

Formel wird nicht eingetragen:
- Überprüfe, ob die Zellen im Arbeitsblatt sichtbar sind. Unsichtbare Zellen werden nicht befüllt.
Leere Zellen nach Ausführung:
- Stelle sicher, dass die Suchkriterien im SVerweis korrekt sind und dass die Daten in der „Datenbestand“-Tabelle vollständig sind.
Leistungseinbußen bei großen Datenmengen:
- Versuche, die Daten in der „Datenbestand“-Tabelle nach der ersten Spalte zu sortieren und den vierten Parameter des SVerweises auf Wahr zu setzen, um die Geschwindigkeit zu erhöhen.

Alternative Methoden

Verwendung von FormulaR1C1Local:

With ThisWorkbook.Sheets("Arbeitsblatt")
   .Range("I25:I" & last).FormulaR1C1Local = "=SVERWEIS(RC[-8];Datenbestand!R1C1:R10000C12;11;FALSCH)"
End With

Diese Methode kann schneller sein, erfordert jedoch ein gutes Verständnis der R1C1-Notation.

Direkte Werte eintragen: Wenn du nur die Werte ohne die Formel einfügen möchtest, kannst du den SVerweis direkt in der Zelle ausführen:
```
.Cells(i, 9).Value = WorksheetFunction.VLookup(.Cells(i, 1), ThisWorkbook.Sheets("Datenbestand").Range("A:L"), 11, False)
```

Praktische Beispiele

Einfacher SVerweis mit Variable:
```
.Cells(i, 9).FormulaLocal = "=SVERWEIS(A" & i & ";Datenbestand!A:L;11;FALSCH)"
```
Hier wird die Zeilennummer dynamisch in die Formel eingefügt.

SVerweis über mehrere Zeilen:

With ThisWorkbook.Sheets("Arbeitsblatt")
   .Range("I25:I" & last).FormulaLocal = "=SVERWEIS(A25;Datenbestand!A:L;11;FALSCH)"
End With

Tipps für Profis

Nutze Application.ScreenUpdating = False zu Beginn deines Codes, um die Aktualisierung des Bildschirms zu deaktivieren und die Leistung zu verbessern. Vergiss nicht, es am Ende wieder auf True zu setzen.
Variablen für häufig genutzte Werte: Speichere oft genutzte Werte oder Bereiche in Variablen, um den Code lesbarer und schneller zu machen.
Vermeide die Verwendung von On Error Resume Next, da dies potenzielle Fehler im Code verbergen kann. Stattdessen solltest du spezifische Fehlerbehandlungsroutinen implementieren.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass der SVerweis bei großen Datenmengen schnell ist?

Sortiere die Daten in der SVerweistabelle nach der ersten Spalte und verwende Wahr als vierten Parameter.

2. Warum funktioniert mein SVerweis nicht?

Überprüfe, ob die Suchkriterien korrekt sind und dass die Daten im „Datenbestand“ vorhanden sind. Achte auch darauf, dass du keine unsichtbaren Zeilen bearbeitest.

Beliebteste Forumthreads (12 Monate)

Sverweis Formulalocal und Variable

Forumthread: Sverweis Formulalocal und Variable

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

SVerweis in Excel VBA mit Variablen erfolgreich nutzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread