choubix
05-06-2008, 04:45 AM
hello!
first and foremost: thanks to all the poster to my previous question (sure they will be reading this one too! ;) )
I have written the following interpolation function.
Is there any better way of addressing the problem?
I think I am clumsy in my approach...
- is there a way to avoid the "If" ?
- is it possible to avoid calling "Range("Historical_Data").Cells" each time in the formula to keep it easy to read/understand?
Dim CurrTest, Round As Variant
Dim i As Integer
For i = 1 To Range("Time_Steps_Increments").Count - 1
Set CurrTest = Range("Time_Steps_Increments").Cells(i + 1)
'Match = Application.Match(CurrTest, Range("Test"))
Round = Application.RoundUp(CurrTest, 0)
'Cells(i + 33, 11) = Round
If Round = 1 Then
Cells(33 + i, 10) = Range("Historical_Data").Cells(Round) + (Range("Time_Steps_Increments").Cells(i + 1) - (Round - 1)) * ((Range("Historical_Data").Cells(Round + 1) - Range("Historical_Data").Cells(1)) / (Round))
Else
Cells(i + 33, 10) = Range("Historical_Data").Cells(Round) + (Range("Time_Steps_Increments").Cells(i + 1) - (Round - 1)) * ((Range("Historical_Data").Cells(Round + 1) - Range("Historical_Data").Cells(Round)))
End If
Next i
thanks!
Alex
first and foremost: thanks to all the poster to my previous question (sure they will be reading this one too! ;) )
I have written the following interpolation function.
Is there any better way of addressing the problem?
I think I am clumsy in my approach...
- is there a way to avoid the "If" ?
- is it possible to avoid calling "Range("Historical_Data").Cells" each time in the formula to keep it easy to read/understand?
Dim CurrTest, Round As Variant
Dim i As Integer
For i = 1 To Range("Time_Steps_Increments").Count - 1
Set CurrTest = Range("Time_Steps_Increments").Cells(i + 1)
'Match = Application.Match(CurrTest, Range("Test"))
Round = Application.RoundUp(CurrTest, 0)
'Cells(i + 33, 11) = Round
If Round = 1 Then
Cells(33 + i, 10) = Range("Historical_Data").Cells(Round) + (Range("Time_Steps_Increments").Cells(i + 1) - (Round - 1)) * ((Range("Historical_Data").Cells(Round + 1) - Range("Historical_Data").Cells(1)) / (Round))
Else
Cells(i + 33, 10) = Range("Historical_Data").Cells(Round) + (Range("Time_Steps_Increments").Cells(i + 1) - (Round - 1)) * ((Range("Historical_Data").Cells(Round + 1) - Range("Historical_Data").Cells(Round)))
End If
Next i
thanks!
Alex