0

I have a Workbook which has four sheets:

  • Settings
  • Product A
  • Product B
  • Overview

Settings sheet contains variables which are used by the other sheets for calculation.

Product A and Product B sheets are using different UDFs to calculate growth revenue and dropouts. We are using different UDFs because the formula to calculate growth and etc. is different for each product.

Overview presents the totals from Product A and Product B.

Now the problem is the following.

When I calculate sheet Product A it messes the values of Product B and vice versa.

Any ideas?

Edited:

UDF

Private Sub Workbook_SelectionChange(ByVal Target As Range)
    Calculate
End Sub
Public Function PSPGrowth(CurrentPos, DataColStart, CountryPSPNew, AvgDuration, Fee, Growth1, Growth2, Growth3, Growth4, Growth, CalcRange As Range)

  'recalculate on cell changes
Application.Volatile True
  'define variables
  Sum = 0
  PosCurrent = CurrentPos.Column()
  PSPNewRow = CountryPSPNew.Row()
  'calculate starting point
  PosStart = PosCurrent - AvgDuration

  If (PosStart <= DataColStart) Then
    PosStart = DataColStart
  End If

  'calculate duration
  Duration = 1 + PosCurrent - PosStart

  'loop new contracts
  For c = PosStart To PosCurrent
    'get new contracts
    NewContracts = Cells(PSPNewRow, c)
    If (NewContracts > 0) Then
        'get contract duration till current position
        ContractDuration = 1 + PosCurrent - c

        'calculate revenue growth according to contract duration
        If (ContractDuration < 13) Then
            'year 1
            cDuration = ContractDuration
            y1 = (1 + Growth1.Value / 12)

            Sum = Sum + (NewContracts * (1 + Growth1.Value / 12))

        ElseIf (ContractDuration > 12 And ContractDuration < 25) Then
            'year 2
            cDuration = ContractDuration - 12
            y1 = (1 + Growth1.Value)
            y2 = y1 * (1 + (cDuration * Growth2.Value / 12))

            Sum = Sum + (NewContracts * y2)

        ElseIf (ContractDuration > 24 And ContractDuration < 37) Then
            'year 3
            cDuration = ContractDuration - 24
            y1 = (1 + Growth1.Value)
            y2 = y1 * (1 + Growth2.Value)
            y3 = y2 * (1 + (cDuration * Growth3.Value / 12))

            Sum = Sum + (NewContracts * y3)

            'Sum = Sum + (NewContracts * (1 + Growth1.Value + Growth2.Value + Growth3.Value / 12))

        ElseIf (ContractDuration > 36 And ContractDuration < 49) Then
            'year 4
            cDuration = ContractDuration - 36
            y1 = (1 + Growth1.Value)
            y2 = y1 * (1 + Growth2.Value)
            y3 = y2 * (1 + Growth3.Value)
            y4 = y3 * (1 + (cDuration * Growth4.Value / 12))

            Sum = Sum + (NewContracts * y4)

    '        Sum = Sum + (NewContracts * (1 + Growth1.Value + Growth2.Value + Growth3.Value + Growth4.Value / 12))

        ElseIf (ContractDuration > 48) Then
            'year 5+
            cDuration = ContractDuration - 48
            y1 = (1 + Growth1.Value)
            y2 = y1 * (1 + Growth2.Value)
            y3 = y2 * (1 + Growth3.Value)
            y4 = y3 * (1 + Growth4.Value)
            y5 = y4 * (1 + (cDuration * Growth5.Value / 12))

            Sum = Sum + (NewContracts * y5)

            'Sum = Sum + (NewContracts * (1 + Growth1.Value + Growth2.Value + Growth3.Value + Growth4.Value + Growth5.Value / 12))
        Else
            Sum = Sum + 0
        End If
    End If
  Next

  'return result
  PSPGrowth = Sum * Fee.Value

End Function

Public Function PRODUCTAActive(CurrentPos, DataColStart, CountryBSNew, DropOutMonth, DropOutRate, CalcRange As Range)

  'recalculate on cell changes
  Application.Volatile True
  'define variables
  Sum = 0
  PosCurrent = CurrentPos.Column
  BSNewRow = CountryBSNew.Row()
  'calculate starting point
  PosStart = DataColStart
  If (PosStart <= DataColStart) Then
    PosStart = DataColStart
  End If
  'calculate duration
  Duration = 1 + PosCurrent - PosStart

  'loop new contracts
  For c = PosStart To PosCurrent
    'get new contracts
    NewContracts = Cells(BSNewRow, c)
    If (NewContracts > 0) Then
        'get contract duration till current position
        ContractDuration = 1 + PosCurrent - c

        'calculate revenue growth according to contract duration
        If (ContractDuration <= DropOutMonth) Then

            Sum = Sum + Round(NewContracts, 0)
        Else
            Sum = Sum + Round((NewContacts * (1 - DropOutRate)), 0)
        End If
    Else
        Sum = Sum + 0
    End If
  Next

  'return result
  PRODUCTAActive = Sum

End Function

Public Function PRODUCTAConvertionCalc(CurrentPos, DataColStart, CountryBSNew, ConvertionMonth, ConvertionRate, CalcRange As Range)

  'recalculate on cell changes
  Application.Volatile True
  'define variables
  Sum = 0
  PosCurrent = CurrentPos.Column
  BSNewRow = CountryBSNew.Row()
  'calculate starting point
  PosStart = DataColStart
  If (PosStart <= DataColStart) Then
    PosStart = DataColStart
  End If
  'calculate duration
  Duration = 1 + PosCurrent - PosStart

  'loop new contracts
  For c = PosStart To PosCurrent
    'get new contracts
    NewContracts = Cells(BSNewRow, c)
    If (NewContracts > 0) Then
        'get contract duration till current position
        ContractDuration = 1 + PosCurrent - c

        'calculate revenue growth according to contract duration
        If (ContractDuration = ConvertionMonth) Then

            Sum = Sum + Round(NewContracts * ConvertionRate, 0)
        Else
            Sum = Sum + 0
        End If
    Else
        Sum = Sum + 0
    End If
  Next

  'return result
  PRODUCTAConvertionCalc = Sum

End Function


Public Function PRODUCTACommissionCalc(CurrentPos, DataColStart, CountryBSNew, DropOutMonth, DropOutRate, ComY1, ComY2, PremiumPrice, CountryBSNewConv, ConvertionMonth, ConvertionRate, CalcRange1 As Range, CalcRange2 As Range)

  'recalculate on cell changes
  Application.Volatile True
  'define variables
  Sum = 0
  TotalCom = 0
  PosCurrent = CurrentPos.Column
  BSNewRow = CountryBSNew.Row()
  'calculate starting point
  PosStart = DataColStart
  If (PosStart <= DataColStart) Then
    PosStart = DataColStart
  End If
  'calculate duration
  Duration = 1 + PosCurrent - PosStart

  'loop new contracts
  For c = PosStart To PosCurrent
    'get new contracts
    NewContracts = Cells(BSNewRow, c)
    If (NewContracts > 0) Then
        'get contract duration till current position
        ContractDuration = 1 + PosCurrent - c

        'calculate revenue from new contracts
        If (ContractDuration <= DropOutMonth) Then
            If (ContractDuration <= 12) Then
               TotalCom = TotalCom + Round(NewContracts, 0) * ComY1 * PremiumPrice
            Else
               TotalCom = TotalCom + Round(NewContracts, 0) * ComY2 * PremiumPrice
            End If
        Else
            If (ContractDuration <= 12) Then
               TotalCom = TotalCom + Round((NewContacts * (1 - DropOutRate)), 0) * ComY1 * PremiumPrice
            Else
               TotalCom = TotalCom + Round((NewContacts * (1 - DropOutRate)), 0) * ComY2 * PremiumPrice
            End If
        End If

    Else
        TotalCom = TotalCom + 0
    End If
  Next

  ' converted contracts
  BSNewRow = CountryBSNewConv.Row()
   'loop new contracts
  For c = PosStart To PosCurrent
    'get new contracts
    NewContracts = Cells(BSNewRow, c)
    If (NewContracts > 0) Then
        'get contract duration till current position
        ContractDuration = 1 + PosCurrent - c

        'calculate revenue growth according to contract duration
        If (ContractDuration >= ConvertionMonth) Then
            If (ContractDuration <= 12) Then
               TotalCom = TotalCom + Round(NewContracts * ConvertionRate, 0) * ComY1 * PremiumPrice
            Else
               TotalCom = TotalCom + Round(NewContracts * ConvertionRate, 0) * ComY2 * PremiumPrice
            End If
        Else
           TotalCom = TotalCom + 0
        End If
    Else
        TotalCom = TotalCom + 0
    End If
  Next

  'return result
  PRODUCTACommissionCalc = TotalCom

End Function
pnuts
  • 58,317
  • 11
  • 87
  • 139
curiusX
  • 15
  • 4
  • 2
    Post the UDF please. I would suspect you have references to `Range`, `ActiveCell` or `Cells` which are defaulting to the active sheet. – Rory Nov 23 '15 at 10:26
  • @Rory just included the UDF – curiusX Nov 23 '15 at 10:41
  • Have you tried Worksheets(“SheetName”).Calculate? – Estevam Garcia Nov 23 '15 at 10:48
  • Your problem is in this line: `NewContracts = Cells(PSPNewRow, c)`. The `Cells` call here will always refer to the active sheet. I suspect you want: `NewContracts = CountryBSNew.Worksheet.Cells(PSPNewRow, c)` – Rory Nov 23 '15 at 10:50
  • @Rory Great It worked!!!! Please post is as an answer to accept it as correct! – curiusX Nov 23 '15 at 10:52

1 Answers1

0

Your problem is in this line: 

NewContracts = Cells(PSPNewRow, c)

The Cells call here will always refer to the active sheet. I suspect you want this:

NewContracts = CountryBSNew.Worksheet.Cells(PSPNewRow, c).Value
Rory
  • 32,730
  • 5
  • 32
  • 35