New row isn't being added onto my table

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
106
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Is your data in a real table or just a range?
If it is a real table, what's the name?
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
106
Hi There,

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

It is in a table called "Data".

Mike
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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)
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
106
I'll try this out first thing tomorrow, thank you so much :)
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
106
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,822
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top