Hi,
(16.03.2017, 17:53)snb schrieb: [ -> ]In VBA könnte das so aussehen:
Danke, das muß ich jetzt erst mal verstehen und dann auf die restlichen 42 Zellen versuchen anzuwenden.
Beim Austesten mußte zuerst in der sn= - Zeile das schließende ) weg, danach wurde aber nichts aus der Datenbank in das Eingabe_ELC-Blatt übertragen, weil folgender Fehler kam:
[
attachment=10271]
Hier das gesamte Makro, das funktioniert, aber eben nicht verallgemeinert (max. Zeilenzahl in Datenbank fix) ist und die INDEX-VERWEIS-Formeln in den zwei Zellen hat, in denen ich lieber die Werte stehen hätte:
'Option Explicit
Sub SVERWEIS_eintragen()
'
' übertragen der Daten aus Datenbank
'
'
On Error GoTo Fehler
' With Sheets("Datenbank")
' sn = .Range("C3:G3").Resize.Cells(Rows.Count, 3).End(xlUp).Row - 2
' sp = .Range("C8:G9")
' End With
'
' c00 = Sheets("Eingabe_ELC").Cells(7, 5)
'
' For j = 1 To UBound(sn)
' If sn(j, 1) = c00 Then
' sp(1, 1) = sn(j, 2)
' sp(2, 1) = sn(j, 5)
' sp(1, 3) = sn(j, 3)
' sp(1, 5) = sn(j, 4)
' Exit For
' End If
' Next
' If j <= UBound(sn) Then Sheets("Datenbank").Range("C8:G9") = sp
With Worksheets("Eingabe_ELC")
loLetzte = .Cells(Rows.Count, 3).End(xlUp).Row ' letzte belegte in Spalte C (3)
'Formel eintragen hier stand ursprünglich $A$2:$A$71 und $C$2:$C$71
.Range("I7").FormulaLocal = "=INDEX(Datenbank!$A:$A;VERGLEICH($E$7;Datenbank!$C:$C;0))"
.Range("K7").FormulaLocal = "=INDEX(Datenbank!$B:$B;VERGLEICH($E$7;Datenbank!$C:$C;0))"
'Werte eintragen
'.Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & loLetzte), 2, 0)
.Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 2, 0)
.Range("E8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 3, 0)
.Range("G8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 4, 0)
.Range("C9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 5, 0)
.Range("E9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 6, 0)
.Range("G9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 7, 0)
.Range("I9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 8, 0)
.Range("C10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 9, 0)
.Range("E10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 10, 0)
.Range("G10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 11, 0)
.Range("I10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 12, 0)
.Range("K10").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 13, 0)
.Range("C12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 14, 0)
.Range("E12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 15, 0)
.Range("G12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 16, 0)
.Range("I12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 17, 0)
.Range("K12").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 18, 0)
.Range("C14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 19, 0)
.Range("E14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 20, 0)
.Range("G14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 21, 0)
.Range("I14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 22, 0)
.Range("K14").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 23, 0)
.Range("C15").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 24, 0)
.Range("C16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 25, 0)
.Range("E16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 26, 0)
.Range("G16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 27, 0)
.Range("I16").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 28, 0)
.Range("C18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 29, 0)
.Range("E18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 30, 0)
.Range("G18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 31, 0)
.Range("I18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 32, 0)
.Range("K18").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 33, 0)
.Range("C19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 34, 0)
.Range("E19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 43, 0)
.Range("G19").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 49, 0)
.Range("C21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 35, 0)
.Range("E21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 36, 0)
.Range("G21").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 46, 0)
.Range("C23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 37, 0)
.Range("E23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 38, 0)
.Range("G23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 39, 0)
.Range("I23").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 40, 0)
.Range("C24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 41, 0)
.Range("E24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 42, 0)
.Range("G24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 45, 0)
.Range("I24").Value = ""
.Range("K24").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY71"), 47, 0)
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
Fehler:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "FehlerNr.: " & Err.Number & vbNewLine & vbNewLine _
& "Beschreibung: " & Err.Description _
, vbCritical, "Fehler"
End Sub