% variance calculation

  • Thread starter Thread starter al
  • Start date Start date
A

al

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
 
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
 
Thxs a lot Patrick !!

Patrick said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

% variance calculation - amended 1
% Change calculation 4
why won't this work 2
Geometric progression in VBA 10
Linking Cells Macro 3
Data samples 3
create a macro with the match lookup_array is changing everyday 3
"=TEXT(RC[-1],"MMM")" 4

Back
Top