Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-05-2019, 09:29 AM
Bumba Bumba is offline Why is listrows.add not working in excel 2007? Windows 7 32bit Why is listrows.add not working in excel 2007? Office 2007
Novice
Why is listrows.add not working in excel 2007?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default Why is listrows.add not working in excel 2007?


Hi, I'm trying to add row to the end of the table "mt" but it is showing a run time error 9, subscript out of range but why?

File attached.

Pls help
Attached Files
File Type: xlsm data.xlsm (15.5 KB, 7 views)
Reply With Quote
  #2  
Old 11-05-2019, 05:46 PM
NoSparks NoSparks is offline Why is listrows.add not working in excel 2007? Windows 7 64bit Why is listrows.add not working in excel 2007? Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

"mt" is not the name of your table.
On the Formula ribbon, click Name Manager and you can filter for "Table Names" to see.
Reply With Quote
  #3  
Old 11-05-2019, 07:37 PM
Bumba Bumba is offline Why is listrows.add not working in excel 2007? Windows 7 32bit Why is listrows.add not working in excel 2007? Office 2007
Novice
Why is listrows.add not working in excel 2007?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default

Hi, but in my pc it is showing "mt" as table name...btw, how can I do it without giving the table a name?
Reply With Quote
  #4  
Old 11-05-2019, 11:15 PM
NoSparks NoSparks is offline Why is listrows.add not working in excel 2007? Windows 7 64bit Why is listrows.add not working in excel 2007? Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

All I have to go by is the file you posted and it does not have a table named "mt".

Table1 is the default name Excel gave to the table at the time it was created, I suspect you tried to rename it and in the process created the named range "mt".
Click the drop down arrow in the Name Box above column A, you'll see both mt and Table1.
Selecting each of them, you'll see they are not the same, one includes row 1 and one doesn't.

Quote:
how can I do it without giving the table a name?
you could refer to the first table on a sheet as .ListObjects(1)


Here's a couple of links to sites with info on Excel Tables:
The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru
Excel: Working with Tables (VBA)
Reply With Quote
  #5  
Old 11-09-2019, 05:59 AM
p45cal's Avatar
p45cal p45cal is offline Why is listrows.add not working in excel 2007? Windows 10 Why is listrows.add not working in excel 2007? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

You have a named range mt so you could:
Code:
Set tbl = Range("mt").ListObject
but your mt named range is itself based on a table name.
If you know where the table is you can use any cell of that table to refer to the table:
Code:
Set tbl = Sheets("Sheet1").range("A1").listobject
Of course you can bypass setting the tbl object variable with:
Code:
Sheets("Sheet1").Range("A1").ListObject.ListRows.Add
Reply With Quote
Reply

Tags
vba excel

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Outlook 2007 Search Not Working Harry Rolnick Outlook 1 04-06-2016 12:59 PM
Excel error window: Excel has stopped working MunganBrus Excel 1 01-13-2015 11:19 AM
How can I fix a print view problem that causes it to stop working.. Excel 2007, Vista birdie123 Excel 0 08-18-2012 12:02 PM
Excel 2007 to Word 2007 Labels mail merge not working rec Mail Merge 0 07-31-2010 10:45 AM
Why is listrows.add not working in excel 2007? HELP!! With Outlook 2007. STILL not working jbiggs Office 1 02-12-2009 09:02 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft