0

so this is a general question, Im sure this has been asked before, but i cant find much help.

In Excel having a range in a worksheet say B6:F12 that are all the results of calculations I am having a problem with the Worksheet_Calculate() subroutine

my problem is that with this VERY generalized code for the worksheet_calculate subroutine

Private Sub Worksheet_Calculate()
     Dim aCell as Range
     Dim aRange as Range
     Set aRange = Range(B6:B12)
     For Each aCell in aRange
          SomeFunction(aCell)
     Next
End Sub

any calculation that happens in the Range C6:F12 causes the iteration through aRange to happen and unneeded calls to SomeFunction(aCell).

How can I make this scenario only happen when there is a calculation performed on the range B6:B12 ?

Chris
  • 443
  • 1
  • 5
  • 13
  • you may have to be a bit more specific. Maybe show samples from your data. When is a calculation performed? In your code you have `B6:B12` but in your question you have `C6:F12` and `B6:F12`. – ShanayL Sep 06 '17 at 13:17
  • Are you trying to run the function only if there is a value in Range`B6:B12`? If it is what you are trying, use `If aCell <> "" Then`or [on cell or range change](https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) – danieltakeshi Sep 06 '17 at 13:26
  • separate calculations are performed in all cells B6:F12, so that is 35 different calculations. When the calculations that are in B6:B12 happen i want SomeFunction() to be called. As it sits any time ANY calculation anywhere on the worksheet is called, the SomeFunction() is called via the iteration. – Chris Sep 06 '17 at 13:48

1 Answers1

0

Add this before you start your FOR loop:

If Not Application.Intersect(ActiveCell, aRange) Is Nothing Then

This will only trigger the FOR loop if you are in the given range

EDIT
I have the code setup like below and it seems to work for me:

Private Sub Worksheet_Calculate()
    Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet8")
    Dim aRange As Range: Set aRange = oWS.Range("F6:F10")
    If ActiveSheet.Name = oWS.Name Then
        If Not Application.Intersect(ActiveCell, aRange) Is Nothing Then
            MsgBox "In range"
        End If
    End If
End Sub
Zac
  • 1,924
  • 1
  • 8
  • 21
  • Is ActiveCell a protected variable that is a part of worksheet_calculate? or do i have to declare it? – Chris Sep 06 '17 at 19:43
  • sorry this ends up with object error 1006, as i am creating a value in a cell on another worksheet as well as creating a worksheet sometimes if one doesnt exist, and im assuming when it creates the worksheet and/or creates the value in the cell it moves the active cell and cause this to have issues. – Chris Sep 06 '17 at 20:09