Edit Question - KnowledgeHub

Title Body

Hopefully this is an easy one. I have a series of charts in MS Excel that point to data on the same worksheet. The data on the worksheet is calculated using a VBA function. When the data is updated by the VBA function the new numbers are not reflected in the charts that are pointing to them. I tried calling Application.Calculate, but that didn't do the trick. Any thoughts? UDPATE: I was able to duplicate this issue on a much smaller scale. Here's how: Create a new workbook Rename Sheet 1 to "Summary" Rename Sheet 2 to "Data" Open the Summary sheet in the VBA editor and paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Parent.Range("worksheetDate") = Target Then Application.CalculateFull End If End Sub Create a new VBA module Paste the following code into the new VBA module (I apologize - I can't get Stack Overflow to format this correctly for the life of me - this is the best I could get it to do): . Function getWeekValue (weekNumber As Integer, valuesRange As Range) As Integer Dim aCell As Range Dim currentDate As Date Dim arrayIndex As Integer Dim weekValues(1 To 6) As Integer currentDate = ThisWorkbook.Names("worksheetDate").RefersToRange.Value arrayIndex = 1 For Each aCell In valuesRange If month(currentDate) = month(ThisWorkbook.Sheets("Data").Cells( _ aCell.Row - 1, aCell.Column)) Then weekValues(arrayIndex) = aCell.Value arrayIndex = arrayIndex + 1 End If Next getWeekValue = weekValues(weekNumber) End Function . Modify the Data worksheet to match the following image: Select Cell B1 and name the range "work

Tags (comma-separated) Cancel