VBA adding values to new table rows

[duteberta]

This code is working perfectly for adding values to one column in NEW table rows, But I wanted to add a 2nd value in the same newly created table row. But my syntax is bad- can you help me?

' Add RECORD ROW to table

Sub AddRecordToTable()

    Dim ws As Worksheet
    Dim newRow As ListRow
    
    Set ws = ActiveSheet

        With ws.ListObjects("tbl_BANKS")
            .ListRows.Add(1).Range(.ListColumns("ID").Index) = GUID
        
            ' How do I add a 2nd value such as: Also add the value "Smith" in the column "LastName"
        
        
        End With


End Sub

 

[Domenic]

Try the following...

' Add RECORD ROW to table

Sub AddRecordToTable()

    Dim ws As Worksheet
    Dim newRow As ListRow
    
    Set ws = ActiveSheet

    With ws.ListObjects("tbl_BANKS")
        Set newRow = .ListRows.Add(1)
        newRow.Range(.ListColumns("ID").Index) = GUID
        newRow.Range(.ListColumns("LastName").Index) = "Smith"
    End With

End Sub

Hope this helps!

 

[duteberta]

WOW perfect- thanks!!!!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!

 

[duteberta]

You're very welcome, glad I could help.

Cheers!

Oops when I started using it in my solution it is creating the new rows at the bottom of the table instead the top. Is there an easy way to modify the code to that the new row is at the top?

 

[duteberta]

Oops when I started using it in my solution it is creating the new rows at the bottom of the table instead the top. Is there an easy way to modify the code to that the new row is at the top?

It works in my my test sheet but when I added that to my real solution (which combines other rules) it doesn't work. Thats on me - I'll figure it out. Thanks for your help though!

 

[Domenic]

Are you still having problems? If so, are you getting an error? And if so, which error, and on which line?

Oops when I started using it in my solution it is creating the new rows at the bottom of the table instead the top. Is there an easy way to modify the code to that the new row is at the top?

The first argument of the Add method of the ListRows object specifies the position at which the new row should be inserted relative to the first/top row. So if you've set that first argument to 1, as per your example, it should do so. Did you?

 

[duteberta]

It works on my test workbook. But when I combine it with 7 other subs on the same sheet weirdness ensues. I will need to have someone eventually optimize my entire solution so that things are clean and efficient

 

[Domenic]

Okay, sounds good, cheers!