calculate a range

[MartinL]

Hi Guys

Can anyone help me solve this

I have a cell "Z12" on sheet1 that will change when an option is made using radio buttons.

What I need to do is recalulate a range "C5:L105" on sheet2 whenever this cell "Z12" changes

I'm guessing its

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

or

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

but how do I refer to the cell and then recalc the correct worksheet range?

 

[Jerry Sullivan]

The range Prize!C5:L150 has many links to external workbooks that I don't have, so it is difficult to test the problem you are having with that range needing 2 calculations in the one combination you describe.

Can you identify a specific Cell on that Sheet and explain what value it should have when going from 4 Weeks to 52 Weeks and what value you are seeing when you only calculate once?

 

[Jerry Sullivan]

Martin,

It's getting late here in California, so I need to wrap up for now.

If you only want to trigger the recalc when you change M12, (and not when you change other Cells on Sheet Market), the code below will work a little better.

I've left the second calculation in for now so this will still work for you.
I'll take another look at this tomorrow after seeing your reply.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M12")) Is Nothing Then
        Call CalcSheet
    End If
End Sub

Sub CalcSheet()
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Sheets("Market")
        .Range("R12:T12").Calculate
        .Range("Z12").Calculate
    End With
    
    With Sheets("Prize")
        .Range("M1:M2").Calculate
        .Range("C5:L150").Calculate
        .Range("C5:L150").Calculate 'can this be eliminated?
    End With

    Sheets("Market").Range("M15:N22").Calculate
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub