+ Reply to Thread
Results 1 to 10 of 10

Running a macro in different sells

  1. 05-10-2012, 06:56 PM #1
    Myria
    Myria is offline
    Registered User
    Join Date
    05-06-2012
    Location
    Cyprus
    MS-Off Ver
    Excel 2010
    Posts
    7

    Running a macro in different sells

    Hello!!

    I'm a new user of macros
    I have recorded a macro were it measures correlation between the cells.
    My problem is that I have to do that procedure for a lot combinations that's why I record the macro
    My cells range that is recorded starts from the cells JSL6-JU6. I want to make the same procedure with the help of macros for the range of The following 52 Cell but I will start from JUM.

    The Code is this one below:


    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7002]:R[37]C[-7002],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7003]:R[37]C[-7003],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7004]:R[37]C[-7004],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7005]:R[37]C[-7005],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7006]:R[37]C[-7006],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7007]:R[37]C[-7007],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7008]:R[37]C[-7008],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7009]:R[37]C[-7009],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7010]:R[37]C[-7010],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7011]:R[37]C[-7011],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7012]:R[37]C[-7012],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7013]:R[37]C[-7013],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7014]:R[37]C[-7014],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7015]:R[37]C[-7015],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7016]:R[37]C[-7016],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7017]:R[37]C[-7017],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7018]:R[37]C[-7018],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7019]:R[37]C[-7019],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7020]:R[37]C[-7020],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7021]:R[37]C[-7021],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7022]:R[37]C[-7022],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7023]:R[37]C[-7023],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7024]:R[37]C[-7024],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7025]:R[37]C[-7025],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7026]:R[37]C[-7026],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7027]:R[37]C[-7027],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7028]:R[37]C[-7028],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7029]:R[37]C[-7029],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7030]:R[37]C[-7030],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7031]:R[37]C[-7031],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7032]:R[37]C[-7032],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7033]:R[37]C[-7033],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7034]:R[37]C[-7034],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7035]:R[37]C[-7035],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7036]:R[37]C[-7036],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7037]:R[37]C[-7037],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7038]:R[37]C[-7038],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7039]:R[37]C[-7039],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7040]:R[37]C[-7040],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7041]:R[37]C[-7041],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7042]:R[37]C[-7042],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7043]:R[37]C[-7043],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7044]:R[37]C[-7044],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7045]:R[37]C[-7045],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7046]:R[37]C[-7046],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7047]:R[37]C[-7047],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7048]:R[37]C[-7048],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7049]:R[37]C[-7049],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7050]:R[37]C[-7050],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7051]:R[37]C[-7051],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7052]:R[37]C[-7052],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7053]:R[37]C[-7053],RC[-7263]:R[37]C[-7263])"
    ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub
    Reply With QuoteRegister To Reply
  2. 05-10-2012, 07:21 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Running a macro in different sells

    Hi Myria.

    A change to your formula makes the formula itself dynamic. If you select JSL6 and run this, you get the same thing:
    Please Login or Register  to view this content.
    Now, if you select a different cell, what would that cell be and what would the first formula be?


    As per forum rules, you should EDIT that post above and put code tags around the code, like I've shown here.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 05-10-2012, 07:32 PM #3
    Myria
    Myria is offline
    Registered User
    Join Date
    05-06-2012
    Location
    Cyprus
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Running a macro in different sells

    Thank you for the reply
    My problem is that I don't know what I have to write or change the code :/ I don't know the procedure.

    I want to start from the cell JWM6 and make the same procedure for the next 52 cells the last cell will be JYM6
    the formula is : =CORREL(JJ6:JJ43;B6:B43)

    I need a code to be able to adjust it because I will do the same procedure for more than 1000 times not only for the next 52.
    Every 52 the formula is changing.
    Basically I measure the relationship of two shares return in the first array and in the second the price of the shares.

    I would be more than grateful If I find a code ton minimize the work that I have to do.
    Thank you
    Reply With QuoteRegister To Reply
  4. 05-11-2012, 06:52 AM #4
    Myria
    Myria is offline
    Registered User
    Join Date
    05-06-2012
    Location
    Cyprus
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Running a macro in different sells

    I saw some tutorials in youtube and know I have that code below. My macro run for the next 52 cells but only change the formula for the one only What I do wrong? :/


    Sub MACRO_CORREL_()
    '
    ' MACRO_CORREL_ Macro
    '

    '
    'ActiveCell.Offset(0, 2).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7156]:R[37]C[-7156],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7157]:R[37]C[-7157],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7158]:R[37]C[-7158],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7159]:R[37]C[-7159],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7160]:R[37]C[-7160],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7161]:R[37]C[-7161],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7162]:R[37]C[-7162],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7163]:R[37]C[-7163],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7164]:R[37]C[-7164],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7165]:R[37]C[-7165],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7166]:R[37]C[-7166],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7167]:R[37]C[-7167],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7168]:R[37]C[-7168],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7169]:R[37]C[-7169],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7170]:R[37]C[-7170],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7171]:R[37]C[-7171],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7172]:R[37]C[-7172],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7173]:R[37]C[-7173],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7174]:R[37]C[-7174],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7175]:R[37]C[-7175],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7176]:R[37]C[-7176],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7177]:R[37]C[-7177],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7178]:R[37]C[-7178],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7179]:R[37]C[-7179],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7180]:R[37]C[-7180],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7181]:R[37]C[-7181],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7182]:R[37]C[-7182],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7183]:R[37]C[-7183],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7184]:R[37]C[-7184],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7185]:R[37]C[-7185],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7186]:R[37]C[-7186],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7187]:R[37]C[-7187],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7188]:R[37]C[-7188],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7189]:R[37]C[-7189],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7190]:R[37]C[-7190],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7191]:R[37]C[-7191],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7192]:R[37]C[-7192],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7193]:R[37]C[-7193],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7194]:R[37]C[-7194],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7195]:R[37]C[-7195],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7196]:R[37]C[-7196],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7197]:R[37]C[-7197],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7198]:R[37]C[-7198],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7199]:R[37]C[-7199],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7200]:R[37]C[-7200],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7201]:R[37]C[-7201],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7202]:R[37]C[-7202],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7203]:R[37]C[-7203],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7204]:R[37]C[-7204],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7205]:R[37]C[-7205],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7206]:R[37]C[-7206],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
    "=CORREL(RC[-7207]:R[37]C[-7207],RC[-7422]:R[37]C[-7422])"
    ActiveCell.Offset(0, 1).Select
    End Sub
    Reply With QuoteRegister To Reply
  5. 05-11-2012, 09:16 AM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Running a macro in different sells

    My original answer is the one I think is the correct approach. It's a single formula can enter all at once into all 52 cells and the OFFSET() increments your columns for you automatically.

    And you didn't answer my question. Even staying with your own forrmula, what is the next formula you would like in what starting cell?

    JWM6: =CORREL(JJ6:JJ43, B6:B43) (is this correct starting formula?)
    JWN6: =CORREL(JJ6:JJ43, C6:C43) (is this correct next formula)
    ....continue pattern for a total of 52 cells.


    Please do not answer with VBA.


    BTW, the post #2 formula I am suggesting for JSM6 is:
    =CORREL($JD$6:$JD$43, INDEX(OFFSET($D$6,,COLUMN(A$1)-1,38,1),0))

    When you copy that cell to the right as is it increments properly, giving you the same results with a single formula.
    Last edited by JBeaucaire; 05-11-2012 at 09:18 AM.
    Reply With QuoteRegister To Reply
  6. 05-11-2012, 11:21 AM #6
    Myria
    Myria is offline
    Registered User
    Join Date
    05-06-2012
    Location
    Cyprus
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Running a macro in different sells

    It goes like this
    JWM6: =CORREL(JJ6:JJ43, B6:B43)
    JMN6: =CORREL(JJ6:JJ43, C6:C43)
    JW06: =CORREL(JJ6:JJ43, D6:D43)
    JMp6: =CORREL(JJ6:JJ43, E6:E43)...etc

    When I add the first formula and use the scroll function it goes like this:

    JWM6: =CORREL(JJ6:JJ43, B6:B43)
    JMN6: =CORREL(JK6:JK43, C6:C43)

    I need a macro to do it automatically or to make correlation function to remain stable the first array and the second one to move.

    The formula: =CORREL($JD$6:$JD$43, INDEX(OFFSET($D$6,,COLUMN(A$1)-1,38,1),0)) is not working
    Reply With QuoteRegister To Reply
  7. 05-11-2012, 03:14 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Running a macro in different sells

    When I add the first formula and use the scroll function it goes like this:

    JWM6: =CORREL(JJ6:JJ43, B6:B43)
    JMN6: =CORREL(JK6:JK43, C6:C43)
    If you were to lock the first section, you would need to add in the $ symbols to lock the columns and rows:
    JWM6: =CORREL($JJ$6:$JJ$43, B6:B43)


    The formula: =CORREL($JD$6:$JD$43, INDEX(OFFSET($D$6,,COLUMN(A$1)-1,38,1),0)) is not working
    For JSM6, this formula certainly does resolve to the same thing as your original formula:

    =CORREL($JD$6:$JD$43, INDEX(OFFSET($D$6,,COLUMN(A$1)-1,38,1),0))

    ...and when copied to the right, it increments the COLUMN only.

    Now, you might want to change the anchor cell from $D$6 to $B$6... up to you.
    Reply With QuoteRegister To Reply
  8. 05-11-2012, 06:26 PM #8
    Myria
    Myria is offline
    Registered User
    Join Date
    05-06-2012
    Location
    Cyprus
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Running a macro in different sells

    JBeaucaire thank youuu a loooot )

    I just use the $ symbols and worked thank you again I'm more than grateful :*

    Myria
    Reply With QuoteRegister To Reply
  9. 05-12-2012, 01:09 AM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Running a macro in different sells

    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.
    Reply With QuoteRegister To Reply
  10. 05-12-2012, 04:21 AM #10
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running a macro in different sells

    Myria

    Code tags need adding to your posts.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1