Excel VBA - Select a cell in a highlighted range

0
votes

Is it possible to select a specific cell in a range that changes depending on what cells are highlighted.

So if i Had; 

Range("C1").Value = Application.WorksheetFunction.Sum(Selection)

It would sum the entire highlighted area and put the value in C1. Is it possible to only select some cells in the highlighted area. I know it sounds dumb, i realise can just highlight the cells i need but this is just a simplified version of the problem I've got.

What i'm asking is, is there a way in code to say;

"In the highlighted range, select the cell that is 2 columns to the right and 4 columns down from the top left boundary of the range"

Thanks

3
excelvbarangeselected
You can try using for example Selection.Offset(1,2) It will take the Cells 1 Row down and 2 colums right from the current selection. - Shmukko

3 Answers

0
votes

The Code for your question:

"In the highlighted range, select the cell that is 2 columns to the right and 4 columns down from the top left boundary of the range"

Selection.Cells(1).Offset(4,2).Select
0
votes

in your case being Selection a Range you can use its methods/properties:

Range("C1").Value = Application.WorksheetFunction.Sum(Selection.Cells(5,3))

since Cells(5,3) reference a cell 2 columns to the right and 4 rows down offset the selection top-left one

0
votes

You may be able to use the Worksheet_SelectionChange Event and examine the Target reference.

For example paste this test code into some sheet class:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "A1" Then Exit Sub
    Range("A1").Value = WorksheetFunction.Sum(Target)
End Sub

Something like this - obviously you're going to have all kinds of checks in there for Errors and the likes.

I'd also look at some way of disabling the code since you're going to have Events firing all over the place. Depends on your requirements.