Solved: getting rid of a "If" + improving my code

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?



[VBA]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[/VBA]


thanks!
Alex

You can get rid of the multiple range references by using a With clause.

As to no loop, undoubtedly, but it is very difficult to ascertain what you are doing to work out the formula. For instance, you divide by 1!

indeed. this was corrected in the meantime ;)

I'll check what to do with the "with" clause.

really appreciate your help.

+++

as far as i can tell these two equations are exactly the same.


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))


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)))


i starts at 1 and gose up so your adding a positive to a positive, with makes

Cells(33 + i, 10)=Cells(i + 33, 10)

there is apsolutely no diffranec in the first part of the equeations

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) + (Range("Time_Steps_Increments").Cells(i + 1) - (Round - 1)) * ((Range("Historical_Data").Cells(Round + 1)


the first pare here is only called uner the spechel curcumstance that Round = 1

- Range("Historical_Data").Cells(1)) / (Round))

the first pare here is only called uner the spechel curcumstance that Round = 1 so it can be rewiten to be


- Range("Historical_Data").Cells(Round)))

which matches the second equation

- Range("Historical_Data").Cells(Round)))

so in stead of an if statment you can simply use

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)))

here i how to do this with the with statment

[vba]Dim CurrTest, Round As Variant
Dim i As Integer

with Range("Historical_Data")
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
Cells(i + 33, 10) = .Cells(Round) + (Range("Time_Steps_Increments").Cells(i + 1) - (Round - 1)) * ((.Cells(Round + 1) - .Cells(Round)))

Next i
End with[/vba]

although i would go with this

[VBA]Dim CurrTest, Round As Variant
Dim i As Integer, timeStep As Range, Histor As Range

Set Histor = Range("Historical_Data")
Set timeStep = Range("Time_Steps_Increments")

For i = 1 To timeStep.Count - 1

Set CurrTest = timeStep.Cells(i + 1)
'Match = Application.Match(CurrTest, Range("Test"))
Round = Application.RoundUp(CurrTest, 0)

'Cells(i + 33, 11) = Round
Cells(i + 33, 10) = Histor.Cells(Round) + (timeStep.Cells(i + 1) - (Round - 1)) * ((Histor.Cells(Round + 1) - Histor.Cells(Round)))

Next i[/VBA]

Please insert line breaks in long lines of code to remove the need for scrolling.

You really shouldn't use Round for a variable name.

It's not only a workshee function available in VBA with WorksheetFunction but it's also an actual VBA function.