# Selecting multiple individual cells for use in VBA

#### sabinesF

##### New Member
I would like to select individual, non-adjacent cells by mouse-click and use those cells as the range to be used within a macro. At the end of the calculation, I would like the result to appear in the status bar.

Currently, I can use the macro when the range consists of adjacent cells selected by either clicking and dragging the mouse or using shift+mouse-click. As one can do on the worksheet itself, I have tried to use ctrl+mouse-click to select non-adjacent cells, but receive an error message. I move around that by including "On Error Resume Next," but the cells selected using the control key are not included in the calculation.

How might I go about selecting non-adjacent cells to be used as the range?

Thank you.

For reference, the current code reads:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.StatusBar = "ESum: " & _

End Sub

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Dim sum As Double

sum = 0
For Each c In Target
sum = sum + Application.WorksheetFunction.Power(10, Val(c.Value) / 10)
Next
Application.StatusBar = "ESum: " & 10 * Log(sum)
End Sub``````

Thank you Vaskov, much appreciated. I can now use cells individually selected by ctrl+mouse-click or ctrl+arrows.

The logarithmic summation is not correct, coming out to over double the expected value. I do not know why this is, as the current equation yields the correct answer when carried out logically.

The equation worked in the original code, when I was unable to use the ctrl+mouse-click selection. For example, selecting a range of 9 values all equal to 50 would yield a result of 59.54242509. With the current code, it yields a value of 137.101500423064.
Similarly, if one cells is selected, the "ESum" value should be the value of the single selected cell. If a single cell is selected with a value of 50, the ESum should be 50. However, such a selection currently yields 115.129254649702.

Perhaps this is more of an arithmetic issue, bu I am not convinced.

The equation is effectively 10 * Log(SUM(10^(xi/10))) where x ranges from xi to xn.

The code currently reads as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Dim sum As Double

sum = 0
For Each c In Target
sum = sum + Application.WorksheetFunction.Power(10, Val(c.Value) * 0.1)
Next
Application.StatusBar = "ESum: " & (10 * Log(sum))
End Sub

Apparently Log() returns the natural logarithm not the base 10 one, so try this:

Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Dim sum As Double

sum = 0
For Each c In Target
sum = sum + Application.WorksheetFunction.Power(10, Val(c.Value) / 10)
Next
Application.StatusBar = "ESum: " & 10 * Application.WorksheetFunction.Log10(sum)
End Sub``````

That works perfectly, good catch. Thank you very much!

Replies
3
Views
258
Replies
5
Views
177
Replies
1
Views
288
Replies
6
Views
1K
Replies
3
Views
82

1,196,102
Messages
6,013,473
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back