Find last row

[johnster]

Why does my code not work? It always writes in row A

The code in green works fine.

Private Sub CommandButton100_Click()
Dim lItem As Long
Dim IRange As Integer

For lItem = 0 To ListBox1.ListCount - 1
ListBox1.ColumnCount = 2
With Worksheets("Sheet6")
.Cells(lItem + 5, 1) = ListBox1.List(lItem, 0)
.Cells(lItem + 5, 2) = ListBox1.List(lItem, 1)
End With

With Sheets("Histo")
Item = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Value
.Cells(lItem + 1, 1) = Date
.Cells(lItem + 1, 2) = ListBox1.List(lItem, 0)
.Cells(lItem + 1, 3) = ListBox1.List(lItem, 1)
End With

 

[Anthony47]

Maybe:

Item = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Row

bye

 

[johnster]

Maybe:

Item = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Row

bye

No not working,

 

[MARK858]

You are using lItem when your last row is using ltem in the Cells part.

 

[johnster]

You are using lItem when your last row is using ltem in the Cells part.

Mark858

Thats the part that i do not understand. What do you suggest?

 

[Anthony47]

I think MARK is suggesting not to use "Item" as the name of the variable, because Item is a keyword in vba
So use LItem = etc etc

 

[johnster]

I think MARK is suggesting not to use "Item" as the name of the variable, because Item is a keyword in vba
So use LItem = etc etc

Thx for your help but now he copies only the first column.

With Sheets("Histo")
lItem = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Row
.Cells(lItem + 1, 1) = Date
.Cells(lItem + 1, 2) = ListBox1.List(LlItem, 0)
.Cells(lItem + 1, 3) = ListBox1.List(LlItem, 1)
.Cells(lItem + 1, 4) = TextBox2.Value
End With

 

[Anthony47]

Thx for your help but now he copies only the first column.

With Sheets("Histo")
lItem = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Row
.Cells(lItem + 1, 1) = Date
.Cells(lItem + 1, 2) = ListBox1.List(LlItem, 0)
.Cells(lItem + 1, 3) = ListBox1.List(LlItem, 1)
.Cells(lItem + 1, 4) = TextBox2.Value
End With

One moment...
You cannot expect that we (at least "me") understand what you are trying to obtain from a code that don't do that.

So either explain what you wish to obtain or ask yourself why somewhere you use LItem and somewhere else LLItem

Bye

 

[Fluff]

Try

With Sheets("Histo")
nItem = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Value
.Cells(nItem + 1, 1) = Date
.Cells(nItem + 1, 2) = ListBox1.List(lItem, 0)
.Cells(nItem + 1, 3) = ListBox1.List(lItem, 1)
End With

 

[johnster]

I do not know what i did, i still have to practice

Is it possible to take a look at my file?
https://johnneke886.stackstorage.com/s/44GyI1dNBPngech