festes Tabellenende durch loLetzte ersetzen in VBA-Formel

[BoskoBiati]

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]

Warum keine Beispieldatei hochgeladen ?

[Rabe]

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]

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]

Hi Edgar,

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]

Hi Ralf,

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:


  Ralf_SVERWEIS.xlsb (Größe: 26,96 KB / Downloads: 6)

[atilla]

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]

Hallo Edgar,

Du hast immer noch falsche Bezüge:

Arbeitsblatt mit dem Namen 'Tabelle3'
	A	B	C	D	E	F	G	H	I
1			Spalte Quelle	Zelle Ziel			im Code von Edgar
2	.Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 2, 0)		2	C8	1		C8	WAHR	1
3	.Range("E8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 3, 0)		3	E8	1		E8	WAHR	1
4	.Range("G8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 4, 0)		4	G8	1		G8	WAHR	1
5	.Range("C9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 5, 0)		5	C9	1		C9	WAHR	1
6	.Range("E9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 6, 0)		6	E9	1		E9	WAHR	1
7	.Range("G9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 7, 0)		7	G9	1		G9	WAHR	1
8	.Range("I9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 8, 0)		8	I9	1		I9	WAHR	1
9	.Range("C10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 9, 0)		9	C10	1		C10	WAHR	1
10	.Range("E10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 10, 0)		10	E10	1		E10	WAHR	1
11	.Range("G10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 11, 0)		11	G10	1		G10	WAHR	1
12	.Range("I10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 12, 0)		12	I10	1		I10	WAHR	1
13	.Range("K10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 13, 0)		13	K10	1		K10	WAHR	1
14	.Range("C12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 14, 0)		14	C12	1		C12	WAHR	1
15	.Range("E12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 15, 0)		15	E12	1		E12	WAHR	1
16	.Range("G12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 16, 0)		16	G12	1		G12	WAHR	1
17	.Range("I12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 17, 0)		17	I12	1		I12	WAHR	1
18	.Range("K12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 18, 0)		18	K12	1		K12	WAHR	1
19	.Range("C14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 19, 0)		19	C14	1		C14	WAHR	1
20	.Range("E14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 20, 0)		20	E14	1		E14	WAHR	1
21	.Range("G14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 21, 0)		21	G14	1		G14	WAHR	1
22	.Range("I14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 22, 0)		22	I14	1		I14	WAHR	1
23	.Range("K14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 23, 0)		23	K14	1		K14	WAHR	1
24	.Range("C15").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 24, 0)		24	C15	1		C15	WAHR	1
25	.Range("C16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 25, 0)		25	C16	1		C16	WAHR	1
26	.Range("E16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 26, 0)		26	E16	1		E16	WAHR	1
27	.Range("G16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 27, 0)		27	G16	1		G16	WAHR	1
28	.Range("I16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 28, 0)		28	I16	1		I16	WAHR	1
29	.Range("C18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 29, 0)		29	C18	1		C18	WAHR	1
30	.Range("E18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 30, 0)		30	E18	1		E18	WAHR	1
31	.Range("G18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 31, 0)		31	G18	1		G18	WAHR	1
32	.Range("I18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 32, 0)		32	I18	1		I18	WAHR	1
33	.Range("K18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 33, 0)		33	K18	1		K18	WAHR	1
34	.Range("C19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 34, 0)		34	C19	1		C19	WAHR	1
35	.Range("C21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 35, 0)		35	C21	1		C21	WAHR	1
36	.Range("E21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 36, 0)		36	E21	1		E21	WAHR	1
37	.Range("C23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 37, 0)		37	C23	1		C23	WAHR	1
38	.Range("E23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 38, 0)		38	E23	1		E23	WAHR	1
39	.Range("G23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 39, 0)		39	G23	1		G23	WAHR	1
40	.Range("I23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 40, 0)		40	I23	1		I23	WAHR	1
41	.Range("C24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 41, 0)		41	C24	1		C24	WAHR	1
42	.Range("E24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 42, 0)		42	E24	1		E24	WAHR	1
43	.Range("E19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 43, 0)		43	E19	1		E19	WAHR	1
44	.Range("G24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 45, 0)		45	G24	1		I24	FALSCH	2
45	.Range("G21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 46, 0)		46	G21	1		G24	FALSCH	1
46	.Range("K24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 47, 0)		47	K24	1		G21	FALSCH	1
47	.Range("G19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 49, 0)		49	G19	1		K24	FALSCH	1
48	.Range("I24").Value = ""			I24	1		I24	WAHR	2
49							G19	FALSCH	1

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]

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]

Hi atilla,

was glaubst Du, warum die Zahlen in Tabelle 3 (die mit Ralfs Code erzeugt sind!) alle gelb hinterlegt sind?