Hi chirayu,
All 3 questions are somewhat related, so I'll try to cover them all in explanation.
First, how the code works. We limit the amount of work the code needs to do, and prevent errors due to the N/A by using the SpecialCells method to limit our range down. The SpecialCells method is the same dialogue you see in a workbook if you hit Ctrl+G, and click the 'special button'. One VB note when dealing with SpecialCells, is that if no cells match your criteria, an error will occur, and that can mess people up sometimes.
So, we've limited the range of cells we want to look at. Next, we want to look at each cell, and see how it compares the newValue.
Let's say that oldValue = 5, and newValue = 10. We want to end up with newValue. So, formula will be:
oldValue - oldValue + newValue = newValue
However, we want to capture the 'change' and cause it to step incrementally. So, we put that portion in parenthesis, watching our signs
oldValue - (oldValue - newValue) = newValue
Next, we want this change to move incrementally. This is where the i = 1 to upLimit comes in. We multiply the parenthesis by some fraction, will will for from ~ 0 to 100%, causing our oldValue to 'slowly' reach newValue.
DataPig is smoother, because he's dealing all with arrays. This cuts down the # of times VB is reading/writing to the workbook, which improves speed. Also, by using an array, he can modify
all the values, and the update the values in workbook
at the same time. Think of it as doing a paste onto the range, as opposed to doing each cell 1 at a time.
Now, the problem with #n/A. Since DataPig uses an array, we can't use SpecialCells, because we want to deal wiht the whole range. So, we need to add in an If...Then statement to check our values before trying to do math on them. Here's a re-write of DataPig's code, which works in your workbook.
Code:
Sub Chart1Change()
Const OldData As String = "C6:N6" '<<Change this Range
Const NewData As String = "C7:N7" '<<Change this Range
Call AnimateChart(OldData, NewData)
End Sub
Function AnimateChart(OldDataSet As String, NewDataSet As String)
Dim NewData As Variant
Dim OldData As Variant
Dim AnimationArray As Variant
Dim OldPoint As Long
Dim NewPoint As Long
Dim x As Integer
Dim i As Integer
Dim p As Double
NewData = ActiveSheet.Range(NewDataSet).value
OldData = ActiveSheet.Range(OldDataSet).value
AnimationArray = ActiveSheet.Range(NewDataSet).value
For i = 1 To 15
'Luke M, simplifying this line mathematically
'p = 100 / 100 / 15 * i
p = i / 15
'Luke M, not counting numbers, counting # of data points
For x = 1 To UBound(NewData)
'Luke M, ignoring non-numbers
If TypeName(OldData(1, x)) <> "String" Then
OldPoint = OldData(1, x)
NewPoint = NewData(1, x)
AnimationArray(1, x) = OldPoint - (OldPoint - NewPoint) * p
End If
Next x
Range(OldDataSet).value = AnimationArray
DoEvents
Next i
End Function
