Identifying a random Excel cell. 2

[AliJay]

Hi Guys,

I wonder if someone could help me with what should be a VERY simple solution but is beyond me! My problem is similar to some other questions posted here but none quite match.

Using Excel I want to be able to select a random cell using the mouse. I then want to run a macro which performs an operation on that cell. If the cell value were P45 the code would be for example:

Range (“P45”) = Range (“P45”) + 0.25

BUT how do I find the cell “address” to do the operation if the original cell is one that has been selected using a mouse and would be different every time?

Thanks so much in advance if you can help out.

AJ

 

[combo]

You can use Worksheet_SelectionChange(ByVal Target As Range) event procedure. The Target is a range selected. Target.Address will return the address, but it can only be necessary when action depends on selection. You can directly:
Target = Target + 0.25

combo

 

[SkipVought]

hi,

If the cell value were P45...

For clarity sake, you are referring to the cell reference, it seems, and HOPEFULLY, the cell value is NUMERIC if you are performing arithmetic.

If you happen to select a cell that does not contain an arithmetic value, you will get a Type missmatch error.

So I would suggest...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If IsNumeric(Target.Value) Then _
        Target.Value = Target.Value + 0.25
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AliJay]

Thank you so much - stars to you both.

Just finally: There is a list of Macros (TOOLS --> MACROS etc.) associated with the worksheet I am working on but the Private Sub is not on that list whereas any other Sub I have written is. So how do I go about activating the Private Sub?

Just experimenting when I take away the arguments (in this case "ByVal Target As Range") it does appear on the list. Sorry in advance for what is a basic question!

AJ

 

[combo]

It is an event-driven procedure, called automatically. So it requires known location and syntax.
In the Project Explorer window find the worksheet icon. Double-click it or right-click and select 'View code' to display the associated module. (Or simply in excel right-click sheet's tab and select 'View code', but this method is only for sheet level events.) From the left drop-down list (top of module) select 'Worksheet', from the right one event. The VBE environment will build an event procedure template for you - most secure way to avoid errors.

combo