Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 08:41:49
VBA: Probleme mit pdftk zum Zusammenfügen
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1220to1224
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
1220to1224
1220to1224
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA

VBA
Marc
Hi VBA team,
please see attached vba code I am using for copying text which is written in 2 text boxes to another worksheet ( sheet 2 ). It is working fine.
For example I write ( Marc ) in the first text box ( txt content ) and ( Marc II ) in the second (txtcap) it should transfer those into the sheet 2. Then I write again the same text into the boxes and click the Submit button ( Combo Box ) it should write the text exactly below the text before in sheet2.
Now the problem. If I format the Cells like " Center " it seems that the text is not going to be written in those cells. It is going below that. Could it be that the "rngusedrange" command also takes formatted cells as used?
Code:
Private Sub CommandButton1_Click()
Dim wksworksheet As Worksheet
Dim rngusedRange As Range
Dim strErrormessage As String
Dim lngRows As Long
strErrormessage = ""
If txtcontent = "" Then strErrormessage = strErrormessage & _
" Error 1: Please fill in field 1!" _
& Chr(10) & Chr(13)
If strErrormessage = "" Then
Set wksworksheet = ThisWorkbook.Worksheets("sheet2")
Set rngusedRange = wksworksheet.UsedRange
lngRows = rngusedRange.Rows.Count
wksworksheet.Cells(lngRows + 1, 2).Value = txtcontent.Text
wksworksheet.Cells(lngRows + 1, 3).Value = TxtCap.Text
Else
MsgBox strErrormessage
End If
End Sub

Any help is highly appreciated!
Thanks a lot and BR
Marc

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: VBA
01.07.2011 03:34:40
Mustafa
Hi Marc,
Could it be that the "rngusedrange" command also takes formatted cells as used?
Yes it takes.
Use This to find the Last written Cell in Column 2
lngRows = Cells(Rows.Count, 2).End(xlUp).Row
or directly the next empty Cell in Column 2
lngRows = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
This Codes searches from Down to Up which is the first Filled Cell in this Column.
BR from Cologne
PS: Sorry for my bad English :)
AW: VBA
01.07.2011 19:32:31
Marc
Hi,
thanks a lot. I seems that now it is only writing in B2 the text. I would like to do many entries so the next entry comes into B3 etc.
Do I overlook something?
Thanks a lot in adcance
Marc
ATT. CODE
Private Sub CommandButton1_Click()
Dim wksworksheet As Worksheet
Dim rngusedRange As Range
Dim strErrormessage As String
Dim lngRows As Long
'Eingaben ueberpruefen
strErrormessage = ""
If txtcontent = "" Then strErrormessage = strErrormessage & _
" Fehler 1:Sie muessen das Eingabefeld Posten ausfuellen !" _
& Chr(10) & Chr(13)
' Buchugssaetze schreiben
If strErrormessage = "" Then
Set wksworksheet = ThisWorkbook.Worksheets("Input Summary Check")
lngRows = Cells(Rows.Count, 2).End(xlUp).Row
wksworksheet.Cells(lngRows + 1, 2).Value = txtcontent.Text
wksworksheet.Cells(lngRows + 1, 3).Value = TxtCap.Text
Else
MsgBox strErrormessage
End If
End Sub

Anzeige
AW: VBA
01.07.2011 22:44:47
Mustafa
Hay Marc,
lngRows = Cells(Rows.Count, 2).End(xlUp).Row
finds allways the last filled Cell from down to up
If you want to use the next cell under this Cell, you need to change this way :
lngRows = Cells(Rows.Count, 2).End(xlUp).Offset(1,0).Row
BR from Cologne
AW: VBA
02.07.2011 03:09:44
Marc
Hi Mustafa,
thanks for the answer. I placed your formula but still it is only taking B2 to place the text. I shorten my Code. Please see attached. Could you please check where I do my mistake and maybe send me the full code back?
Thanks a lot for help & time
Marc
Private Sub CommandButton1_Click()
Dim wksworksheet As Worksheet
Dim strErrormessage As String
Dim lngRows As Long
strErrormessage = ""
If txtcontent = "" Then strErrormessage = strErrormessage & _
" Fehler 1:Sie muessen das Eingabefeld Posten ausfuellen !" _
& Chr(10) & Chr(13)
If strErrormessage = "" Then
Set wksworksheet = ThisWorkbook.Worksheets("Input Summary Check")
lngRows = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
wksworksheet.Cells(lngRows + 1, 2).Value = txtcontent.Text
Else
MsgBox strErrormessage
End If
End Sub

Anzeige
AW: VBA
02.07.2011 04:14:36
Mustafa
Hay Marc,
Test This Code please :
Sub Test()
Dim LngRows
LngRows = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
MsgBox (LngRows)
End Sub
If there is minimum one Entry in Column B the MSGBox must show you the Row of the next Empty Cell.
BR
AW: VBA
03.07.2011 02:39:37
Marc
Hi Mustafa,
I created a small file with 3 worksheets ( sheet1, sheet2, sheet3 )
In sheet 2 I have following in code and it works just fine:
Private Sub CommandButton1_Click()
Dim wksworksheet As Worksheet
Dim LngRows
LngRows = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
MsgBox (LngRows)
Cells(LngRows + 1, 2).Value = Txtcontent.Text
End Sub
I sheet1 I have what I would like to have. The text should be transfered to sheet3. But it does always take the same cell where it is copy it into ( Msg box as well gives the number always ). Please see the code. Any idea?
Private Sub CommandButton1_Click()
Dim wksworksheet As Worksheet
Dim LngRows
Sheets("Sheet3").Select
LngRows = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
MsgBox (LngRows)
ActiveSheet.Cells(LngRows + 1, 2).Value = Txtcontent.Text
End Sub

Thanks a lot for your help and BR
Marc
Anzeige
AW: VBA
03.07.2011 20:10:15
Mustafa
Hi Marc,
your Problem is you need to refer Exactly to that Sheet where you need to transfer the Data.
I think you Refer to the first Sheet but you want to write the Data into another Sheet.
Maybe thats your problem.
BR
AW: VBA
04.07.2011 13:48:47
Marc
Hi Mustafa,
this was it. Thanks a lot.
Marc
AW: VBA
01.07.2011 19:29:33
Marc
Hi,
I used your first formula and now it seems that it is filling always B2 cell. I would need to do many entrys and the first one should be B2 and the next one B3 and so on........
Att. the codes I have. Maybe I overlook something?
Thanks a lot in advance!!
Marc
Private Sub CommandButton1_Click()
Dim wksworksheet As Worksheet
Dim rngusedRange As Range
Dim strErrormessage As String
Dim lngRows As Long
'Eingaben ueberpruefen
strErrormessage = ""
If txtcontent = "" Then strErrormessage = strErrormessage & _
" Fehler 1:Sie muessen das Eingabefeld Posten ausfuellen !" _
& Chr(10) & Chr(13)
' Buchugssaetze schreiben
If strErrormessage = "" Then
Set wksworksheet = ThisWorkbook.Worksheets("Input Summary Check")
lngRows = Cells(Rows.Count, 2).End(xlUp).Row
wksworksheet.Cells(lngRows + 1, 2).Value = txtcontent.Text
wksworksheet.Cells(lngRows + 1, 3).Value = TxtCap.Text
Else
MsgBox strErrormessage
End If
End Sub

Anzeige

Links zu Excel-Dialogen

CommandButton

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige