How to access cells outside a table pased to a macro?

[JenniferMurphy]

I am working on a macro that will process data in a table. It also needs to access data in cells in rows above the table. Here's the code so far.

Sub WtdRtg()
Const rnTable As String = "Tbl"   'The name of the main table
Dim TblData
TblData = Range(rnTable).Value
End Sub

How can I access the contents of the cell that is 3 rows above the cell in TblData(2,3)?

If TblData(2,3) is cell F7, then I want the contents of F3.

I know I could assign a named range to the cells in row 3, but a named range (eg C3:H3) does not automtically expand if I add a column, whereas the table range does.

Thanks

 

[Peter_SSs]

How can I access the contents of the cell that is 3 rows above the cell in TblData(2,3)?

If TblData(2,3) is cell F7, then I want the contents of F3.

Not exactly sure what you want as F3 is 4 rows above F7 but it could be along these lines.

MsgBox Range(rnTable).Cells(2, 3).Offset(-4).Value

 

[JenniferMurphy]

Not exactly sure what you want as F3 is 4 rows above F7 but it could be along these lines.

MsgBox Range(rnTable).Cells(2, 3).Offset(-4).Value

Perfect. Thank you.

I should have thought of Offset. I always think of it as a worksheet function. I forget about it in VBA.

Sorry about the confusion about the number of rows. It was just an example.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.