AW: Textboxinhalt in nächste freie Zelle schreiben
01.09.2016 13:37:50
UweD
Hallo
du hast in Blatt1 irgendwas in Zeile 4 bis 951 drinstehen.
Lösche mal die kompletten Zeilen, und 1x speichern
Wenjn du jetzt die letzte benutzte Zeile ermittels, wird 4 angezeigt.
Da gilt für Tabelle 1, hier musst du noch 1 addieren, dann hast su die erste Freie Zeile
als auch für Tabelle 2..
so klappt es aber...
Private Sub UserForm_Activate()
Dim lZeile As Long
TextBox501 = ""
TextBoxDatum = ""
TextBox601 = ""
TextBox602 = ""
TextBox603 = ""
TextBox604 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
TextBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox24 = ""
TextBox25 = ""
TextBox26 = ""
TextBox27 = ""
TextBox28 = ""
TextBox30 = ""
TextBox31 = ""
TextBox32 = ""
TextBox33 = ""
TextBox34 = ""
TextBox35 = ""
TextBox36 = ""
TextBox37 = ""
TextBox38 = ""
TextBox40 = ""
TextBox41 = ""
TextBox42 = ""
TextBox43 = ""
TextBox44 = ""
TextBox45 = ""
TextBox46 = ""
TextBox47 = ""
TextBox48 = ""
TextBox49 = ""
TextBox50 = ""
TextBox11.SetFocus
Me!TextBox11.TabIndex = 1
Me!TextBox12.TabIndex = 2
Me!TextBox13.TabIndex = 3
Me!TextBox14.TabIndex = 4
Me!TextBox15.TabIndex = 5
Me!TextBox16.TabIndex = 6
Me!TextBox17.TabIndex = 7
Me!TextBox18.TabIndex = 8
Me!TextBox19.TabIndex = 9
Me!TextBox20.TabIndex = 10
Me!TextBox21.TabIndex = 11
Me!TextBox22.TabIndex = 12
Me!TextBox23.TabIndex = 13
Me!TextBox24.TabIndex = 14
Me!TextBox25.TabIndex = 15
Me!TextBox26.TabIndex = 16
Me!TextBox27.TabIndex = 17
Me!TextBox28.TabIndex = 18
Me!TextBox29.TabIndex = 19
Me!TextBox30.TabIndex = 20
Me!TextBox31.TabIndex = 21
Me!TextBox32.TabIndex = 22
Me!TextBox33.TabIndex = 23
Me!TextBox34.TabIndex = 24
Me!TextBox35.TabIndex = 25
Me!TextBox36.TabIndex = 26
Me!TextBox37.TabIndex = 27
Me!TextBox38.TabIndex = 28
Me!TextBox39.TabIndex = 29
Me!TextBox40.TabIndex = 30
Me!TextBox41.TabIndex = 31
Me!TextBox42.TabIndex = 32
Me!TextBox43.TabIndex = 33
Me!TextBox44.TabIndex = 34
Me!TextBox45.TabIndex = 35
Me!TextBox46.TabIndex = 36
Me!TextBox47.TabIndex = 37
Me!TextBox48.TabIndex = 38
Me!TextBox49.TabIndex = 39
Me!TextBox50.TabIndex = 40
lZeile = Tabelle2.Cells.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile des gesamten Blattes
Do While Trim(CStr(Tabelle1.Cells(3, 1).Value)) <> ""
TextBoxDatum = Trim(CStr(Tabelle1.Cells(lZeile, 16).Value))
TextBox501 = Tabelle2.Cells(lZeile, 1).Value
TextBox1 = Tabelle2.Cells(lZeile, 2).Value
TextBox2 = Tabelle2.Cells(lZeile, 3).Value
TextBox3 = Tabelle2.Cells(lZeile, 4).Value
TextBox4 = Tabelle2.Cells(lZeile, 5).Value
TextBox5 = Tabelle2.Cells(lZeile, 6).Value
TextBox6 = Tabelle2.Cells(lZeile, 7).Value
TextBox7 = Tabelle2.Cells(lZeile, 8).Value
TextBox8 = Tabelle2.Cells(lZeile, 9).Value
TextBox9 = Tabelle2.Cells(lZeile, 10).Value
TextBox10 = Tabelle2.Cells(lZeile, 11).Value
TextBox601 = Tabelle2.Cells(lZeile, 12).Value
TextBox602 = Tabelle2.Cells(lZeile, 13).Value
TextBox603 = Tabelle2.Cells(lZeile, 14).Value
TextBox604 = Tabelle2.Cells(lZeile, 15).Value
Exit Do
lZeile = lZeile + 1
Loop
End Sub
'Speichern Schaltfläche
Private Sub CommandButton1_Click()
Dim lZeile As Long
If Trim(CStr(TextBox1.Text)) = "" Then
MsgBox "Vorn anfangen zu schreiben!", vbCritical + vbOKOnly, "FEHLER!"
Exit Sub
End If
lZeile = Tabelle1.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 'erste freie Zeile des gesamten Blattes
Do While Trim(CStr(Tabelle1.Cells(3, 1).Value)) <> ""
On Error Resume Next
Tabelle1.Cells(lZeile, 2).Value = Trim(CStr(TextBox501.Value))
Tabelle1.Cells(lZeile, 3).Value = TextBox601.Value
Tabelle1.Cells(lZeile, 4).Value = TextBox11.Value
Tabelle1.Cells(lZeile, 5).Value = TextBox12.Value
Tabelle1.Cells(lZeile, 6).Value = TextBox13.Value
Tabelle1.Cells(lZeile, 7).Value = TextBox14.Value
Tabelle1.Cells(lZeile, 8).Value = TextBox15.Value
Tabelle1.Cells(lZeile, 9).Value = TextBox16.Value
Tabelle1.Cells(lZeile, 10).Value = TextBox17.Value
Tabelle1.Cells(lZeile, 11).Value = TextBox18.Value
Tabelle1.Cells(lZeile, 12).Value = TextBox19.Value
Tabelle1.Cells(lZeile, 13).Value = TextBox20.Value
Exit Do
lZeile = lZeile + 1
Loop
ActiveWorkbook.Save
Unload Me
End Sub
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 15 - mit VBAHTML 12.6.0
Gruß UweD