ChristineJ
Wellknown Member
 Joined
 May 18, 2009
 Messages
 602
 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.