Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    simplify this code (2002)

    Hi! Can someone help me simplify this code? It works fine but I was wondering if there's a way to simplify it. Thanks!


    Sub macro_lag1()
    Range("a1").Select
    Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 1).End(xldown)).Select
    Selection.Name = "range1"
    ActiveCell.End(xldown).Select
    Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Select
    Selection.Name = "range2"
    Range("d2").Value = covar(Range("range1"), Range("range2"))
    End Sub

    Sub macro_lag2()
    Range("a1").Select
    Range(ActiveCell.Offset(2, 1), ActiveCell.Offset(2, 1).End(xldown)).Select
    Selection.Name = "range1"
    ActiveCell.End(xldown).Select
    Range(ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 0).End(xlUp)).Select
    Selection.Name = "range2"
    Range("d3").Value = covar(Range("range1"), Range("range2"))
    End Sub

    Sub macro_lag3()
    Range("a1").Select
    Range(ActiveCell.Offset(3, 1), ActiveCell.Offset(3, 1).End(xldown)).Select
    Selection.Name = "range1"
    ActiveCell.End(xldown).Select
    Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-3, 0).End(xlUp)).Select
    Selection.Name = "range2"
    Range("d4").Value = covar(Range("range1"), Range("range2"))
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplify this code (2002)

    As a start you can convert:

    Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 1).End(xldown)).Select
    Selection.Name = "range1"

    to

    Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 1).End(xldown)).Name = "range1"

    It's not wholly clear what you are attempting to do - as you are repeating use of the same range name.

    If this is "dummy" code and you are running all three procedures consecutively, then conceivably, you could condense this into a loop. Alternatively, you could try:<pre>Sub Lag()
    macro_lag1
    macro_lag2
    macro_lag3
    End Sub</pre>

    HTH
    Gre

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: simplify this code (2002)

    Try this (no ranges get selected, that's usually more efficient):

    Sub Do_LagN(n As Long)
    Dim rng As Range, rng1 As Range, rng2 As Range
    Set rng = Range("A1").Offset(n, 1)
    Set rng1 = Range(rng, rng.End(xlDown))
    Set rng = rng.End(xlDown).Offset(-n, 0)
    Set rng2 = Range(rng, rng.End(xlUp))
    Range("D1").Offset(n, 0).Value = Application.WorksheetFunction.Covar(rng1, rng2)
    End Sub

    Sub Macro_Lag1()
    Do_LagN 1
    End Sub

    Sub Macro_Lag2()
    Do_LagN 2
    End Sub

    Sub Macro_Lag3()
    Do_LagN 3
    End Sub

    Or to run all three:

    Sub Macro_Lag()
    Dim i As Long
    For i = 1 To 3
    Do_LagN i
    Next i
    End Sub

  4. #4
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplify this code (2002)

    Hans, thanks. It works!
    Great Big Thanks for you Hans.. THANK YOU!!!!!

Posting Permissions

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