Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
% 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
% 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
% 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
% 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
% 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple calculation where values change to letters? | Excel Worksheet Functions | |||
Calculation options change arbitrarily | Excel Worksheet Functions | |||
the calculation tab being changed and you didn't change it? | Excel Discussion (Misc queries) | |||
why does the calculation summery not change | Excel Discussion (Misc queries) | |||
How do I make the now() static and not change with calculation? | Excel Worksheet Functions |