For Each Next returns "Empty" when Data in Cell
 

I have written a function with the intention of iterating the function
(rather than the workbook) but am having difficulty pulling the information
through:

Public Function Iterative_LIBOR(Target As Double, Frequent As Double,
Periods_Range As Range, Swaps_Range As Range, Nominals_Range As Range,
LIBORs_Range As Range)

Cycle = 1
Freq = Frequent
DCF = 0

'Build the Discount, Period, LIBOR and Nominal Arrays

Dim Period_Array(1 To 100) As Variant

For Each ActualCell In Periods_Range.Cells

If ActualCell.Value 0.001 Then

Period_Array(Cycle) = ActualCell.Value

Else
End If

Cycle = 1 + Cycle

Next ActualCell

Although the Range Periods_Range is populated with data the "Watch" gives
each value as "Empty". Can any one help?

Thank you
--
Matt Roberts

For Each Next returns "Empty" when Data in Cell
 

I don't see anything technically wrong with your code if that is what you
want.

If you have a cell in Periods_Range that is greater than .001, then you
should have a value in your array.

You will have empty elements in the array corresponding to any cell that is
less than or equal to .001 or for any elements that exceed the number of
cells in Periods_Range up to the 100th element.

--
Regards,
Tom Ogilvy


"Matt Roberts" wrote in message
...
I have written a function with the intention of iterating the function
(rather than the workbook) but am having difficulty pulling the
information
through:

Public Function Iterative_LIBOR(Target As Double, Frequent As Double,
Periods_Range As Range, Swaps_Range As Range, Nominals_Range As Range,
LIBORs_Range As Range)

Cycle = 1
Freq = Frequent
DCF = 0

'Build the Discount, Period, LIBOR and Nominal Arrays

Dim Period_Array(1 To 100) As Variant

For Each ActualCell In Periods_Range.Cells

If ActualCell.Value 0.001 Then

Period_Array(Cycle) = ActualCell.Value

Else
End If

Cycle = 1 + Cycle

Next ActualCell

Although the Range Periods_Range is populated with data the "Watch" gives
each value as "Empty". Can any one help?

Thank you
--
Matt Roberts

For Each Next returns "Empty" when Data in Cell
 

Tom

Thank you - your interpretation was quite correct. I had hoped (for perhaps
the first time) that I had overlooked something solvable, instead I am going
to have to find another way of acheiving my aim.
--
Matt Roberts


"Tom Ogilvy" wrote:

I don't see anything technically wrong with your code if that is what you
want.

If you have a cell in Periods_Range that is greater than .001, then you
should have a value in your array.

You will have empty elements in the array corresponding to any cell that is
less than or equal to .001 or for any elements that exceed the number of
cells in Periods_Range up to the 100th element.

--
Regards,
Tom Ogilvy


"Matt Roberts" wrote in message
...
I have written a function with the intention of iterating the function
(rather than the workbook) but am having difficulty pulling the
information
through:

Public Function Iterative_LIBOR(Target As Double, Frequent As Double,
Periods_Range As Range, Swaps_Range As Range, Nominals_Range As Range,
LIBORs_Range As Range)

Cycle = 1
Freq = Frequent
DCF = 0

'Build the Discount, Period, LIBOR and Nominal Arrays

Dim Period_Array(1 To 100) As Variant

For Each ActualCell In Periods_Range.Cells

If ActualCell.Value 0.001 Then

Period_Array(Cycle) = ActualCell.Value

Else
End If

Cycle = 1 + Cycle

Next ActualCell

Although the Range Periods_Range is populated with data the "Watch" gives
each value as "Empty". Can any one help?

Thank you
--
Matt Roberts