Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Find last row

  1. #1
    New Member
    Join Date
    May 2018
    Location
    Belgium
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find last row

    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

  2. #2
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,434
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

    Maybe:
    Code:
    Item = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Row
    bye
    Anthony (GMT+1)

  3. #3
    New Member
    Join Date
    May 2018
    Location
    Belgium
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

    Quote Originally Posted by Anthony47 View Post
    Maybe:
    Code:
    Item = Worksheets("Histo").Cells(Rows.Count, 1).End(xlUp).Row
    bye

    No not working,

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,982
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find last row

    You are using lItem when your last row is using ltem in the Cells part.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    May 2018
    Location
    Belgium
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

    Quote Originally Posted by MARK858 View Post
    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?

  6. #6
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,434
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

    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
    Anthony (GMT+1)

  7. #7
    New Member
    Join Date
    May 2018
    Location
    Belgium
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

    Quote Originally Posted by Anthony47 View Post
    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
    Last edited by johnster; Jun 11th, 2018 at 08:46 AM.

  8. #8
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,434
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

    Quote Originally Posted by johnster View Post
    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
    Anthony (GMT+1)

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Find last row

    Try
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    New Member
    Join Date
    May 2018
    Location
    Belgium
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find last row

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •