I used to use that sub where inputs and outputs were on LINE ranges. Everything was working fine.
Then I switched the data to COLUMN ranges. Now the function loops correctly through all the input data, do all the calculations right, but it seems that the output data is not correctly added to the array: if there are say 10 items in the input range, it will give 10 times the same answer in the output range, that answer being the result of the first input.
Thanks for your help.
Sub GoalSeek5Y()
Dim rRangeInput As Range
Dim rCell As Range
Dim rRangeOutput As Range
Dim sTicker As String
Dim cSpread As Currency
Dim cArraySpread() As Currency
Dim Spread As Currency
Dim x As Integer
Set rRangeInput = Sheets("DB_RES").Range("U2:U4")
Set rRangeOutput = Sheets("DB_RES").Range("Z2:Z4")
ReDim Preserve cArraySpread(1 To rRangeOutput.Cells.Count)
x = 1
For Each rCell In rRangeInput.Cells
sTicker = rCell.Value
Sheets("CALCULS_NPV").Range("newTicker").Value = sTicker
Range("NPVpriceDiff ").goalSeek Goal:=Range("goal"), ChangingCell:=Range("Spread")
cSpread = Sheets("CALCULS_NPV").Range("Spread").Value2
cArraySpread(x) = cSpread
x = x + 1
Next rCell
rRangeOutput = cArraySpread
Debug.Print cSpread
End Sub