Change to Code
 

Pedro,

Use the Set statement to set the variables to range objects. E.g.,

SET fstRange = Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SET SndRange = Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
Hi
What change should I made to the following code to work

fstRange =
Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SndRange =
Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))
strCorr =
Application.WorksheetFunction.Correl(fstRange,
SndRange)

Thanks
Pedro

Change to Code
 

I already tried it but it seems give an error because of different
worksheets



"Chip Pearson" wrote in message
...
Pedro,

Use the Set statement to set the variables to range objects. E.g.,

SET fstRange = Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SET SndRange = Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
Hi
What change should I made to the following code to work

fstRange =
Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SndRange =
Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))
strCorr =
Application.WorksheetFunction.Correl(fstRange,
SndRange)

Thanks
Pedro

Change to Code
 

Pedro,

Try the following:

With Worksheets(strSheet1)
Set fstRange = .Range(.Cells(RIndex2 - 35, CIndex2), _
.Cells(RIndex2, CIndex2))
End With
With Worksheets(strSheet)
Set SndRange = .Range(.Cells(RIndex2 - 35, CIndex3), _
.Cells(RIndex2, CIndex3))
End With

Note the periods preceding "Range" and "Cells".

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
I already tried it but it seems give an error because of
different
worksheets



"Chip Pearson" wrote in message
...
Pedro,

Use the Set statement to set the variables to range objects.
E.g.,

SET fstRange = Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SET SndRange = Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
Hi
What change should I made to the following code to work

fstRange =
Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SndRange =
Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))
strCorr =
Application.WorksheetFunction.Correl(fstRange,
SndRange)

Thanks
Pedro

Change to Code
 

Thanks Chip
I already try that one but when I add the code
Application.WorksheetFunction.Correl(fstRange,SndR ange)
it will consider the range of the active worksheet and not those two in the
two ranges

Do you know how to solve it?
Thanks for your help

Best Regards,
PEdro



"Chip Pearson" wrote in message
...
Pedro,

Try the following:

With Worksheets(strSheet1)
Set fstRange = .Range(.Cells(RIndex2 - 35, CIndex2), _
.Cells(RIndex2, CIndex2))
End With
With Worksheets(strSheet)
Set SndRange = .Range(.Cells(RIndex2 - 35, CIndex3), _
.Cells(RIndex2, CIndex3))
End With

Note the periods preceding "Range" and "Cells".

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
I already tried it but it seems give an error because of
different
worksheets



"Chip Pearson" wrote in message
...
Pedro,

Use the Set statement to set the variables to range objects.
E.g.,

SET fstRange = Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SET SndRange = Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
Hi
What change should I made to the following code to work

fstRange =
Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SndRange =
Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))
strCorr =
Application.WorksheetFunction.Correl(fstRange,
SndRange)

Thanks
Pedro

Change to Code
 

Thanks Chip
Now it has worked

Regards
Pedro



"Pedro" wrote in message
...
Thanks Chip
I already try that one but when I add the code
Application.WorksheetFunction.Correl(fstRange,SndR ange)
it will consider the range of the active worksheet and not those two in
the
two ranges

Do you know how to solve it?
Thanks for your help

Best Regards,
PEdro



"Chip Pearson" wrote in message
...
Pedro,

Try the following:

With Worksheets(strSheet1)
Set fstRange = .Range(.Cells(RIndex2 - 35, CIndex2), _
.Cells(RIndex2, CIndex2))
End With
With Worksheets(strSheet)
Set SndRange = .Range(.Cells(RIndex2 - 35, CIndex3), _
.Cells(RIndex2, CIndex3))
End With

Note the periods preceding "Range" and "Cells".

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
I already tried it but it seems give an error because of
different
worksheets



"Chip Pearson" wrote in message
...
Pedro,

Use the Set statement to set the variables to range objects.
E.g.,

SET fstRange = Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SET SndRange = Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pedro" wrote in message
...
Hi
What change should I made to the following code to work

fstRange =
Worksheets(strSheet1).Range(Cells(RIndex2 - 35,
CIndex2), Cells(RIndex2, CIndex2))
SndRange =
Worksheets(strSheet).Range(Cells(RIndex2 - 35,
CIndex3), Cells(RIndex2, CIndex3))
strCorr =
Application.WorksheetFunction.Correl(fstRange,
SndRange)

Thanks
Pedro