0

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

1 Answer 1

0

SOLVED.

My 'new' understanding is that by default VBA populates an array into a line. To put the values of the array in a column, the array must be transposed.

So I modified the line of code after "Next rCell", and everything works now.

            Next rCell                                                                
    rRangeOutput = Application.WorksheetFunction.Transpose(cArraySpread)
    Debug.Print cSpread

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .