Function Help Please
 

I'm using this in Sheet2 to populate values into columns 12 and 13:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Long
R = Target.Row

If Target.Column = 11 Then
Select Case Cells(R, 11)
Case "Data1"
Cells(R, 12) = "AX1035"
Cells(R, 13) = "Outsourced"
Case "Data2"
Cells(R, 12) = "AX1055"
Cells(R, 13) = "Managed"
' etc., etc.
End Select
End If

End Sub

Then I created this Module that uses a custom formula to perform
calculations in column 7 (also on Sheet2):

Public Function RevCal(Test As String, Number As Double) As Double
Application.Volatile

Select Case Test
Case "Acct1"
t = 15
Case "Acct2"
t = 11.5
'etc.
End Select
RevCal = Round(Number / 30, 2) * t

End Function

Before I added the module the values in columns 12 and 13 would
populate as soon as I selected a value from a drop-down box, but now in
order for the values to populate I have to type it in manually (plus it
runs really slow). If I get rid of the Module it works fine again...

Can someone help me figure out what I'm doing wrong?

Thanks,
Dan

Function Help Please
 

Great suggestion... That fixed it.

Thank You,
Dan

Alok wrote:
Your Worksheet_change routine is calling itself.
To avoid this set
Application.EnableEvents = False
before making any change (even if the change is made in a different
column,row)
and turn the
Application.EnableEvents = True
after making the changes.

"Dan R." wrote:

I'm using this in Sheet2 to populate values into columns 12 and 13:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Long
R = Target.Row

If Target.Column = 11 Then
Select Case Cells(R, 11)
Case "Data1"
Cells(R, 12) = "AX1035"
Cells(R, 13) = "Outsourced"
Case "Data2"
Cells(R, 12) = "AX1055"
Cells(R, 13) = "Managed"
' etc., etc.
End Select
End If

End Sub

Then I created this Module that uses a custom formula to perform
calculations in column 7 (also on Sheet2):

Public Function RevCal(Test As String, Number As Double) As Double
Application.Volatile

Select Case Test
Case "Acct1"
t = 15
Case "Acct2"
t = 11.5
'etc.
End Select
RevCal = Round(Number / 30, 2) * t

End Function

Before I added the module the values in columns 12 and 13 would
populate as soon as I selected a value from a drop-down box, but now in
order for the values to populate I have to type it in manually (plus it
runs really slow). If I get rid of the Module it works fine again...

Can someone help me figure out what I'm doing wrong?

Thanks,
Dan