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

[choubix]

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

[Bob Phillips]

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!

[choubix]

indeed. this was corrected in the meantime

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

really appreciate your help.

+++

[figment]

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]

[mdmackillop]

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

[Norie]

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.