MS Function creation

  • Thread starter Thread starter stefy
  • Start date Start date
S

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
 
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)
 
Bob Phillips said:
Why does it need to 'ask'? A formula such as

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

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

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?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
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?
Click to expand...

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.
 
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)
 
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)
Click to expand...

Thanks Bob, I'll work around it!

Stefy
 
Back
Top