Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > festes Tabellenende durch loLetzte ersetzen in VBA-Formel
Normale Version: festes Tabellenende durch loLetzte ersetzen in VBA-Formel
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2 3

BoskoBiati

17.03.2017, 12:18
Hallo Ralph,

warum läßt Du den SVERWEIS nicht über die ganze Spalte laufen? Da offensichtlich nach der letzten Zeile keine weiteren Einträge folgen, würde das auch nicht soviel ausmachen.

Ich würde das Ganze etwas verkürzen:


Code:
arRange = Array("C8", "E8", "G8", "C9", "E9", "G9", "I9", "C10", "E10", "G10", "I10", "K10", "C12", "E12", "G12", "I12", "K12", "C14", "E14", "G14", "I14", "K14", "C15", "C16", "E16", "G16", "I16", "C18", "E18", "G18", "I18", "K18", "C19", "E19", "G19", "C21", "E21", "G21", "C23", "E23", "G23", "I23", "C24", "E24", "G24", "I24", "K24")
For loa = LBound(arRange) To UBound(arRange)
    .Range(arRange(loa)) = WorksheetFunction.VLookup(Range("E7"), Worksheets("Datenbank").Range("C:AY"), loa + 2, 0)
Next
.Range("I24") = ""

snb

17.03.2017, 12:57
Warum keine Beispieldatei hochgeladen ?

Rabe

17.03.2017, 14:02
(17.03.2017, 12:18)BoskoBiati schrieb: [ -> ]warum läßt Du den SVERWEIS nicht über die ganze Spalte laufen? Da offensichtlich nach der letzten Zeile keine weiteren Einträge folgen, würde das auch nicht soviel ausmachen.

Ich würde das Ganze etwas verkürzen:

ich dachte, wenn ich das über die ganzen Spalten laufen lasse, wird das vielleicht auf die Performance gehen.

Leider sind die Zellen in "Eingabe_ELC" nicht in derselben Reihenfolge, wie die Spalten in der Datenbank.

BoskoBiati

17.03.2017, 15:05
Hi,


da Excel die Spalte von oben nach unten absucht und beim ersten Treffer aufhört, ist es nur ein Problem, wenn der Suchbegriff nicht vorhanden ist.
Die Reihenfolge der Zellen im Array entspricht der Anordnung in Deiner Tabelle, d.h. das, was Du an Zellbezeichnungen untereinander in Deinen Formeln aufgelistet hast, habe ich hintereinander im Array aufgelistet. Nur bei I24 musst Du am Ende den Wert noch löschen, da Du ihn ja nicht drin haben willst.

Rabe

17.03.2017, 16:00
Hi Edgar,

(17.03.2017, 15:05)BoskoBiati schrieb: [ -> ]Die Reihenfolge der Zellen im Array entspricht der Anordnung in Deiner Tabelle, d.h. das, was Du an Zellbezeichnungen untereinander in Deinen Formeln aufgelistet hast, habe ich hintereinander im Array aufgelistet.

das heißt, ich müßte im Array die Reihenfolge so einstellen, wie die Spalten im Datenbankblatt sind, dann werden die Werte in die richtigen Zellen übernommen.

Werde ich Montag mal testen.

BoskoBiati

17.03.2017, 17:27
Hi Ralf,


Zitat:ich müßte im Array die Reihenfolge so einstellen, wie die Spalten im Datenbankblatt sind

nein, Du mußt jede Zelle in der Reihenfolge anordnen, wie sie gefüllt werden soll. Ich habe etwas bei Dir übersehen, hier der korrigierte Code:


Code:
Dim arRange As Variant
Dim loa As Long

arRange = Array("C8", "E8", "G8", "C9", "E9", "G9", "I9", "C10", "E10", "G10", "I10", "K10", "C12", "E12", "G12", "I12", "K12", "C14", "E14", "G14", "I14", "K14", "C15", "C16", "E16", "G16", "I16", "C18", "E18", "G18", "I18", "K18", "C19", "C21", "E21", "C23", "E23", "G23", "I23", "C24", "E24", "E19", "I24", "G24", "G21", "K24", "I24", "G19")
For loa = LBound(arRange) To UBound(arRange)
    Range(arRange(loa)) = WorksheetFunction.VLookup(Range("E7"), Worksheets("Datenbank").Range("C:AY"), loa + 2, 0)
Next
Range("I24") = ""

Hier als Datei mit beiden Varianten:

[attachment=10278]

atilla

17.03.2017, 18:20
Hallo zusammen,

Edgar, Ralfs anfänglicher Code und Deine Variante unterscheiden sich lediglich am Schreibaufwand, von der Performance sind sie gleich.
Wenn es um Performance geht, dann würde ich immer noch meine Variante nehmen.

Die kann natürlich auch noch auf Deine gezeigte Weise zusammengezogen werden.
Aber hier finde ich es übersichtlicher die Zuordnungen in einzelnen Zeilen zu schreiben.
Man kann dann auch hinter jede Zeile mit einem Kommentar die Zuordnung kennzeichnen.

atilla

17.03.2017, 18:56
Hallo Edgar,

Du hast immer noch falsche Bezüge:

Arbeitsblatt mit dem Namen 'Tabelle3'
 ABCDEFGHI
1  Spalte QuelleZelle Ziel  im Code von Edgar  
2.Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 2, 0) 2C81 C8WAHR1
3.Range("E8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 3, 0) 3E81 E8WAHR1
4.Range("G8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 4, 0) 4G81 G8WAHR1
5.Range("C9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 5, 0) 5C91 C9WAHR1
6.Range("E9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 6, 0) 6E91 E9WAHR1
7.Range("G9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 7, 0) 7G91 G9WAHR1
8.Range("I9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 8, 0) 8I91 I9WAHR1
9.Range("C10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 9, 0) 9C101 C10WAHR1
10.Range("E10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 10, 0) 10E101 E10WAHR1
11.Range("G10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 11, 0) 11G101 G10WAHR1
12.Range("I10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 12, 0) 12I101 I10WAHR1
13.Range("K10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 13, 0) 13K101 K10WAHR1
14.Range("C12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 14, 0) 14C121 C12WAHR1
15.Range("E12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 15, 0) 15E121 E12WAHR1
16.Range("G12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 16, 0) 16G121 G12WAHR1
17.Range("I12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 17, 0) 17I121 I12WAHR1
18.Range("K12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 18, 0) 18K121 K12WAHR1
19.Range("C14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 19, 0) 19C141 C14WAHR1
20.Range("E14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 20, 0) 20E141 E14WAHR1
21.Range("G14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 21, 0) 21G141 G14WAHR1
22.Range("I14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 22, 0) 22I141 I14WAHR1
23.Range("K14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 23, 0) 23K141 K14WAHR1
24.Range("C15").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 24, 0) 24C151 C15WAHR1
25.Range("C16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 25, 0) 25C161 C16WAHR1
26.Range("E16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 26, 0) 26E161 E16WAHR1
27.Range("G16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 27, 0) 27G161 G16WAHR1
28.Range("I16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 28, 0) 28I161 I16WAHR1
29.Range("C18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 29, 0) 29C181 C18WAHR1
30.Range("E18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 30, 0) 30E181 E18WAHR1
31.Range("G18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 31, 0) 31G181 G18WAHR1
32.Range("I18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 32, 0) 32I181 I18WAHR1
33.Range("K18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 33, 0) 33K181 K18WAHR1
34.Range("C19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 34, 0) 34C191 C19WAHR1
35.Range("C21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 35, 0) 35C211 C21WAHR1
36.Range("E21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 36, 0) 36E211 E21WAHR1
37.Range("C23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 37, 0) 37C231 C23WAHR1
38.Range("E23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 38, 0) 38E231 E23WAHR1
39.Range("G23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 39, 0) 39G231 G23WAHR1
40.Range("I23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 40, 0) 40I231 I23WAHR1
41.Range("C24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 41, 0) 41C241 C24WAHR1
42.Range("E24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 42, 0) 42E241 E24WAHR1
43.Range("E19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 43, 0) 43E191 E19WAHR1
44.Range("G24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 45, 0) 45G241 I24FALSCH2
45.Range("G21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 46, 0) 46G211 G24FALSCH1
46.Range("K24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 47, 0) 47K241 G21FALSCH1
47.Range("G19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 49, 0) 49G191 K24FALSCH1
48.Range("I24").Value = ""  I241 I24WAHR2
49      G19FALSCH1
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


Spalte C und D habe ich aus Ralfs Code kopiert und mit Text in Spalten herausgefiltert und sortiert nach D
Zum Ende hin scheint die Konzentration nachgelassen zu haben, als Opa nur verständlich..... :19:

snb

17.03.2017, 23:06
in Bosko's Datei:


Code:
Sub M_snb()
  sn = Sheets("Datenbank").Cells(3, 3).CurrentRegion
  sp = Sheets("Tabelle3").Range("C8:K24")
  x = Sheets("Tabelle3").Cells(7, 5)
    
  y = 2
  For j = 1 To UBound(sp)
    Select Case j
    Case 1, 2, 3, 5, 7, 8, 9, 11, 12, 14, 16, 17
      For jj = 1 To UBound(sp, 2) Step 2
        sp(j, jj) = sn(Asc(x) - 96, y)
        y = y + 1
        Select Case j
        Case 8
          If jj = 1 Then Exit For
        Case 1, 12, 14
          If jj = 5 Then Exit For
        Case 2, 9, 16
          If jj = 7 Then Exit For
        End Select
      Next
    End Select
  Next
    
  Sheets("Tabelle3").Range("C8:K24").Offset(22) = sp
End Sub

BoskoBiati

18.03.2017, 06:56
Hi atilla,

was glaubst Du, warum die Zahlen in Tabelle 3 (die mit Ralfs Code erzeugt sind!) alle gelb hinterlegt sind?
Seiten: 1 2 3
Clever-Excel-Forum > Office-Anwendungen > Excel > festes Tabellenende durch loLetzte ersetzen in VBA-Formel