Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1480to1484
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
Inhaltsverzeichnis

Eine fortlaufende Nummer anhängen - wie?

Eine fortlaufende Nummer anhängen - wie?
23.03.2016 15:50:53
Christian
Hallo ich kopiere mit u.a. Coding so einiges von A nach B, zu einer kopierten Nummer (z.B. 14/15) soll nun je Zeile fortlaufend -1 angehängt werden; d.h.:
14/15-1
14/15-2
14/15-3
........
Der betroffene Bereich ist fett markiert:
Sub Plan()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long, tempZeile As Long
Dim strMark As String
Set WS1 = Worksheets("Fragen")
Set WS2 = Worksheets("Plan")
Application.ScreenUpdating = False
For iZeile = WS1.Cells(WS1.Rows.Count, 3).End(xlUp).Row To 5 Step -1
If IsNumeric(WS1.Cells(iZeile, 3)) And WS1.Cells(iZeile, 3)  "" Then
Select Case WS1.Cells(iZeile, 3)
Case 6 To 8: strMark = "Bemerkung"
Case 4: strMark = "Bemerkung"
Case 0 To 2: strMark = "Bemerkung"
Case Else: strMark = ""
End Select
If strMark  "" Then
tempZeile = Application.Match(strMark, WS2.Columns(6), 0) + 1
WS2.Rows(tempZeile).Insert Shift:=xlDown
WS2.Cells(tempZeile, 5) = WS1.Cells(iZeile, 1)
WS2.Cells(tempZeile, 6) = WS1.Cells(iZeile, 4)
WS2.Cells(tempZeile, 7) = WS1.Cells(iZeile, 3)
WS2.Cells(tempZeile, 2) = Worksheets("Deckblatt").Range("F7").Value
WS2.Cells(tempZeile, 8) = Worksheets("Deckblatt").Range("F11").Value
WS2.Cells(tempZeile, 3) = Worksheets("Deckblatt").Range("F12").Value
 WS2.Cells(tempZeile, 1) = Worksheets("Deckblatt").Range("F6").Value
WS2.Cells(tempZeile, 4) = "S"
Call ZeileFormatieren1(tempZeile, WS2)
End If
End If
Next iZeile
End Sub
Private Sub ZeileFormatieren1(Zeile As Long, WS As Worksheet)
With WS.Range(WS.Cells(Zeile, 1), WS.Cells(Zeile, 16))
.Interior.Pattern = xlNone
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.WrapText = True
.Rows.EntireRow.AutoFit
End With
Dim Zelle As Range
Set Zelle = Columns(7).Find(what:="0")
If Not Zelle Is Nothing Then Zelle.Offset(0, 0).Value = "A"
Set Zelle = Columns(7).Find(what:="4")
If Not Zelle Is Nothing Then Zelle.Offset(0, 0).Value = "A"
Set Zelle = Columns(7).Find(what:="6")
If Not Zelle Is Nothing Then Zelle.Offset(0, 0).Value = "F"
Set Zelle = Columns(7).Find(what:="8")
If Not Zelle Is Nothing Then Zelle.Offset(0, 0).Value = "V"
End Sub
Weiters bitte um Info, wie man vor dem Kopieren auch noch prüfen kann, ob die Fragennummer bereits vorkommt - wenn ja, dann nicht mehr kopieren:
Betroffene Coding-Zeile:
WS2.Cells(tempZeile, 5) = WS1.Cells(iZeile, 1)

Danke im Voraus für Eure Unterstützung!
Lg,
Chrisi

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
23.03.2016 15:58:38
UweD
Hallo
ungetestet:
i=1
For iZeile = WS1.Cells(WS1.Rows.Count, 3).End(xlUp).Row To 5 Step -1
If IsNumeric(WS1.Cells(iZeile, 3)) And WS1.Cells(iZeile, 3)  "" Then
Select Case WS1.Cells(iZeile, 3)
Case 6 To 8: strMark = "Bemerkung"
Case 4: strMark = "Bemerkung"
Case 0 To 2: strMark = "Bemerkung"
Case Else: strMark = ""
End Select
If strMark  "" Then
tempZeile = Application.Match(strMark, WS2.Columns(6), 0) + 1
WS2.Rows(tempZeile).Insert Shift:=xlDown
WS2.Cells(tempZeile, 5) = WS1.Cells(iZeile, 1) & "-" &i
i=i+1
'usw
LG UweD

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
23.03.2016 16:10:54
ChrisL
Hi Christian
https://www.herber.de/forum/messages/1483497.html
Der Code kann grundsätzlich nicht funktionieren, ausser die Ausgangslage hat sich geändert.
Case 6 To 8: strMark = "Bemerkung"
Case 4: strMark = "Bemerkung"
Case 0 To 2: strMark = "Bemerkung"
tempZeile = Application.Match(strMark, WS2.Columns(6), 0) + 1
strMark diente mal als Suchkriterium.
Zudem läuft die Schleife rückwärts, somit läuft die Nummerierung im Vorschlag von Uwe rückwärts. Um dies zu beheben müsste man den Code generell umschreiben. Und dann vermutlich je Block nummerieren.
cu
Chris

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
23.03.2016 17:14:14
Christian
Hi Chris,
schön wieder von Dir zu hören - dein kompetenter Rat ist immer Willkommen! Natürlich bin ich jedem anderen auch sehr dankbar ;-)
Darum frage ich mich, wie ich das Ganze umbauen muss - beim Umbau möchte ich auch gerne einbauen, dass vor dem Kopieren geprüft wird, ob die Nummer schon vorhanden ist - aber wie?
Vielleicht kannst mir Du / Ihr nochmal helfen - Danke!
Lg,
Chrisi

AW: Eine fortlaufende Nummer anhängen - wie?
23.03.2016 17:46:01
ChrisL
Hi Chrisi
Ach wie schön deine Computerschrift zu lesen :)
Gib uns bitte noch einmal eine Beispieldatei mit Ausgangslage und Soll-Zustand, so dass wir die Frage ein für allemal erschlagen können (auch wenn wir uns dann nicht mehr schreiben können). Inzwischen habe ich zu viele Fragezeichen, um weiter rum zu basteln. Nummerierung je Block, Überschreiben oder nicht, woher kommen plötzlich die doppelten, was und wo sind nun die Suchkriterien...
cu
Chris

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
23.03.2016 19:29:45
Christian
Hi Chris,
erneut vielen Dank für deine Hilfe!
Anbei das File:
https://www.herber.de/bbs/user/104578.xlsm
Die gelb markierten Nummern der Spalte A im Blatt "Plan" sollen um -1, -2, usw. ergänzt werden, da der erste Teil der Nummer vom Blatt "Deckblatt" kopiert wird. Und wie gesagt sollen nur Einträge kopiert werden welche im Blatt "Plan" noch nicht vorkommen - also kein Überschreiben oder zusätzliches Einfügen...
Dürfte etwas "Tricky" werden...
Danke,
Chrisi

AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 08:03:08
Christian
Guten Morgen,
würde mich freuen, wenn sich heute jemand in meiner Angelegenheit bemüht ;-)
Danke schonmal!
Lg,
Chrisi

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 11:01:24
ChrisL
Hi Chrisi

Sub Zusammenfassung()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long, tempZeile As Long
Dim strMark As String
Set WS1 = Worksheets("Fragen")
Set WS2 = Worksheets("Zusammenfassung")
Application.ScreenUpdating = False
For iZeile = WS1.Cells(WS1.Rows.Count, 3).End(xlUp).Row To 5 Step -1
If IsNumeric(WS1.Cells(iZeile, 3)) And WS1.Cells(iZeile, 3)  "" And _
WorksheetFunction.CountIf(WS2.Columns(1), WS1.Cells(iZeile, 1)) = 0 Then
Select Case WS1.Cells(iZeile, 3)
Case 10: strMark = "Positive Bemerkungen (10 Punkte):"
Case 6 To 8: strMark = "Hinweise / Verbesserungsvorschläge (6-8 Punkte):"
Case 4: strMark = "Nebenabweichungen (4 Punkte):"
Case 0 To 2: strMark = "Hauptabweichungen (0 - 2 Punkte):"
Case Else: strMark = ""
End Select
If strMark  "" Then
tempZeile = Application.Match(strMark, WS2.Columns(2), 0) + 1
WS2.Rows(tempZeile).Insert Shift:=xlDown
WS2.Cells(tempZeile, 1) = WS1.Cells(iZeile, 1)
WS2.Cells(tempZeile, 2) = WS1.Cells(iZeile, 4)
Call ZeileFormatieren(tempZeile, WS2)
End If
End If
Next iZeile
End Sub

Private Sub ZeileFormatieren(Zeile As Long, WS As Worksheet)
With WS.Range(WS.Cells(Zeile, 1), WS.Cells(Zeile, 2))
.Interior.Pattern = xlNone
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.WrapText = True
.Rows.EntireRow.AutoFit
End With
End Sub

Sub Plan()
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim iZeile As Long, tempZeile As Long, iZähler As Long
Dim strMark As String
Set WS1 = Worksheets("Fragen")
Set WS2 = Worksheets("Plan")
Set WS3 = Worksheets("Deckblatt")
Application.ScreenUpdating = False
For iZeile = 5 To WS1.Cells(WS1.Rows.Count, 3).End(xlUp).Row
If IsNumeric(WS1.Cells(iZeile, 3)) And WS1.Cells(iZeile, 3)  "" And _
WorksheetFunction.CountIf(WS2.Columns(5), WS1.Cells(iZeile, 1)) = 0 And _
WS1.Cells(iZeile, 3)  10 Then
iZähler = iZähler + 1
tempZeile = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(tempZeile, 5) = WS1.Cells(iZeile, 1)
WS2.Cells(tempZeile, 6) = WS1.Cells(iZeile, 4)
Select Case WS1.Cells(iZeile, 3)
Case 6: strMark = "F"
Case 8: strMark = "V"
Case Else: strMark = "A"
End Select
WS2.Cells(tempZeile, 7) = strMark
WS2.Cells(tempZeile, 2) = WS3.Range("F7").Value
WS2.Cells(tempZeile, 8) = WS3.Range("F11").Value
WS2.Cells(tempZeile, 3) = WS3.Range("F12").Value
WS2.Cells(tempZeile, 1) = WS3.Range("F6").Value & "-" & iZähler
WS2.Cells(tempZeile, 4) = "S"
End If
Next iZeile
tempZeile = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row
With WS2.Range(WS2.Cells(9, 1), WS2.Cells(tempZeile, 16))
.Interior.Pattern = xlNone
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.WrapText = True
.Rows.EntireRow.AutoFit
End With
End Sub

cu
Chris

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 11:44:49
Christian
Hey Chris,
vielen lieben Dank für deine Bemühungen! Jetzt wären aber schon einige Biere für Dich fällig ;-)
Funktioniert echt super - aber ich habe trotzdem noch eine Frage:
Wenn ich die Einträge generieren lasse und nachher eine Eintrag lösche, dann wird beim erneuten Ausführen des Makros nur der gelöschte Eintrag wieder angehängt (passt auch so, da Prüfung auf Fragennummer), jedoch beginnt die fortlaufende Nummer wieder bei 1.
Somit folgende Frage(n):
Kann die fortlaufende Nummer nicht fortgeführt werden? Eventuell sogar mit der Nummer die vorher gelöscht wurde? Oder wäre es nicht besser die fortlaufende Nummer erst nach dem Erzeugen der Einträge anzuhängen? So wäre es dann immer durchgehend...
Falls Du noch Lust hast, würde ich mich über eine Antwort freuen.
Glg,
Chrisi
P.S.: Was mich interessieren würde - wie alt bist eigentlich und was machst beruflich? Scheinst extrem gut drauf zu sein...

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 12:54:20
ChrisL
Hi Chrisi
Probier mal...
Sub Plan()
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim iZeile As Long, tempZeile As Long
Dim strMark As String
Set WS1 = Worksheets("Fragen")
Set WS2 = Worksheets("Plan")
Set WS3 = Worksheets("Deckblatt")
Application.ScreenUpdating = False
For iZeile = 5 To WS1.Cells(WS1.Rows.Count, 3).End(xlUp).Row
If IsNumeric(WS1.Cells(iZeile, 3)) And WS1.Cells(iZeile, 3)  "" And _
WorksheetFunction.CountIf(WS2.Columns(5), WS1.Cells(iZeile, 1)) = 0 And _
WS1.Cells(iZeile, 3)  10 Then
tempZeile = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(tempZeile, 5) = WS1.Cells(iZeile, 1)
WS2.Cells(tempZeile, 6) = WS1.Cells(iZeile, 4)
Select Case WS1.Cells(iZeile, 3)
Case 6: strMark = "F"
Case 8: strMark = "V"
Case Else: strMark = "A"
End Select
WS2.Cells(tempZeile, 7) = strMark
WS2.Cells(tempZeile, 2) = WS3.Range("F7").Value
WS2.Cells(tempZeile, 8) = WS3.Range("F11").Value
WS2.Cells(tempZeile, 3) = WS3.Range("F12").Value
WS2.Cells(tempZeile, 1).Formula = "=""" & WS3.Range("F6").Value & "-""&ROW()-8"
WS2.Cells(tempZeile, 4) = "S"
End If
Next iZeile
tempZeile = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row
With WS2.Range(WS2.Cells(9, 1), WS2.Cells(tempZeile, 16))
.Interior.Pattern = xlNone
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.WrapText = True
.Rows.EntireRow.AutoFit
End With
End Sub

Und zur Anschlussfrage nur soviel: Bin ungelernter Hobby-Programmierer, aber Alt genug, um schon einige Jahre Excel-Forum mitzulesen. 10-Finger-System habe ich noch auf der Schreibmaschine gelernt ;)
cu
Chris

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 13:54:06
Christian
Hey vielen Dank ;-)
Warum funktioniert das Coding eigentlich nur, wenn man keine Zellen verbunden hat?
Beim Originalfile sind viele Zellen verbunden und da funktioniert das Coding nicht, obwohl ich die Bedingungen geändert habe - z.B. statt vorher Spalte 3 hätte ich nun Spalte 11, da viele Zellen verbunden sind...
Vielleicht kannst mich kurz aufklären...

AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 14:06:59
ChrisL
Hi Chrisi
Grundsätzlich: Verbundene Zellen sind im Forum ein Reizwort, weil sie immer wieder Probleme machen (z.B. bei automatischer Zeilenhöhe). Bereits im ersten Beitrag habe ich die verbundenen Zellen darum aufgehoben (war auch gar nicht nötig).
Bin zwar verdammt cool drauf (kurz vor Ostern sowieso :), aber Ferndiagnosen kann ich leider trotzdem nicht machen.
Schau mal ob es nicht auch ohne verbundene Zellen geht. Falls nicht, einmal mehr eine Beispieldatei hochladen...
cu
Chris

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 15:41:55
Christian
Hi Chris,
ich habe versucht den Code für die Zusammenfassung in das Originalfile einzubauen - aber es funktioniert nicht - siehe File:
https://www.herber.de/bbs/user/104598.xlsm
Liegt das wirklich an den verbundenen Zellen oder an mir?
Bitte, Danke.
Lg,
Chrisi

AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 16:20:51
ChrisL
Hi Chrisi
Grummel...
- Suchbegriffe stimmen nicht mit Code überein
- Zusammenfassung "Positive Bemerkungen" muss in die Spalte C
- Fragen I125: Formelfehler muss weg =WENN(ISTFEHLER(MITTELWERT(I110:J124));"";MITTELWERT(I110:J124))
- Formatierung muss angepasst werden
- etc.

Sub Zusammenfassung()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iZeile As Long, tempZeile As Long
Dim strMark As String
Set WS1 = Worksheets("Fragen (BL4)")
Set WS2 = Worksheets("Zusammenfassung (BL2)")
Application.ScreenUpdating = False
For iZeile = WS1.Cells(WS1.Rows.Count, 9).End(xlUp).Row To 9 Step -1
If IsNumeric(WS1.Cells(iZeile, 9)) And WS1.Cells(iZeile, 9)  "" And _
WorksheetFunction.CountIf(WS2.Columns(1), WS1.Cells(iZeile, 1)) = 0 And _
Left(WS1.Cells(iZeile, 1), 4)  "Punk" Then
Select Case WS1.Cells(iZeile, 9)
Case 10: strMark = "Positive Bemerkungen"
Case 6 To 8: strMark = "Hinweise / Verbesserungsvorschläge:"
Case 4: strMark = "Nebenabweichungen:"
Case 0 To 2: strMark = "Hauptabweichungen:"
Case Else: strMark = ""
End Select
If strMark  "" Then
tempZeile = Application.Match(strMark, WS2.Columns(3), 0) + 1
WS2.Rows(tempZeile).Insert Shift:=xlDown
WS2.Cells(tempZeile, 1) = WS1.Cells(iZeile, 1)
WS2.Cells(tempZeile, 3) = WS1.Cells(iZeile, 2)
Call ZeileFormatieren(tempZeile, WS2)
End If
End If
Next iZeile
End Sub

Private Sub ZeileFormatieren(Zeile As Long, WS As Worksheet)
WS.Range(WS.Cells(Zeile, 1), WS.Cells(Zeile, 2)).Merge
WS.Range(WS.Cells(Zeile, 3), WS.Cells(Zeile, 17)).Merge
With WS.Range(WS.Cells(Zeile, 1), WS.Cells(Zeile, 17))
.Interior.Pattern = xlNone
.Font.Bold = False
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.WrapText = True
.Rows.EntireRow.AutoFit
End With
End Sub

Für Plan habe ich jetzt keine Zeit mehr, muss weg.
schöne Ostern
Chris

Anzeige
AW: Eine fortlaufende Nummer anhängen - wie?
24.03.2016 18:53:56
Christian
Hey Chris,
nun passt alles - besten Dank und frohe Ostern!
Glg

AW: Eine fortlaufende Nummer anhängen - wie?
25.03.2016 10:29:45
Christian
Eines passt nun doch nicht - aufgrund der verbundenen Zellen funktioniert die automatische Anpassung der Zeilenhöhe nicht mehr...
Hat hier jemand einen Tipp für mich, wie ich das auch noch hin bekomme? Und ja - die verbundenen Zellen brauche ich leider...
Danke im Voraus und schönes Wochenende!
Lg,
Chrisi

die verbundenen Zellen brauche ich leider-wozu?
25.03.2016 15:13:40
robert
....nur eine Frage :-)

die verbundenen Zellen brauche ich leider-wozu?
25.03.2016 15:13:41
robert
....nur eine Frage :-)

Gelöst
26.03.2016 16:20:49
cH_rI_sI
Mittlerweile so gelöst:
   Private Sub ZeileFormatieren(Zeile As Long, WS As Worksheet)
WS.Range(WS.Cells(Zeile, 1), WS.Cells(Zeile, 2)).Merge
With WS.Range(WS.Cells(Zeile, 1), WS.Cells(Zeile, 17))
.Interior.Pattern = xlNone
.Font.Bold = False
.Font.Size = 10
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.WrapText = True
.Columns("C:C").ColumnWidth = 55
.Rows.EntireRow.AutoFit
.Columns("C:C").ColumnWidth = 5
WS.Range(WS.Cells(Zeile, 3), WS.Cells(Zeile, 17)).Merge
End With
End Sub

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige