Results 1 to 1 of 1

How do i loop by offsetting range by 1 row

Threaded View

  1. #1
    Registered User
    Join Date
    07-18-2012
    Location
    ny, ny
    MS-Off Ver
    Excel 2010
    Posts
    10

    How do i loop by offsetting range by 1 row

    How to I increment the x and y data sets by one?
    -------------------

        Sub main_ols()
        Dim x, y, z,
        Dim strMsg As String
    
    
        For x = 2 To 500
    
        x = RangeToArray(Sheet4.Range("B2:B51"))
        y = RangeToArray(Sheet4.Range("C2:C51"))
    
        z = regress_orthols(x, y)
    
        strMsg = "1.slope" & vbTab & z(1) & vbNewLine
        strMsg = strMsg & "2.intercept" & vbTab & z(2) & vbNewLine
        strMsg = strMsg & "3.sigma_slope" & vbTab & z(3) & vbNewLine
        strMsg = strMsg & "4.sigma_intercept" & vbTab & z(4) & vbNewLine
    
        MsgBox strMsg, vbInformation, "Least-squares Orthogonal Regression Result"
    
        Sheet4.Range("slope") = z(1)
        Sheet4.Range("intercept") = z(2)
    
        z = regress_ls(x, y)
    
        strMsg = "1.slope" & vbTab & z(1) & vbNewLine
        strMsg = strMsg & "2.intercept" & vbTab & z(2) & vbNewLine
        strMsg = strMsg & "3.sigma_slope" & vbTab & z(3) & vbNewLine
        strMsg = strMsg & "4.sigma_intercept" & vbTab & z(4) & vbNewLine
    
        MsgBox strMsg, vbInformation, "Least-squares Regression Result"
        Next
        Set x = RangeToArray("B2:B51").Offset([1], [0]).Select
    
        Set y = Range("y").Offset(1, 0).Select
    
        z = regress_orthols(x, y)
        Set z(1) = Sheet4.Range("slope").Offset(1, 0).Select
        Set z(2) = Sheet4.Range("intercept").Offset(1, 0).Select
        Sheet4.Range("slope") = z(1)
        Sheet4.Range("intercept") = z(2)
    Last edited by Leith Ross; 07-23-2012 at 04:52 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1