Results 1 to 7 of 7

Thread: New row isn't being added onto my table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default New row isn't being added onto my table

    Hi there,

    I have a spreadsheet which uses a userform to enter data on to a sheet called "Readings".
    Everything seems to work fine except for a couple of things:

    - It misses out the first available line in my table
    - My table does not expand to encompass the new line

    I have checked my autocorrect settings and everything seems to be in order there.
    Below is the code that I am using:

    Code:
     Dim rw As Long    'next available row 
     Dim ws As Worksheet
            Set ws = Worksheets("Readings")
       
     
          'get the next avialable row in Sheet1
          rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
     
          'put the text box values in this row
          With ws
          
          .Cells(rw, "A").Value = Me.TBID.Value
          .Cells(rw, "B").Value = Me.TBDate.Value
          .Cells(rw, "C").Value = Me.TBReason.Value
          .Cells(rw, "D").Value = Me.CBoxSpread.Value
          
          
          .Cells(rw, "E").Value = Comboxnumber2.Text & " OF " & Comboxminor2.Text & "; " & Comboxarea2.Text
          .Cells(rw, "F").Value = TBanswer2.Value
          
          .Cells(rw, "G").Value = Comboxnumber3.Text & " OF " & Comboxminor3.Text & "; " & Comboxarea3.Text
          .Cells(rw, "H").Value = TBanswer3.Value
          
          .Cells(rw, "I").Value = Comboxnumber4.Text & " OF " & Comboxminor4.Text & "; " & Comboxarea4.Text
          .Cells(rw, "J").Value = TBanswer4.Value
          
          .Cells(rw, "K").Value = Comboxnumber5.Text & " OF " & Comboxminor5.Text & "; " & Comboxarea5.Text
          .Cells(rw, "L").Value = TBanswer5.Value
          
          .Cells(rw, "M").Value = Comboxnumber6.Text & " OF " & Comboxminor6.Text & "; " & Comboxarea6.Text
          .Cells(rw, "N").Value = TBanswer6.Value
          
          .Cells(rw, "O").Value = Comboxnumber7.Text & " OF " & Comboxminor7.Text & "; " & Comboxarea7.Text
          .Cells(rw, "P").Value = TBanswer7.Value
          
          .Cells(rw, "Q").Value = Comboxnumber8.Text & " OF " & Comboxminor8.Text & "; " & Comboxarea8.Text
          .Cells(rw, "R").Value = TBanswer8.Value
          
          .Cells(rw, "S").Value = Comboxnumber9.Text & " OF " & Comboxminor9.Text & "; " & Comboxarea9.Text
          .Cells(rw, "T").Value = TBanswer9.Value
          
          .Cells(rw, "U").Value = Comboxnumber10.Text & " OF " & Comboxminor10.Text & "; " & Comboxarea10.Text
          .Cells(rw, "V").Value = TBanswer10.Value
          
          .Cells(rw, "W").Value = Comboxnumber11.Text & " OF " & Comboxminor11.Text & "; " & Comboxarea11.Text
          .Cells(rw, "X").Value = TBanswer11.Value
          
          
     
          
       End With
    Many thanks in advance for any help you can give,

    Mike

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: New row isn't being added onto my table

    Is your data in a real table or just a range?
    If it is a real table, what's the name?

  3. #3
    Board Regular
    Join Date
    Feb 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New row isn't being added onto my table

    Hi There,

    I'm really sorry but I didn't realise someone had responded.

    It is in a table called "Data".

    Mike

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: New row isn't being added onto my table

    Try replacing this line:
    Code:
     rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    with this:
    Code:
          ws.ListObjects("Data").ListRows.Add
           rw = Split(ws.ListObjects("Data").DataBodyRange.Address, "$")(4)

  5. #5
    Board Regular
    Join Date
    Feb 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New row isn't being added onto my table

    I'll try this out first thing tomorrow, thank you so much

  6. #6
    Board Regular
    Join Date
    Feb 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New row isn't being added onto my table

    Hi Akuini,

    I tried changing the code but comes up with a runtime error saying that the subscript is out of range. Not sure what this means. Have checked and the name of my table is definitely "Data".

    Thanks,

    Mike

  7. #7
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: New row isn't being added onto my table

    It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.

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
  •