correlation addin to macro

S

ssaifan

Active Member
Joined
Jul 29, 2003
Messages
355
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Macro Does Not Close Excel Workbook
2
Replies
14
Views
133
Jeffrey Mahoney
Jeffrey Mahoney
N
  • Question
My code is suddenly really slow
Replies
1
Views
132
RobP
R
A
  • Question
Help Debugging Custom Data Label Macro
Replies
0
Views
121
arndtea
A
P
  • Solved
Slow Macro
Replies
3
Views
216
Philip1957
P
N
  • Question
Select Method of Range Class Failed with Table
Replies
4
Views
177
NorthbyNorthwest
N

Forum statistics

Threads
1,214,788
Messages
6,121,582
Members
449,039
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top