• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error during inserting a new row in Data Table

inddon

Member
Hello There,

I have the below Sub which inserts a new row at the end of the table.
In the sub, there is On Error Resume Next. When this line of codeis enabled all goes okay
But when I disable On Error Resume Next, it pops up with an error: 'Runtime error '438' Object doesn't support this property or error.

The error popsup at Set Maxrr = O_MstTbl_Transactions.....

Code:
Sub TestInsertData()
  Dim MstWSName_Transactions As Worksheet, MstTblName_Transactions As String, O_MstTbl_LastTransactionNumber As ListObject
  Dim Maxrr As Range, MaxSrNumber  As Integer

  'On Error Resume Next

  InitializeEnvironment
  'Set Master Worksheet and Master Table for Transactions
  Set MstWSName_Transactions = MstWB.Worksheets("Transactions")
  MstTblName_Transactions = "Tbl_Transactions"
  Set O_MstTbl_Transactions = MstWSName_Transactions.ListObjects(MstTblName_Transactions)

  'Insert a new row at the start of the table
  Set Maxrr = O_MstTbl_Transactions.DataBodyRange.Rows(O_MstTbl_Transactions.ListRows.Count)
  MaxSrNumber = Intersect(Maxrr.EntireRow, O_MstTbl_Transactions.ListColumns("Sr.").DataBodyRange).Value + 1

  O_MstTbl_Transactions.ListRows.Add (O_MstTbl_Transactions.ListRows.Count + 1)

  Set Maxrr = O_MstTbl_Transactions.DataBodyRange.Rows(O_MstTbl_Transactions.ListRows.Count + 1).Add

  'Keep adding values to the new row
  Intersect(O_MstTbl_Transactions.ListColumns("Sr.").DataBodyRange, Maxrr.Offset(1)).Value = MaxSrNumber
  Intersect(O_MstTbl_Transactions.ListColumns("Legal Entity Number").DataBodyRange, Maxrr.Offset(1)).Value = EntWSDE_Header.[FLegalEntityNumber]
End Sub

Could you please advice?

Thanks & regards,
Don


Capture.JPG
 

vletm

Excel Ninja
inddon
Where have You find that kind of code?
I've used code like below.
It adds row ...
Code:
    With Sheets("Sheet1").ListObjects("List").ListRows.Add
        .Range(1) = Now
        .Range(2) = "txt1"
        .Range(3) = "txt2"
    End With
 

inddon

Member
inddon
Where have You find that kind of code?
I've used code like below.
It adds row ...
Code:
    With Sheets("Sheet1").ListObjects("List").ListRows.Add
        .Range(1) = Now
        .Range(2) = "txt1"
        .Range(3) = "txt2"
    End With
Thank you for showing this option of inserting rows in table.

I am just following the existing kind of code that was written for Select, Insert, Update and Delete.

Your method seems to be good and I would like to apply it all through our VBA application the above 4 operations.

Instead, of giving fixed ranges (1), (2) or (3). I would like to give the Table Column Names instead. How can this be done?

Also if could kindly let me know the best approach for the other 3 operations (Select, Update and Delete) would be great.
For Select, Update and Delete the search key is Data Table Column Name ("Sr.")


Code:
example: 
   With O_MstTbl_Transactions.ListRows.Add
               ("Sr.") = "1"
               ("Legal Entity Number") = "Txt1"
               ("Transaction Type Code") = "Txt2"
               ("Column Name 4") = "Txt4"
               ...
               ...
    End With
Appreciate your help and look forward to hearing from you.


Regards,
Don
 
Top