Seems like this should be a simple thing, and maybe it is, but I can't seem to find out how to do this. I want to keep track of which rows within the table certain cells are but I can't find a way to get the table row number rather than the worksheet row number.
Is it an excel table or just area on the spreadsheet that you have placed a border around?
It's an Excel table. I want to be able to refer to other cells in that cells row in the table using table.range.cells(x,y)
Ok, well let's assume your table is called "MyTable" and exists in "Sheet1":Code
- Sub tableNav()
- Dim tbl As ListObject
- Dim rw As Long
- Dim cl As Long
- Dim bodyCell As Range
- Dim headCell As Range
- Set tbl = Sheets("Sheet1").ListObjects("MyTable")
- If tbl.ListRows.Count > 0 And tbl.ListColumns.Count > 0 Then
- For rw = 1 To tbl.ListRows.Count
- For cl = 1 To tbl.ListColumns.Count
- Set headCell = tbl.HeaderRowRange.Cells(1, cl)
- Set bodyCell = tbl.DataBodyRange.Cells(rw, cl)
- Debug.Print "Row: " & rw & " | Column: " & cl & " (" & headCell.Value & ")"
- Debug.Print "Current Cell: " & bodyCell.Address & " = " & bodyCell.Value
- Debug.Print "----------------------"
- Next cl
- Next rw
- End If
- End Sub
That works thanks! Think I got hung up on finding a specific function rather than thinking outside the box.