% Change calculation
Have a look at application.inputbox in VBA help.
Sub Variance1()
Dim actual As Range
Dim std As Range
Dim exptype As String
exptype = InputBox("Enter e if expense type, else blank.")
On Error Resume Next
Set actual = Application.InputBox( _
prompt:="Select cell for Actual data.", Type:=8)
Set std = Application.InputBox( _
prompt:="Select cell for Standard data.", Type:=8)
On Error GoTo 0
If Not actual Is Nothing And Not std Is Nothing Then
If exptype = "e" Then
ActiveCell.Formula = "=-(" & actual.Address _
& "/" & std.Address & "-1)"
Else
ActiveCell.Formula = "=" & actual.Address & _
"/" & std.Address & "-1"
End If
End If
Selection.Style = "Percent"
End Sub
Hope this helps
Rowan
al wrote:
Can anybody help me with this macro:
Sub Variance1()
Dim exptype
exptype = InputBox("Enter e if expense type, else blank.")
Dim Actual
Actual = InputBox("Select cell for Actual data.")
Dim Std
Std = InputBox("Select cell for Standard data.")
If exptype = "e" Then
ActiveCell.Formula = "=-(Actual/Std-1)"
Else
ActiveCell.Formula = "=Actual/Std-1"
End If
Selection.Style = "Percent"
End Sub
I want Actual & Std to be the cell address of my selected cells
Am new to excel & not familiar with input box, specially cell addresses
Thxs
Al
|