Thanks:  0
Likes:  0

1. ## 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)

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. ## 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. ## Re: correlation addin to macro

that was gr8 ..
thanks

## User Tag List

#### Posting Permissions

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