0

I'm trying to create a small table that you can double click on any cell and filter a second table based on the values in row 1 (AYEAR) and column B (AMONTH) to return the breakdown of the total value from my original table. How do I express this using R1C1 reference style?

ACLICK = Activecell.Address
AMONTH = RC2.Value
AYEAR = R1C.Value

What am I doing wrong?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Frinkenstein
  • 45
  • 7

1 Answers1

2

It needs to always look at row 1 for AYEAR and column B for AMONTH no matter which cell I'm double clicking in... EG: If I double click in J9 it needs to look at J1 for AYEAR and B9 for AMONTH

Simply:

AMONTH = Range("B" & ActiveCell.Row).Value ' or Cells(ActiveCell.Row, "B")
AYEAR = Cells(1, ActiveCell.Column).Value

Original answer:

IIUC, you're looking for Offset, and there's no need to use .Address or even think about R1C1 notation.

AMONTH = ActiveCell.Offset(,1).Value
AYEAR = ActiveCell.Offeset(1).Value
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • It needs to always look at row 1 for AYEAR and column B for AMONTH no matter which cell I'm double clicking in. I see what you've done and that's very helpful for future use. EG: If I double click in J9 it needs to look at J1 for AYEAR and B9 for AMONTH. – Frinkenstein Mar 15 '22 at 20:03
  • @Frinkenstein - oh ok, see the edit. – BigBen Mar 15 '22 at 20:16
  • 1
    Legend, worked like a charm! My fault for not explaining myself properly. Thank you sir. – Frinkenstein Mar 15 '22 at 20:23