How to return entire output from Array to Excel sheet in one go

[Whisky]

Hi

I have the code below which creates and populates an array (rsqout) with 6 rows that stores results from a calculation (RSQ). When done I simply want to output the entire array to excel but when I do

"Sheets("Results").Range("a1:a7").value = rsqout" - it only returns the first row from the array.

I can only get it to work if i output each element of the array one at a time like this:

Sheets("Results").Range("a1").value = rsqout (1)
Sheets("Results").Range("a2").value = rsqout (2)
Sheets("Results").Range("a2").value = rsqout (3)

Is it possible to do all in one go?

More of the actual code below.

Thanks

W
-----------------------------------------------------------
option base 1

Dim rsqout(6) As Variant 'variant stores mixed 
Dim Untransformed As Single, Logged As Single, SQRRoot As Single, reciprocal As Single
Dim VarName As String


VarName = Range("BW1").Value
rsqout(1) = VarName 


'1) Orginal series
Set YReg = Range("B" & firstcell & ":" & "B" & lastcell)
Set XReg = Range("C" & firstcell & ":" & "C" & lastcell)
Untransformed = Application.WorksheetFunction.rsq(YReg, XReg)
rsqout(2) = Untransformed


'2) Natural Log
Set YReg = Range("B" & firstcell & ":" & "B" & lastcell)
Set XReg = Range("C" & firstcell & ":" & "C" & lastcell)
 Logged = Application.WorksheetFunction.rsq(YReg, XReg)
rsqout(3) = Logged


etc - 2 more steps till all 5 are done


    Out = Split(Sheets("Results").Range("BBW1").End(xlToLeft).Offset(0, 1).Address, "$")(1) '
    
'Range("f7").Value = rsqout


Sheets("Results").Range(Out & 1).Value = rsqout(1)
Sheets("Results").Range(Out & 2).Value = rsqout(2)
Sheets("Results").Range(Out & 3).Value = rsqout(3)
Sheets("Results").Range(Out & 4).Value = rsqout(4)
Sheets("Results").Range(Out & 5).Value = rsqout(5)
Sheets("Results").Range(Out & 6).Value = Application.Max(rsqout(2), rsqout(3), rsqout(4), rsqout(5))
Sheets("Results").Range(Out & 7).Formula = "=MATCH(" & Out & "6," & Out & "2:" & Out & "5)"


Dim best As String
 Select Case Sheets("Results").Range(Out & 7).Value
         Case 2
           best = "Untransformed"
           Case 3
            best = "Natural Log"
           Case 4
             best = "Square Root"
           Case 5
             best = "Reciporical"
          End Select


Sheets("Results").Range(Out & 8).Value = best

[Paul_Hossler]

Arrays have been a frequent topic of discussion here lately

Here's some example concept code that shows how I get and put data


Here's a pretty good (if advanced) tutorial on arrays

http://www.snb-vba.eu/VBA_Arrays_en.html

Option Explicit
Sub ToFrom()
    Dim i As Long, j As Long
    Dim R As Range
    
    Dim All As Variant, OneRow As Variant, OneCol As Variant

    With ActiveSheet
    
        Set R = .Range("A1:Z10")
        
        'fill some data
        R.Value = 123
    
        '2 dim array
        All = R.Value
        
        For i = LBound(All, 1) To UBound(All, 1)
            For j = LBound(All, 2) To UBound(All, 2)
                All(i, j) = 10 * All(i, j)
            Next j
        Next i
        
        'put back
        .Range("A15").Resize(UBound(All, 1) - LBound(All, 1) + 1, UBound(All, 2) - LBound(All, 2) + 1) = All
        
        
        '1 dim array
        OneRow = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(R.Rows(1)))
        '1 dim array
        OneCol = Application.WorksheetFunction.Transpose(R.Columns(1))
        
        For i = LBound(OneRow) To UBound(OneRow)
            OneRow(i) = 2 * OneRow(i)
        Next i
        For i = LBound(OneCol) To UBound(OneCol)
            OneCol(i) = 3 * OneCol(i)
        Next i
 
    End With
End Sub

[snb]

Simply use:

Sheets("Results").Range("a1:a7").value = application.transpose(rsqout)

You should have consulted: http://www.snb-vba.eu/VBA_Arrays_en.html first

[Whisky]

Thanks everyone for the links and help. That works but I have to be honest there are still some things I don't understand. Will probably post some more questions if that's ok.

Thanks

W

[Paul_Hossler]

Thanks everyone for the links and help. That works but I have to be honest there are still some things I don't understand. Will probably post some more questions if that's ok.

Of course it's OK --


PS: Make sure that you understand whatever code you end up using so that you can add/fix it later easily