Enter value in cell based on first cell in named range

[ChristineJ]

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.

 

[jasonb75]

I had to read that a few times before I got it (or at least think I got it)
1

Range("Test")(1, 1).Offset(2, -1).Value = Range("Test")(1, 1).Row - 2

2

Range("Test")(1, 1).Offset(1, -1).Value = Range("Test")(1, 1).Column - 2

 

[Fluff]

How about

Sub ChristineJ()
   With Range("Test")
      .Offset(, -1).Resize(1, 1).Value = .Row - 2
      .Offset(1, -1).Resize(1, 1).Value = .Column - 2
   End With
End Sub

This assumes that Test is workbook scope, or it's on the active sheet

 

[ChristineJ]

I had to read that a few times before I got it (or at least think I got it)
1

Range("Test")(1, 1).Offset(2, -1).Value = Range("Test")(1, 1).Row - 2

2

Range("Test")(1, 1).Offset(1, -1).Value = Range("Test")(1, 1).Column - 2

Thanks for deciphering it! It just about works - results are fine, one appears on the 3rd row of the range rather than the first. Thanks for the help!

 

[ChristineJ]

How about

Sub ChristineJ()
   With Range("Test")
      .Offset(, -1).Resize(1, 1).Value = .Row - 2
      .Offset(1, -1).Resize(1, 1).Value = .Column - 2
   End With
End Sub

This assumes that Test is workbook scope, or it's on the active sheet

That does it! Thanks so much.

 

[Fluff]

Glad we could help & thanks for the feedback.