Live-Forum - Die aktuellen Beiträge
Datum
Titel
07.05.2024 10:21:13
Makro - Bilder ausführen
07.05.2024 09:59:06
CSV-Datei ohne Anführugnszeichen
07.05.2024 09:05:15
Einkaufsliste - Automatischer Abgleich Arbeitsblätter
07.05.2024 08:58:53
Dynamischer Bereich in Diagramm verwenden.
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1896to1900
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1896to1900
1896to1900
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Offset ?

Offset ?
20.09.2022 09:51:03
Fred
Hallo Excel Experten,
wenn ich einen Wert, einer bestimten Spalte aus der letzten Zeile kopieren und einfügen möchte, schreibe ich zB so;

Sub uebertrag_1()
LZ_q = ThisWorkbook.Sheets("Auswertung").Cells(Rows.Count, 6).End(xlUp).Row
LZ_z = ThisWorkbook.Sheets("Auswahl").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Auswertung").Cells(LZ_q, 47).Copy
Worksheets("Auswahl").Range("A" & LZ_z).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
Hier wird die letzte beschriebene Zeile in "Spalte 6" ermittelt
der letzte Wert in "Spalte 47" kopiert
in die erste unbeschriebene Zeile der "Spalte A" eingefügt.
Meine Frage;
Wie kopiere ich (nach oberen Muster) die Werte aus den "Spalten 44, 45, 47, 48" ?
Gruss
Fred

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Spalte unklar
20.09.2022 10:18:20
MCO
Hallo Fred!
Leider hast du nicht spezifiziert, in welche Spalte die Daten sollen, daher hab ich es jetzt mal ab Spalte "A" bzw 1 eingefügt.
Schau mal, wie du klar kommst...

    Dim sp_arr As Variant
Dim sp As Long
LZ_q = ThisWorkbook.Sheets("Auswertung").Cells(Rows.Count, 6).End(xlUp).Row
LZ_z = ThisWorkbook.Sheets("Auswahl").Cells(Rows.Count, 1).End(xlUp).Row + 1
sp_arr = Array(44, 45, 47, 48)
For sp = 0 To UBound(sp_array)
Sheets("Auswertung").Cells(LZ_q, sp_array(sp)).Copy
Sheets("Auswahl").Cells(LZ_z, 1 + sp).PasteSpecial Paste:=xlPasteValues
End If
Gruß, MCO
Anzeige
AW: Offset ?
20.09.2022 10:26:47
ChrisL
hi
Die Lösung von MCO gefällt mir sehr gut.
Dennoch hier eine Variante, welche simpel gehalten ist und hoffentlich leicht nachvollziehbar ist.

Sub uebertrag_1()
Dim wks_q As Worksheet
Dim wks_z As Worksheet
Dim LZ_q As Long
Dim LZ_z As Long
Set wks_q = ThisWorkbook.Sheets("Auswertung")
Set wks_z = ThisWorkbook.Sheets("Auswahl")
LZ_q = wks_q.Cells(wks_q.Rows.Count, 6).End(xlUp).Row
LZ_z = wks_z.Cells(wks_z.Rows.Count, 1).End(xlUp).Row + 1
wks_z.Cells(LZ_z, 1) = wks_q.Cells(LZ_q, 44).Value
wks_z.Cells(LZ_z, 2) = wks_q.Cells(LZ_q, 45).Value
wks_z.Cells(LZ_z, 3) = wks_q.Cells(LZ_q, 47).Value
wks_z.Cells(LZ_z, 4) = wks_q.Cells(LZ_q, 48).Value
End Sub
cu
Chris
Anzeige
AW: Offset ?
20.09.2022 11:28:14
Fred
Hallo MCO und Chris,
vielen Dank für die Aufmerksamkeit und die geschriebenen Lösungen!
Tatsächlich kann ich das Makro von Chris (obwohl voluminöser) viel besser nachvollziehen.
Der zu "kopierende Block" ist allerdings nur einer von neun zudem in "IF Anweisungen",- es würde also relativ viel Codezeilen geben.
Ich werde mich heute Nachmittag entscheiden ...
Vielen Dank für eure kompetenten Vorschläge!!
Gruss
Fred
AW: Offset ?
20.09.2022 12:26:00
ralf_b
Gibts dann auch nen Preis für den Gewinner?
Für die Nachvollziehbarkeit von Code, ist es meist besser nicht die kürzeste Variante zu wählen, gerade wenn man nicht so ganz der Profi ist. Sehr hilfreich sind aber auch ausführliche Kommentare im Code, die erheblich helfen wenn man später noch mal dran muß. Man könnte auch gleiche Codepassagen auslagern und den Basiscode somit lesbarer zu gestalten.
Anzeige
AW: Offset ?
20.09.2022 13:30:53
Fred
Ja ralf_b,
da hast du sicherlich recht!!
"gleiche Codepassagen auslagern" folgt wohl als nächstes.
Hier eine Beispieldatei:
https://www.herber.de/bbs/user/155281.xlsb
.. zumindest die Dim's, set's, LZ_* könnte ich wohl selber zusammen legen ...
Gruss
Fred
AW: Offset ?
20.09.2022 17:25:45
ChrisL
Hi Fred

Sub blockauswahl()
Dim wks_q As Worksheet
Dim wks_z As Worksheet
Dim LZ_q As Long
Dim LZ_z As Long
Dim i As Long
Set wks_q = ThisWorkbook.Sheets("Auswertung")
Set wks_z = ThisWorkbook.Sheets("Auswahl")
With wks_q
LZ_q = .Cells(.Rows.Count, 6).End(xlUp).Row
For i = 37 To 85 Step 6
If .Cells(LZ_q, i + 4) >= 1 Or _
.Cells(LZ_q, i + 2) = .Cells(LZ_q, i + 5) Or _
.Cells(LZ_q, i + 2) = .Cells(LZ_q, i + 5) + 1 Then
LZ_z = wks_z.Cells(wks_z.Rows.Count, 1).End(xlUp).Row + 1
wks_z.Cells(LZ_z, 1) = .Cells(3, 6).Value
wks_z.Cells(LZ_z, 6) = .Cells(3, i).Value
wks_z.Cells(LZ_z, 2) = .Cells(LZ_q, i + 1).Value
wks_z.Cells(LZ_z, 3) = .Cells(LZ_q, i + 2).Value
wks_z.Cells(LZ_z, 4) = .Cells(LZ_q, i + 4).Value
wks_z.Cells(LZ_z, 5) = .Cells(LZ_q, i + 5).Value
End If
Next i
End With
End Sub
cu
Chris
Anzeige
AW: Offset ?
20.09.2022 21:16:43
Fred
Hallo Chris,
vielen vielen Dank für den effizenten "Nachbau" meiner "VBA-Strickerei" !!
Ich habe die Reihenfolge für Zieltabelle leicht verändert und noch einen weiteren Wert (Anzahl der Spiele) hinzugefügt.

Sub blockauswahl()
Dim wks_q As Worksheet
Dim wks_z As Worksheet
Dim LZ_q As Long
Dim LZ_z As Long
Dim i As Long
Set wks_q = ThisWorkbook.Sheets("Auswertung")
Set wks_z = ThisWorkbook.Sheets("Auswahl")
With wks_q
LZ_q = .Cells(.Rows.Count, 6).End(xlUp).Row
For i = 37 To 85 Step 6
If .Cells(LZ_q, i + 4) >= 1 Or _
.Cells(LZ_q, i + 2) = .Cells(LZ_q, i + 5) Or _
.Cells(LZ_q, i + 2) = .Cells(LZ_q, i + 5) + 1 Then
LZ_z = wks_z.Cells(wks_z.Rows.Count, 1).End(xlUp).Row + 1
wks_z.Cells(LZ_z, 1) = .Cells(3, 6).Value
wks_z.Cells(LZ_z, 2) = .Cells(3, 35).Value
wks_z.Cells(LZ_z, 3) = .Cells(3, i).Value
wks_z.Cells(LZ_z, 4) = .Cells(LZ_q, i + 1).Value
wks_z.Cells(LZ_z, 5) = .Cells(LZ_q, i + 2).Value
wks_z.Cells(LZ_z, 6) = .Cells(LZ_q, i + 4).Value
wks_z.Cells(LZ_z, 7) = .Cells(LZ_q, i + 5).Value
End If
Next i
End With
End Sub
Dein Makro arbeitet die Vorgänge sicherlich schneller ab als meine Makros,- doch verstehen tue ich es (auch wenn ich es leicht verändert und ergänzt habe) teilweise nicht,- geschweige das ich so etwas selber schreibe (bis dahin wird es wohl kein VBA mehr geben :-)
Ich habe aber dennoch meine Version gelöscht und nun dein Krypto eingefügt.
Nochmals vielen Dank für deine Mühe des Nachbau's,- damit hatte ich eigentlich nicht gerechnet!
Gruss
Fred
Anzeige
AW: Offset ?
21.09.2022 08:29:20
ChrisL
Hi Fred
Danke für die Rückmeldung.
Deine Ansätze waren gut. Womöglich fehlt dir zum Verständnis lediglich das Prinzip von For-Next-Schleifen. Vielleicht möchtest du dich mal damit beschäftigen, um bei Bedarf auf Verschiebungen der Spalten reagieren zu können.
Die Variable "i" wird als Spalten-Index (Nummer) für die Quelle verwendet.

For i = 37 To 85 Step 6
MsgBox i
Nex i
i = 37, 37 = Spalte AK
i = 43, 43 = Spalte AQ
usw.
Du "hüpfst" also durch die 6er Blöck.
cu
Chris
AW: Offset ?
21.09.2022 12:19:58
Fred
Hallo Chris,
ja,- ich habe noch mehrere Sheets mit jeweils sich wiederholenden Spaltenaufbau (Blöcke) und es dann ratsam ist, ein solches Makro zu verstehen.
Ich habe noch 2 Fragen dazu,- wenn du erlaubst.
Frage 1:
Wie ermittel ich den Mittelwert (Average) für die (den Kriterien if, or) entsprechenden Spalten?
Also Average von i

wks_z.Cells(LZ_z, 8) = i=Average !!?

Anzeige
AW: Offset ?
21.09.2022 12:59:40
ChrisL
Hi
Mathe und Deutsch waren meine drei gutesten Fächer ;)
z.B.

wks_z.Cells(LZ_z, 8) = WorksheetFunction.Average(.Columns(i))
cu
Chris

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige