Excel 2007 Better Way To Write Correlation Code

Hello,

Can someone recommend a better way to write the following code that will decrease the amount of time it would take to generate the same resuls?  I've provided a sample set showing 4 strings of code but my actual file contains 17-20 strings.  Just curious if there is a better way of setting this up.  Note that the display/events/screen updates are turned off/on already.

Thanks
Range("CM3").Select
ActiveCell.FormulaR1C1 = _
"=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])
RANGE("CM4").SELECT
ActiveCell.FormulaR1C1 = 
"=CORREL('FILTERED RAW DATA 1'!R[-2]C[72]:R[4996]C[72],'FILTERED RAW DATA 1'!R[-2]C[73]:R[4996]C[73])
RANGE("CM5").SELECT
ActiveCell.FormulaR1C1 = 
"=CORREL('FILTERED RAW DATA 1'!R[-3]C[72]:R[4995]C[72],'FILTERED RAW DATA 1'!R[-3]C[76]:R[4995]C[76])
RANGE("CM6").SELECT
ActiveCell.FormulaR1C1 = 
"=CORREL('FILTERED RAW DATA 1'!R[-4]C[72]:R[4994]C[72],'FILTERED RAW DATA 1'!R[-4]C[87]:R[4994]C[87])

Open in new window

LVL 1
EscanabaAsked:
Who is Participating?
 
SiddharthRoutCommented:
Another interesting way to do it is this

Sub Test()
    Dim i As Long, j As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    
    j = 4997
    
    For i = 3 To 6
        Range("CM" & i).FormulaR1C1 = "=CORREL('FILTERED RAW DATA 1'!R[-" & _
                                      i - 2 & _
                                     "]C[72]:R[" & _
                                     j & _
                                     "]C[72],'FILTERED RAW DATA 1'!R[-" & _
                                     i - 2 & _
                                     "]C[75]:R[" & j & "]C[75])"
        
        j = j - 1
    Next i
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
UNTESTED

Please Try this

Range("CM3:CM6").FormulaR1C1 = _
"=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])"

Sid
0
 
EscanabaAuthor Commented:
Sid - Thanks for the quick response.  Your option would duplicate the 1st string for the entire range.  Unfortunately that wouldnt work.  Each range is different.  I can see where my code got cut off when I posted the question so maybe this will show everything:
Range("CM3").Select  
ActiveCell.FormulaR1C1 = _  
"=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])  
RANGE("CM4").SELECT  
ActiveCell.FormulaR1C1 =  
"=CORREL('FILTERED RAW DATA 1'!R[-2]C[72]:R[4996]C[72],'FILTERED RAW DATA 1'!R[-2]C[73]:R[4996]C[73])  
RANGE("CM5").SELECT  
ActiveCell.FormulaR1C1 =  
"=CORREL('FILTERED RAW DATA 1'!R[-3]C[72]:R[4995]C[72],'FILTERED RAW DATA 1'!R[-3]C[76]:R[4995]C[76])  
RANGE("CM6").SELECT  
ActiveCell.FormulaR1C1 =  
"=CORREL('FILTERED RAW DATA 1'!R[-4]C[72]:R[4994]C[72],'FILTERED RAW DATA 1'
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
SiddharthRoutCommented:
If you can copy the formula from the cell directly and paste it here from Cell CM3 and CM4 then I can give you an exact code.

Sid
0
 
SiddharthRoutCommented:
Seems like we both posted at the same time :)

Let me go through it again :)

Sid
0
 
SiddharthRoutCommented:
>>> Note that the display/events/screen updates are turned off/on already.

Also Set the calculation to manual before you run the code and set it to automatic after you have finished. Let me create a sample code for you...

Sid
0
 
SiddharthRoutCommented:
Since the range is different every where my suggestion would be to use this.

1) Like I mentioned setting calculation to manual
2) Avoid .SELECT. Directly perform the operation.

Something like

Sub Test()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With

    Range("CM3").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])"
    
    Range("CM4").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-2]C[72]:R[4996]C[72],'FILTERED RAW DATA 1'!R[-2]C[73]:R[4996]C[73])"
    
    Range("CM5").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-3]C[72]:R[4995]C[72],'FILTERED RAW DATA 1'!R[-3]C[76]:R[4995]C[76])"
    
    Range("CM6").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-4]C[72]:R[4994]C[72],'FILTERED RAW DATA 1'!R[-4]C[87]:R[4994]C[87])"

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

Open in new window


Sid
0
 
EscanabaAuthor Commented:
Ill test t out and get back with you shortly.  Thanks!
0
 
EscanabaAuthor Commented:
Thanks, Sid.  Worked out perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.