PDA

View Full Version : How to return entire output from Array to Excel sheet in one go



Whisky
02-24-2018, 02:11 PM
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
02-24-2018, 02:53 PM
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.Trans pose(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
02-24-2018, 03:49 PM
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
03-03-2018, 12:23 PM
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
03-05-2018, 05:13 AM
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