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
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