Consulting

Results 1 to 5 of 5

Thread: Solved: Regression Analysis macro

  1. #1
    VBAX Regular
    Joined
    Nov 2008
    Posts
    10
    Location

    Solved: Regression Analysis macro

    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:


     
    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 - This is where the error occurs
         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 Sub
    Any help would be greatly appreciated.

    Tim

  2. #2
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    Is there a specifc reason why you are using the 'Application.Run' syntax?

    [vba]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
    [/vba]

    I'm pretty sure that if you want to pass Objects to a subroutine, you need to call it as a VBA Sub.

    [vba]Regress [Range Object], [Range Object], False, False, , "Analysis", False, _
    False, False, True, , False[/vba]

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I don't see where you're putting a value into yLast. I think you meant yEnd?

    [vba]
    Range("$e$10:$E$" & yLast)
    [/vba]

    Actually, it does not seemed to be Dim-ed. Did you use Option Explicit at the top of your module?

    That still won't work, since yEnd is a Range. Your probably want yEnd.Row since you're making a address (string)

    [vba]
    Range("$e$10:$E$" & yEnd.Row)
    [/vba]

    will give something like Range("$e$10:$E$1000")


    Paul

  4. #4
    VBAX Regular
    Joined
    Nov 2008
    Posts
    10
    Location
    Dr. K

    To answer your question, the syntax for the regression came from a recorded macro. I took this as a shortcut.

  5. #5
    VBAX Regular
    Joined
    Nov 2008
    Posts
    10
    Location

    Solved Regression Macro

    Paul

    Thanks. That did the trick. Sometimes the answer lies in the basics and I get wrapped up in the code and forget the simple things.

    Tim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •