correlation addin to macro

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: correlation addin to macro

  1. #1
    Board Regular
    Join Date
    Jul 2003
    Location
    london
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default correlation addin to macro

     
    i have the foll. macro which captures a column of data - as my data length
    changes frequently the below macro does that ....

    now i can capture 2 colums of data with that macro by changing column name..

    now what i wana do is that ...

    calculate the correlation coeff using correl function ...

    can anyone tell me how to add that bit to my existing macro ...
    below is the macro ......

    --------------------------
    Sub select_datastrategy()

    Range("f1:f500").Select
    Selection.ClearContents

    Dim ulhc As String 'upper left hand corner
    Dim lrhc As String 'lower right hand corner
    Dim wdth As Integer 'true width in columns

    ulhc = "$b$2"
    wdth = 1

    Application.Goto Range(ulhc) 'goto upper left hand corner
    ActiveCell.Offset(0, wdth - 1).Select 'move to edge of data (column)
    lrhc = Selection.End(xlDown).Address 'get address of lrhc

    Range(ulhc & ":" & lrhc).Select
    Selection.Copy
    Sheets("sheet1").Select
    Range("$f$2").Select
    'ActiveCell.Select
    Selection.PasteSpecial Paste:=xlValues, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    End Sub
    -----------------
    assume next column of data in column G ....


    thanks

    sam

  2. #2
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: correlation addin to macro

    Hi Sam, it just seems you want to replace contents in column f with those in column b (from b2 down until last row), then do a correl formula between column f and column g. Ive shortened the code somewhat (uyou dont really need to select things to transfer data) gives an example to get you started. I have just put the formula in cell h2 with range of rows 2-10 for cols f & g. Note that the correl appears to need two ranges that are identical size?

    Code:
    Sub test()
    Dim Rng As Range
    'Copy data from b2 until last row in range into cell f1
    Range("f1:f500").ClearContents
    Set Rng = Range("b2:b" & Range("b65536").End(xlUp).Row)
    Rng.Copy Range("f1")
    
    'Insert correl function for f2:f10 and g2:g10 into cell h2
    Range("h2").Formula = "=correl(f2:f10,g2:g10)"
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Jul 2003
    Location
    london
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: correlation addin to macro

      
    that was gr8 ..
    thanks

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com