Clever-Excel-Forum › Office-Anwendungen › Excel v
festes Tabellenende durch loLetzte ersetzen in VBA-Formel

Thema geschlossen 
Baumstrukturmodus
festes Tabellenende durch loLetzte ersetzen in VBA-Formel
Rabe Offline
:-)
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#1
16.03.2017, 18:23 (Dieser Beitrag wurde zuletzt bearbeitet: 16.03.2017, 18:24 von Rabe.)
Hallo,

wie kann ich im folgenden Code die 71 (in den Spalten A, B, C und AY) in den Formeln durch loLetzte ersetzen?
Zum Zweiten würde ich gerne das Eintragen der Formel in I7 und K7 durch Eintragen der Werte ersetzen, also anstelle der INDEX/VERWEIS-Formel die WorksheetFunction verwenden.
            With Worksheets("Eingabe_ELC")
              loLetzte = .Cells(Rows.Count, 3).End(xlUp).Row           ' letzte belegte in Spalte C (3) 
              'Formel eintragen 
              .Range("I7").FormulaLocal = "=INDEX(Datenbank!$A$2:$A$71;VERGLEICH($E$7;Datenbank!$C$2:$C$71;0))"
              .Range("K7").FormulaLocal = "=INDEX(Datenbank!$B$2:$B$71;VERGLEICH($E$7;Datenbank!$C$2:$C$71;0))"
              'Werte eintragen 
              .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)
Suchen
Top
Glausius Offline
CEF-Moderator
Registriert seit: 11.04.2014
Version(en): 2021
#2
16.03.2017, 18:41
Hallo Ralf,

ich denke einmal so:

With Worksheets("Eingabe_ELC")
              loLetzte = .Cells(Rows.Count, 3).End(xlUp).Row           ' letzte belegte in Spalte C (3)
              'Formel eintragen
              .Range("I7").FormulaLocal = "=INDEX(Datenbank!$A$2:$A$71;VERGLEICH($E$7;Datenbank!$C$2:$C$" & LoLetzte & ";0))"
              .Range("K7").FormulaLocal = "=INDEX(Datenbank!$B$2:$B$71;VERGLEICH($E$7;Datenbank!$C$2:$C$" & LoLeetzte & ";0))"
              'Werte eintragen
              .Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte & "), 2, 0)
              .Range("E8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte & "), 3, 0)
              .Range("G8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte & "), 4, 0)
              .Range("C9").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte & "), 5, 0)
96 
Gruß Günter
aus der Helden-, Messe-, Musik-, Buch-, Universitäts- und Autostadt Leipzig
Suchen
Top
snb Offline
Benutzer
Registriert seit: 29.09.2015
Version(en): 2030,5
#3
16.03.2017, 18:53 (Dieser Beitrag wurde zuletzt bearbeitet: 16.03.2017, 18:54 von snb.)
In VBA könnte das so aussehen:

Sub M_snb()
   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
Edn Sub
Zum übersetzen von Excel Formeln:

http://dolf.trieschnigg.nl/excel/index.p...gids=en+de
Homepage Suchen
Top
Rabe Offline
:-)
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#4
17.03.2017, 09:56 (Dieser Beitrag wurde zuletzt bearbeitet: 17.03.2017, 10:29 von Rabe.)
Hi Günter,

in den ersten zwei Zeilen hat das Weglassen der Zeilen funktioniert, die Formel wurde dadurch auf die gesamten Spalten A, B und C erweitert.

(16.03.2017, 18:41)Glausius schrieb: .Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte & "), 2, 0)

Hier wird beim Schreiben der Formel schon ein Fehler angezeigt, nach loLetzte wird eine schließende Klammer, das Anweisungsende erwartet.

Wenn ich es so ändere, also den rot markierten Teil weglasse:
Code:
              .Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte), 2, 0)
passiert gar nichts, d.h. es wird kein Wert in die Zelle eingetragen.

Es sind auch nicht nur 4 Zellen, in die was eingetragen werden soll, sondern insgesamt 46.
Suchen
Top
Rabe Offline
:-)
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#5
17.03.2017, 10:02 (Dieser Beitrag wurde zuletzt bearbeitet: 17.03.2017, 10:46 von Rabe.)
Hi,

(16.03.2017, 18: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:
   

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
Suchen
Top
Steffl Offline
Excelanwender
Registriert seit: 11.04.2014
Version(en): Office 2007
#6
17.03.2017, 10:20
Hallo Ralf,

beim Vorschlag von Günter war ein & " zuviel.

Code:
.Range("C8").Value = WorksheetFunction.VLookup(.Range("E7"), Worksheets("Datenbank").Range("C3:AY" & Loletzte), 2, 0)
Gruß Stefan
Win 10 / Office 2016
Suchen
Top
Rabe Offline
:-)
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#7
17.03.2017, 10:40 (Dieser Beitrag wurde zuletzt bearbeitet: 17.03.2017, 15:00 von Rabe.)
Hi Stefan,

(17.03.2017, 10:20)Steffl schrieb: beim Vorschlag von Günter war ein & " zuviel.

das hatte ich auch schon bemerkt, aber es kommt bei Verwendung dieser Zelle folgender Fehler:
   
Suchen
Top
Steffl Offline
Excelanwender
Registriert seit: 11.04.2014
Version(en): Office 2007
#8
17.03.2017, 10:51
Hi Ralf,

welchen Wert hat in dem Fall die Variable LoLetzte?
Gruß Stefan
Win 10 / Office 2016
Suchen
Top
atilla Abwesend
....
Registriert seit: 14.04.2014
Version(en): 2003, 2007
#9
17.03.2017, 10:56
Hallo ihr beiden,

schaut mal wo ihr loLetzte bestimmt  Blush

Den Code würde ich ohne Funktionen so aufbauen:


Code:
Sub mach()
 Dim x
 Dim ati
 
 With Sheets("Datenbank")
   x = Application.Match(Sheets("Eingabe_ELC").Range("E7"), .Columns("C:C"), 0)
   If IsNumeric(x) Then
     ati = Application.Transpose(Application.Transpose(.Range(.Cells(x, 1), .Cells(x, 45))))
   Else
     MsgBox "Wert aus Zelle E7 in Datenbank nicht vorhanden)"
     Exit Sub
   End If
 End With
 
 With Sheets("Eingabe_ELC")
         .Range("I7") = ati(1)
         .Range("K7") = ati(2)
         .Range("C8").Value = ati(4) ''Zahl in Klammern ist die Spaltenzahl aus deiner VLookupfunktion + 2
         .Range("E8").Value = ati(5)
         .Range("G8").Value = ati(6)
         .Range("C9").Value = ati(7)
         'usw
 
 
 End With

End Sub
Gruß Atilla
[-] Folgende(r) 1 Nutzer sagt Danke an atilla für diesen Beitrag:
  • Rabe
Suchen
Top
Rabe Offline
:-)
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 2016 + 365
#10
17.03.2017, 11:16 (Dieser Beitrag wurde zuletzt bearbeitet: 18.03.2017, 11:06 von Rabe.)
Hi Atilla,

(17.03.2017, 10:56)atilla schrieb: schaut mal wo ihr loLetzte bestimmt  Blush

Den Code würde ich ohne Funktionen so aufbauen:

ooh, wie peinlich! Jetzt geht es.
Super, Danke für den Hinweis!
Ich hatte beim Einfügen der Codezeile noch dran gedacht, daß ich den Punkt entfernen muß, aber dann wurde ich abgelenkt und habe es vergessen.

Danke, der Code sieht gut aus, den teste ich später, jetzt muß erst das Womo in die Werkstatt!

[edit] jetzt mußte das Abholauto auch gleich in der Werkstatt bleiben wegen defektem Luftmengenmesser.  :16:  :27:

Ok, der Code funktioniert einwandfrei!
Ich mußte nur die 45 aus der ati= Codezeile in 51 ändern, damit auch alle Zellen gefüllt werden konnten.
Super, so gefällt mir das!
Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste