I reformatted a range of Sheets("Records")
in a workbook as a Table (named "RecordsTable")
to make it easier to do INDEX(MATCH,MATCH)
functions for generating reports.... but now I screwed up my looping routine for filling that range from the input on Sheets("FORM")
.
It used to be:
Set r = Sheets("Records").Range(A & Rows.Count).End(x1Up).Offset(1, 0)
i = 0
For Each c In Range("dataRange")
'dataRange is a list of cells to reference from the FORM input sheet
r.Offset(0, i).Value = Worksheets("FORM").Range(c)
i = i + 1
Next
However this code is now selecting the first row at the END of "RecordsTable"
(row 501, as I defined 500 rows in my table) and inserting the data there.
I tried to change it to this:
Set r = Sheets("Records").ListObjects("RecordsTable").DataBodyRange("A" & Rows.Count).End(x1Up).Offset(1, 0)
i = 0
For Each c In Range("dataRange")
r.Offset(0, i).Value = Worksheets("FORM").Range(c)
i = i + 1
Next
But this code is still selecting row 501 and making that row part of "RecordsTable"
.
How can I properly Set "r" to =
the first empty row in "RecordsTable"
?
For reference, Column "A" in "RecordsTable"
has the header [INV #]
. Also, when I step into the "Set r = ..."
line, Rows.Count
is returning a value of 1million+ (ie, total rows on the sheet) - if I understand this correctly, I want it to return a value of 500 (ie, total rows in table) - is that correct?
EDIT
"dataRange"
is a single column list of cell references (I do have them labeled in column B, as @chrisneilsen suggest:
A
J6
Y6
J8
J10
Y8
etc.
They are the cells on Sheets("FORM")
that I need to pull data from and populate into my table, in the order indicated in "dataRange"
.
Assuming you really have a Table, adding data to a Table (ListObject) using it's properties and methods:
Sub Demo()
Dim lo As ListObject
Dim c As Range
Set lo = Worksheets("Records").ListObjects("RecordsTable")
For Each c In Sheets("V").Range("dataRange")
If Not lo.InsertRowRange Is Nothing Then
lo.InsertRowRange.Cells(1, 1) = Sheets("FORM").Range(c)
Else
lo.ListRows.Add.Range.Cells(1, 1) = Sheets("FORM").Range(c)
End If
Next
End Sub
Note: looping a range on sheet V
and using that as a pointer to data on sheet FORM
, copied from your answer - I'm assuming you know what you are doing here
Based on OP comment, adding data a single new row
Sub Demo()
Dim lo As ListObject
Dim c As Range, TableRange As Range
Dim i As Long
Set lo = Worksheetsheets("Records").ListObjects("RecordsTable")
If Not lo.InsertRowRange Is Nothing Then
Set TableRange = lo.InsertRowRange
Else
Set TableRange = lo.ListRows.Add.Range
End If
i = 1
For Each c In Sheets("V").Range("dataRange")
TableRange.Cells(1, i) = Sheets("FORM").Range(c)
i = i + 1
Next
End Sub
Note, this assumes that the order of the table columns is the same as the order of dataRange
. It may be better to include table field names in dataRange
to avoid any mismatch issues
As mentioned in updated OP, if column labels are in the next column, replace the For
loop with this (and add Dim r as Range, col as long
to declarations)
For Each c In Sheets("V").Range("dataRange")
If Not c = vbNullString Then
Set r = Worksheets("FORM").Range(c.Value)
col = lo.ListColumns(c.Offset(, 1).Value).Index
TableRange.Cells(1, col) = r.Value
End If
Next