ChristineJ
Well-known Member
- Joined
- May 18, 2009
- Messages
- 761
- Office Version
- 365
- Platform
- Windows
I have a named range called "Test" that is 6 rows high and 6 columns wide. It can appear anywhere on a worksheet between A1:Z400.
=ADDRESS(MIN(ROW(Test)),MIN(COLUMN(Test)),4) gives me the cell reference of the first cell in the range. Let's say that cell reference is G8.
Rather than formulas, I'd like to use VBA to return two values:
1. In the cell to the left of the first cell in the Test range (F8 in the case where the first cell in the range is G8), return the number that is two rows less than the number of rows in the first cell in the range. If that first cell is G8, the number in F8 would be 6.
2. In the cell to the left of the first cell in the Test range and one row down (F9 in the case where the first cell in the range is G8), return the number that is two columns less than the number of columns in the first cell in the range. If that first cell is G8, the number in F9 would be 5.
The first cell in the range will vary and will not always be G8. Thanks.
=ADDRESS(MIN(ROW(Test)),MIN(COLUMN(Test)),4) gives me the cell reference of the first cell in the range. Let's say that cell reference is G8.
Rather than formulas, I'd like to use VBA to return two values:
1. In the cell to the left of the first cell in the Test range (F8 in the case where the first cell in the range is G8), return the number that is two rows less than the number of rows in the first cell in the range. If that first cell is G8, the number in F8 would be 6.
2. In the cell to the left of the first cell in the Test range and one row down (F9 in the case where the first cell in the range is G8), return the number that is two columns less than the number of columns in the first cell in the range. If that first cell is G8, the number in F9 would be 5.
The first cell in the range will vary and will not always be G8. Thanks.