Hlookup Code Improvement Suggestions

[Guest]

Howdy,

The following code works, it just does not appear to be very efficient. It
makes use of several dynamic ranges. Any suggestions how to speed it up?

If Range("current_num") > 0 Then
For i = 1 To Range("current_num")
If i = 1 Then
LocRate = Range("year_dist_corner").Offset(0, -1)
Else
LocRate =
Range("Year_Dist_Corner").Offset(Application.RoundUp(Range("Length") / _
(Range("current_num") - 1) * i, 0) - 1, -1)
End If
Rate = LocRate * Application.HLookup(Range("current_period"),
Range("History_Table"), 8) _
+ LocRate * Application.HLookup(Range("current_period"),
Range("History_Table"), 8) _
* 0.5 * (Rnd - 0.5)
Range("NspMon").Cells(1, 1).Offset(-17 + i, k - 1) = Rate
Next
End If

TIA,
Tim

 

[Fredrik Wahlgren]

Howdy,

The following code works, it just does not appear to be very efficient. It
makes use of several dynamic ranges. Any suggestions how to speed it up?

If Range("current_num") > 0 Then
For i = 1 To Range("current_num")
If i = 1 Then
LocRate = Range("year_dist_corner").Offset(0, -1)
Else
LocRate =
Range("Year_Dist_Corner").Offset(Application.RoundUp(Range("Length") / _
(Range("current_num") - 1) * i, 0) - 1, -1)
End If
Rate = LocRate * Application.HLookup(Range("current_period"),
Range("History_Table"), 8) _
+ LocRate * Application.HLookup(Range("current_period"),
Range("History_Table"), 8) _
* 0.5 * (Rnd - 0.5)
Range("NspMon").Cells(1, 1).Offset(-17 + i, k - 1) = Rate
Next
End If

TIA,
Tim

I haven't looked at the code in detail. Here's a link that explains how to
optimize performance when dealing with dynamic ranges:
http://www.decisionmodels.com/optspeedf.htm

/Fredrik

 

[Guest]

Fredrik,

Thanks for the information. I was aware of the Decisionmodels site, and
have relied on information posted there. My question was intended to be less
of how to improve the Application.Hlookup performance and more of is there
another way to approach the problem. For example, I could use the SS to
calculate the number, instead of doing it in VBA. I did not want to go down
that path with out a little feedback from the "VBA experts".

Regards,
Tim