Learning Loops and List Objects

[CascadeDiver]

Hi all.

I'm trying to learn some basics on looping and List Objects.
I've created a simple loop that displays data from a selected range into a new field. It works just fine however I think there is a way I can replace the .Offset using ListRow but I'm not sure how. The reason for wanting to do away with the .Offset is for the ability to move the tables elsewhere.

Option Explicit
Sub Load_Data()
Dim MainTable As ListObject
Set MainTable = Sheet1.ListObjects("Main_Table")
Dim RefTable As Range
Set RefTable = Sheet1.ListObjects("Review_Table").ListRows(1).Range
Dim SelectedRow As Long
SelectedRow = ActiveCell.Row

Dim EntryCol As Integer
For EntryCol = 1 To 3
    RefTable.Cells(1, EntryCol).Value = MainTable.Range(SelectedRow, EntryCol).Offset(-6, 0).Value
Next EntryCol

End Sub

As the photo shows. When a cell in the MainTable is selected the data repopulates in the RefTable

 

[John_w]

Rather than looping through the 3 columns, you can copy the whole table row in one statement.

Public Sub Load_Data2()

    Dim MainTable As ListObject
    Dim RefTable As ListObject
    Dim MainTableRow As Range
    
    Set MainTable = Sheet1.ListObjects("Main_Table")
    Set RefTable = Sheet1.ListObjects("Review_Table")
    
    If Not Intersect(ActiveCell, MainTable.DataBodyRange) Is Nothing Then
        Set MainTableRow = MainTable.ListRows(ActiveCell.Row - MainTable.DataBodyRange.Row + 1).Range
        MainTableRow.Copy RefTable.DataBodyRange
    Else
        MsgBox "Active cell is not in Main_Table data"
    End If
    
End Sub

 

[CascadeDiver]

Rather than looping through the 3 columns, you can copy the whole table row in one statement.

Works Like a charm. Thanks

 

[CascadeDiver]

Attempting to use a formula in one of the columns in the MainTable. The RefTable shows the formula and not the cell value.

I tried substituting with

    MainTableRow.Copy
    RefTable.DataBodyRange.PasteSpecial xlPasteValues

However the active selection immediately highlights the RefTable yielding anything in the MainTable unselectable.

Is there a way around this?

 

[RoryA]

For just values, you could use a straight assignment like this:

Sub Load_Data()
Dim MainTable As ListObject
Set MainTable = Sheet1.ListObjects("Main_Table")
Dim RefTable As Range
Set RefTable = Sheet1.ListObjects("Review_Table").ListRows(1).Range
Dim SelectedRow As Range
On Error Resume Next
Set SelectedRow = Intersect(ActiveCell.EntireRow, MainTable.DataBodyRange)
On Error GoTo 0
If Not SelectedRow Is Nothing Then RefTable.Cells(1).Resize(1, SelectedRow.Columns.Count).Value = SelectedRow.Value
End Sub

 

[CascadeDiver]

For just values, you could use a straight assignment like this:

That worked indeed. Thanks. Now I'll do some reading on what .Resize represents so I'll have a sound footing going forward.