Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data only if adjacent to cell labeled "male"/"female" | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Need formula like ISBLANK that returns "no value" even if there is aformula in referenced cell | Excel Worksheet Functions | |||
Finding cell that returns bad "solver_val" function. | Excel Discussion (Misc queries) | |||
Excel: can "go to adjacent empty cell" double-click be disabled? | Excel Discussion (Misc queries) |