I am working on automating a regression analysis for our field staff but I am running into a problem referencing the cell range for the regression. Essentially, I have a pre-made form that the sales staff fills out and then runs one of two macros depending upon what type of regression analysis is desired. These macros populate the data form that is the base of the regression analysis. This form could have anywhere from 1 to 88 entries. I am trying to find the last cell used (yLast) in the named range and by using an offset set the xLast cell. I can find the last y value cell and get the offset to the xLast cell but when I try to use these as a reference in the regression code, I get an "application or object defined error.
Here is the code:
Any help would be greatly appreciated.Private Sub CommandButton1_Click() 'code to run regression analysis Dim yFirst As Range Dim xFirst As Range Dim yEnd As Range Dim xEnd As Range Dim yRng As Range Dim xRng As Range With Range("RegRng") Set yEnd = .Cells(.Cells.Count).End(xlUp) End With Set xEnd = yEnd.Offset(0, -1) Set yFirst = Sheets("Data Summary").Cells(10, 5) Set xFirst = Sheets("Data Summary").Cells(10, 4) 'code to run regression - [COLOR=red]This is where the error occurs [/COLOR] Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$e$10:$E$" & yLast), _ ActiveSheet.Range("$d$10:$d$" & xEnd), False, False, , "Analysis", False, False _ , False, True, , False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1" Sheets("Data Summary").Select ActiveWindow.ScrollRow = 1 Range("G10").Select ActiveCell.FormulaR1C1 = "=SUM(Analysis!R17C2+RC[-3]*Analysis!R18C2)" Range("G10").Select Selection.AutoFill Destination:=Range("G10:G99"), Type:=xlFillDefault Range("G10:G99").Select End SubFormatting tags added by mark007