MS Function creation

[stefy]

Hello,

I would need to create an (easy) function in MS Excel.
The function should give back the percentage variation between two cells.
The function should be "interactive", I mean, once called i want the
function asks to select the two cells.

Any suggestion?

Thanks,
Stefy

 

[Bob Phillips]

Why does it need to 'ask'? A formula such as

=(A1-B1)/B1 formatted as percentage allows you to nominate your cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[stefy]

Why does it need to 'ask'? A formula such as

=(A1-B1)/B1 formatted as percentage allows you to nominate your cells.

I would prefere a formula as Many times I need to apply it to different
datasheets: it would be faster i think.

Stefy

 

[Bob Phillips]

Stefy,

That is a formula.

How about a macro that, on selecting a certain cell (you tell us which) it
creates a formula in that cell (as I suggested earlier), but based upon two
cells that you select with the mouse?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stefy]

Stefy,

That is a formula.

How about a macro that, on selecting a certain cell (you tell us which) it
creates a formula in that cell (as I suggested earlier), but based upon two
cells that you select with the mouse?

It would be enough, but I'm not able to create such a macro :-((.
I'm able to create a macro with the macro recorder, and for my
acknowledgement no way to do this with it. Is it? Is there an easy way to do
in other way?

Thanks,
Stefy.

 

[Bob Phillips]

Stefy,

Here is some example code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oFirst As Range
Dim oSecond As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
Set oFirst = Application.InputBox( _
"Select the first cell to compare", _
"Range Select", _
Type:=8)
If Not oFirst Is Nothing Then
Set oSecond = Application.InputBox( _
"Select the second cell to compare", _
"Range Select", _
Type:=8)
If Not oSecond Is Nothing Then
With Target
.Formula = "=(" & oSecond.Address(False, False) & _
"-" & oFirst.Address(False, False) & _
")/" & oSecond.Address(False, False)
.NumberFormat = "0.0%"
End With
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[stefy]

Bob Phillips ha scritto:

Stefy,

Here is some example code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oFirst As Range
Dim oSecond As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
Set oFirst = Application.InputBox( _
"Select the first cell to compare", _
"Range Select", _
Type:=8)
If Not oFirst Is Nothing Then
Set oSecond = Application.InputBox( _
"Select the second cell to compare", _
"Range Select", _
Type:=8)
If Not oSecond Is Nothing Then
With Target
.Formula = "=(" & oSecond.Address(False, False) &
_ "-" & oFirst.Address(False, False) &
_ ")/" & oSecond.Address(False, False)
.NumberFormat = "0.0%"
End With
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanks Bob, I'll work around it!

Stefy