Error during inserting a new row in Data Table

[inddon]

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.....

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

 

[vletm]

inddon
Where have You find that kind of code?
I've used code like below.
It adds row ...

    With Sheets("Sheet1").ListObjects("List").ListRows.Add
        .Range(1) = Now
        .Range(2) = "txt1"
        .Range(3) = "txt2"
    End With

 

[inddon]

inddon
Where have You find that kind of code?
I've used code like below.
It adds row ...

    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.")

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

 

[vletm]

inddon
You could check eg next link:

The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[inddon]

inddon
You could check eg next link:

The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Thank you Ninja