MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel -> Regression-> How to write a macro with variable dataset sizes


Posted by yogesh k. potdar on October 26, 2001 3:37 AM

I am trying to write a macro in Excel which will do Regression analysis in Excel for a given set of data.

e.g. Cell(i,1)=178

Cell(i,3:178)->X1
Cell(i,3:178)->X2
Cell(i,3:178)->Y

Where X1,X2 go in X input of regression and Y in Y input.

This can be done rather easily by writing the following macro statement:

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$K$3:$K$178"), _
ActiveSheet.Range("$I$3:$J$178"), False, False, 95, "", False, False, _
False, True, , False


How do I replace 178 in the above statement by
Cells(i,1).Value or the value contained in Cell(i,1)?

Please Help!!

thanks...


Posted by Juan Pablo on October 26, 2001 7:23 AM

Use this

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$K$3:$K$" & Cells(i,1)), _
ActiveSheet.Range("$I$3:$J$" & Cells(i,1)), False, False, 95, "", False, False, _
False, True, , False

Juan Pablo

Posted by yogesh k. potdar on October 26, 2001 8:25 AM

Thanks .. it worked!!

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$K$3:$K$" & Cells(i,1)), _