% variance calculation
you have not expalined the difference between Std and Actual. However, take a
look at this...
Option Explicit
Sub Variance1()
Dim exptype As String
Dim Actual As Range
Dim Std As Range
exptype = InputBox("Enter e if expense type, else blank.")
Set Actual = Application.InputBox("Actual data.", "Select cell for ...",
Type:=8)
Set Std = Application.InputBox("Standard data.", "Select cell for ...",
Type:=8)
If exptype = "e" Then
Actual.Formula = "=-(Actual/Std-1)"
Else
Actual.Formula = "=Actual/Std-1"
End If
Actual.NumberFormat = "0.00%;-0.00%;-"
End Sub
"al" wrote:
Patrick can you 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
Patrick Molloy wrote:
two ways to fix this
Option Explicit
Sub Macro1()
Dim exptype As String
exptype = InputBox("Enter e if expense type.")
ActiveCell.FormulaR1C1 = _
"=IF(""" & exptype & """=""e"",-(RC[-1]/RC[-2]-1),RC[-1]/RC[-2]-1)"
End Sub
Sub Macro1_new()
Dim exptype As String
exptype = InputBox("Enter e if expense type.")
If UCase(exptype) = "E" Then
ActiveCell.FormulaR1C1 = "=-(RC[-1]/RC[-2]-1)"
Else
ActiveCell.FormulaR1C1 = "=(RC[-1]/RC[-2]-1)"
End If
End Sub
The secnd example is far easier to understand and this debug at some future
date
"al" wrote:
Can anybody help me to correct this macro:
Sub Macro1()
Dim exptype
exptype = InputBox("Enter e if expense type.")
ActiveCell.FormulaR1C1 =
"=IF(exptype=""e"",-(RC[-1]/RC[-2]-1),RC[-1]/RC[-2]-1)"
End Sub
Am getting error #NAME?
I do not want to refer exptype to any cell as it's only a general input
Thxs
|