Err.Number=1004 occurs at the Application.WorksheetFunction.Correl function
call. Why? Please refer my macro code snippet below:


For intCtr1 = 1 To intFOcount
For intCtr2 = 1 To intFOcount
Set rngRange1 = Range(wshOut3.Cells(2, intCtr1 + 1).Address(),
wshOut3.Cells(intNumdays + 1, intCtr1).Address())
Set rngRange2 = Range(wshOut3.Cells(2, intCtr2 + 1).Address(),
wshOut3.Cells(intNumdays + 1, intCtr2).Address())
'this statement below throws Err 1004, why?
dblCorrel = Application.WorksheetFunction.Correl(rngRange1, rngRange2)
wshOut2.Cells(intCtr1 + 1, intCtr2 + 1) = dblCorrel
Next intCtr2
Next intCtr1

'Objective: To find the correlation of two futures contracts, given their
closing price.
'Description of variables:
'intFOcount: The total number of (futures) contracts (scrip names)
'wshOut3: Of type worksheet is Sheet3 of the output file wherein the closing
price is written in vertical columns using another macro
'intNumdays: Total number of days for which I extract the closing price of
each contract
'intCtr1, intCtr2: Loop variables
'dblCorrel: wish to store the correlation (rho) value in this variable



TIA!!


Regards,
BR


--
Manager, International Private Banking, International Banking Group, ICICI
Bank
East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
400051

Hi BR,
The online help says:
- If rngRnage1 and rngRange2 have a different number of data points, CORREL
returns the #N/A error value.
- If either rngRange1 or rngRange2 is empty, or if s (the standard
deviation) of their values equals zero, CORREL returns the #DIV/0! error
value.
In these cases vba would return error 1004.

So what is the result of the function if you enter it directly in the sheet?
Could you please post the result of
Debug.Print rngRange1.address, RngRange2.Address
when placed right before the <error line: dblCorrel = ...

Regards,
Sebastien

"BHARATH RAJAMANI" wrote:


Err.Number=1004 occurs at the Application.WorksheetFunction.Correl function
call. Why? Please refer my macro code snippet below:


For intCtr1 = 1 To intFOcount
For intCtr2 = 1 To intFOcount
Set rngRange1 = Range(wshOut3.Cells(2, intCtr1 + 1).Address(),
wshOut3.Cells(intNumdays + 1, intCtr1).Address())
Set rngRange2 = Range(wshOut3.Cells(2, intCtr2 + 1).Address(),
wshOut3.Cells(intNumdays + 1, intCtr2).Address())
'this statement below throws Err 1004, why?
dblCorrel = Application.WorksheetFunction.Correl(rngRange1, rngRange2)
wshOut2.Cells(intCtr1 + 1, intCtr2 + 1) = dblCorrel
Next intCtr2
Next intCtr1

'Objective: To find the correlation of two futures contracts, given their
closing price.
'Description of variables:
'intFOcount: The total number of (futures) contracts (scrip names)
'wshOut3: Of type worksheet is Sheet3 of the output file wherein the closing
price is written in vertical columns using another macro
'intNumdays: Total number of days for which I extract the closing price of
each contract
'intCtr1, intCtr2: Loop variables
'dblCorrel: wish to store the correlation (rho) value in this variable



TIA!!


Regards,
BR


--
Manager, International Private Banking, International Banking Group, ICICI
Bank
East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
400051

Sebastien, rngRange1.Address & rngRange2.Address did not give any value in
the watch window so I used this workaround:

wshOut2.Cells(intCtr1 + 1, intCtr2 + 1).FormulaR1C1 = "=CORREL(Sheet3!R[" &
(1 - intCtr1) & "]C[" & intCtr3 & "]:R[" & (intNumdays - intCtr1) & "]C[" &
intCtr3 & "],Sheet3!R[" & (1 - intCtr1) & "]C:R[" & (intNumdays - intCtr1) &
"]C)"

and it works fine for now! thank msft for the macro recorder :-)


Regards,
BR


"sebastienm" wrote:

Hi BR,
The online help says:
- If rngRnage1 and rngRange2 have a different number of data points, CORREL
returns the #N/A error value.
- If either rngRange1 or rngRange2 is empty, or if s (the standard
deviation) of their values equals zero, CORREL returns the #DIV/0! error
value.
In these cases vba would return error 1004.

So what is the result of the function if you enter it directly in the sheet?
Could you please post the result of
Debug.Print rngRange1.address, RngRange2.Address
when placed right before the <error line: dblCorrel = ...

Regards,
Sebastien

"BHARATH RAJAMANI" wrote:


Err.Number=1004 occurs at the Application.WorksheetFunction.Correl function
call. Why? Please refer my macro code snippet below:


For intCtr1 = 1 To intFOcount
For intCtr2 = 1 To intFOcount
Set rngRange1 = Range(wshOut3.Cells(2, intCtr1 + 1).Address(),
wshOut3.Cells(intNumdays + 1, intCtr1).Address())
Set rngRange2 = Range(wshOut3.Cells(2, intCtr2 + 1).Address(),
wshOut3.Cells(intNumdays + 1, intCtr2).Address())
'this statement below throws Err 1004, why?
dblCorrel = Application.WorksheetFunction.Correl(rngRange1, rngRange2)
wshOut2.Cells(intCtr1 + 1, intCtr2 + 1) = dblCorrel
Next intCtr2
Next intCtr1

'Objective: To find the correlation of two futures contracts, given their
closing price.
'Description of variables:
'intFOcount: The total number of (futures) contracts (scrip names)
'wshOut3: Of type worksheet is Sheet3 of the output file wherein the closing
price is written in vertical columns using another macro
'intNumdays: Total number of days for which I extract the closing price of
each contract
'intCtr1, intCtr2: Loop variables
'dblCorrel: wish to store the correlation (rho) value in this variable



TIA!!


Regards,
BR


--
Manager, International Private Banking, International Banking Group, ICICI
Bank
East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
400051