% Change calculation

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

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
>

'/=============================================/
Sub Variance1()
Dim Actual As Range, std As Range
Dim exptype As String

On Error GoTo exit_Sub

exptype = _
Application.InputBox(Prompt:="Enter e if expense type, " & _
"else blank.", Type:=2)

Set Actual = _
Application.InputBox(Prompt:="Select cell for Actual data.", _
Type:=8)

Set std = _
Application.InputBox(Prompt:="Select cell for Standard data.", _
Type:=8)

If exptype = "e" Then
ActiveCell.Formula = "=-(" & Actual.Address & "/" & std.Address & "-1)"
Else
ActiveCell.Formula = "=" & Actual.Address & "/" & std.Address & "-1"
End If

Selection.Style = "Percent"

exit_Sub:
On Error Resume Next
Set Actual = Nothing
Set std = Nothing
Exit Sub

End Sub
'/=============================================/

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"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
>
>

Thxs a lot - what should I amend to get a relative formula instead of
an absolute formula as is the case
thxs
Rowan Drummond wrote:
> 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
> >

You can change the RowAbsolute and ColumnAbsolute values of the Address eg:

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(0, 0) _
& "/" & std.Address(0, 0) & "-1)"
Else
ActiveCell.Formula = "=" & actual.Address(0, 0) & _
"/" & std.Address(0, 0) & "-1"
End If

End If

Selection.Style = "Percent"

End Sub

Regards
Rowan

al wrote:
> Thxs a lot - what should I amend to get a relative formula instead of
> an absolute formula as is the case
> thxs