% 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 |
% 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 |
% Change calculation
'/=============================================/
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 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 |
% Change calculation
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 |
% Change calculation
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 |
All times are GMT +1. The time now is 03:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com