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

    pls simplify this code (excel)

    I dont know how to make a relative reference code for this problem. Here are the codes:
    Sheets("sales").Select
    ActiveSheet.Range("A1").Select
    lag_1
    lag_2
    lag_3
    end sub
    I have 10 lags;and here is the sub macro for 'lags_1',(similar in all 10 lags, adjustment is made for the cell offsets)

    Range("A1").Select
    Range(ActiveCell.Offset(1, 2), ActiveCell.Offset(0, 2).End(xlDown)).Name = "sales1"
    ActiveCell.End(xlDown).Select
    Range(ActiveCell.Offset(-1, 2), ActiveCell.Offset(0, 2).End(xlUp)).Name = "lag1"
    Sheets("Lag").Cells(2, 2).FormulaR1C1 = "=COVAR(sales1,lag1)"

    the problem is...when i run the macro, lag_1 is fine as it selects cell A1 relative to Sheets("sales").Range("A1").....but lag_2 until lag_10 won't be relative to Sheets("sales").Range("A1"). lag_2 until lag_10 will select cell A1 relative to its previous activecell. Basically, i want every 'lag' to select cell A1 relative to Sheets("sales").Range("A1"). Hope you understnad this problem. Thank you.

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

    Re: pls simplify this code (excel)

    See the thread starting at <post#=456275>post 456275</post#>. You can do something similar to my reply there.

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pls simplify this code (excel)

    but, i have 10 companies with will use lag_1 until lag_10. I'm thinking of just changing (Sheets("sales").Select
    ActiveSheet.Range("A1").Select) to (Sheets("sales").Select.ActiveSheet.Range("B1").Se lect ) and then the macro of lag will run automatically will relative to the cell B1 at thgis time.

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

    Re: pls simplify this code (excel)

    I'm afraid I don't understand. Could you post a workbook that shows what you have now, with an indication of what you want to accomplish?

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pls simplify this code (excel)

    owh, i've got it...thank u so much

Posting Permissions

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