Macro selected cells/apply macro to selected cells

[blah757]

Hi, so what I'm trying to do is run a regression for every 5 cells of data, copy a value and paste it and repeat for 1000cells. I tried making a Macro but it just regresses the exact 5cells I used. I want to make it so that i can keep applying the task for the next 5 cells for the macro, or apply the task to the 5 cells I am currently selecting.


this is the code i have from the macro

Sub test()

'

' test Macro

' test

'

' Keyboard Shortcut: Ctrl+t

'

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$B$257:$B$261") _

, ActiveSheet.Range("$C$257:$C$261"), False, False, , ActiveSheet.Range( _

"$A$1:$I$18"), False, False, False, False, , False

Range("B18").Select

Selection.Copy

Sheets("Sheet2").Select

Range("B53").Select

ActiveSheet.Paste

End Sub


PS:I have no knowledge of coding/macro-ing whatsoever, so a simple answer would be great

thanks!

 

[Hui]

Blah757


Firstly, Welcome to the Chandoo.org forums.


Can you upload a sample file? Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[blah757]

thankss.


http://www.mediafire.com/view/?1mdv1kz9ckd1hi2

 

[Hui]

Blah757


2 Questions please:


Q1. Does the groups of 5 Rows go:


OBS 1:5, OBS 6:10, OBS 11:15, OBS 16:20 etc ?

or

OBS 1:5, OBS 2:6, OBS 3:7 etc


Q2. Whats stats do you want to collect for each group of 5 Rows ?

Because I'm sure you don't want to make 200 sheets of results

I can setup a table of 200 results of the selected stats you are after


FYI: I am thinking of using a technique like that discussed at: http://chandoo.org/wp/2011/06/20/analyse-data-like-a-super-hero/

 

[blah757]

yeah it would be obs 1:5, 6:10, 11:15 and so on.

umm, it creates 200 sheets of results but i am only taking that one value.

that value is "coefficient" of the x value. because when i run the regression(the function) it creates the sheet with numerous values/information and i only need that one value and i dont think i can just select it to display that value.


I tried reading it but I'm not sure i understand it properly.

 

[Hui]

Blah 757


D2:

=INDEX(LINEST(OFFSET($B$2,(A2-1)*5,0,5,1),OFFSET($C$2,(A2-1)*5,0,5,1),TRUE,TRUE),3,1)


Copy down


This uses the Obs Number in Column A

Obs 1: =Rows 2:6

Obs 2: =Rows 7:11

Obs 3: =Rows 12:16


You can read about the Linest() function here: http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/


Edit by Hui: Or you could just use the RSQ() Function:

D2: =RSQ(OFFSET($B$2,(A2-1)*5,0,5,1),OFFSET($C$2,(A2-1)*5,0,5,1))