200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
The following excel function works well in excel formula bar
=CORREL(INDEX(INDIRECT(inRng),INDIRECT(f1Rng),1):INDEX(INDIRECT(inRng),INDIRECT(l1Rng),1), INDEX(INDIRECT(inRng),INDIRECT(f2Rng),2):INDEX(INDIRECT(inRng),INDIRECT(l2Rng),2))
Please can anyone help me implementing the above function using WorksheetFunction in VBA code?
Wow! That was a tought nut.
Please find below a UDF which can replace the worksheet function in column K.
Function Correl_UDF(ByVal R As Long)
' 19 Jul 2018
Const F1 As String = "G"
Const F2 As String = "I"
Const L1 As String = "H"
Const L2 As String = "J"
Dim inRng As Range
Dim Rng1 As Range, Rng2 As Range
Dim Rr As Long ' conversion factor: range row to sheet row
With Worksheets("Arkusz1")
Set inRng = .Range("B2:C853")
Rr = inRng.Row - 1
Set Rng1 = Range(.Cells(.Cells(R, F1).Value + Rr, inRng.Columns(1).Column), _
.Cells(.Cells(R, L1).Value + Rr, inRng.Columns(1).Column))
Set Rng2 = Range(.Cells(.Cells(R, F2).Value + Rr, inRng.Columns(2).Column), _
.Cells(.Cells(R, L2).Value + Rr, inRng.Columns(2).Column))
Correl_UDF = Application.WorksheetFunction.Correl(Rng1, Rng2)
End With
End Function
To call the function enter =Correl_UDF(ROW()) in the worksheet cell.
For some reason I was unable to reference the named ranges in your workbook. Therefore I recreated the range inRng in the function. Perhaps you can get the original to work. That would be better - in a way. In the sheet, row numbers are entered as range row numbers instead of sheet row numbers. That makes things rather convoluted now that you don't need the ranges anymore. For all the single column ranges I just declared constants and then adjusted the row numbers. Observe the functionality of the variable Rr in this regard.