vba to calculate correlations from averages based on header criteria

[cjcass]

Hi,
Please have a look at the pic below and it's hopefully self-explanatory to a degree.
I'm looking for a macro I can run that will go to all the wkshts (tab names) in Col W in the wksht 'Results' and work out Correlations based on the Header Criteria in Cols AA and AB. So using the example the first part of the calculation is to work out the average across Row4 for the yellow criteria (6.5), and then the average for the green (4.0), then do the same the whole way down the table and give the Correlation for all the averages in each wksht. In this example for the wksht '1' the result is 0.43. The tables in each wksht cover the range A3:GR50000 but not every table will be that size, most will be <10000 rows down (at the moment).
Can this be achieved?
Any help much appreciated
PS. I've assumed a solution using cell formulas is probably not an option.

 

[cjcass]

sorry, don't follow this, could you maybe re-post the correct version of the code, many thanks.

 

[offthelip]

For debugging have a look at Chip pearson's excellent website;
http://www.cpearson.com/excel/DebuggingVBA.aspx
I spotted another error in that I wasn't picking up the "variables" table correctly, this will make a huge difference
The code change is as follows:

Sub test()
Dim outarr As Variant




Worksheets("Results").Select




tabnames = Range(Cells(7, 23), Cells(26, 24))
Variables = Range(Cells(7, 27), Cells(19, 28))
For i = 1 To 19
 With Worksheets(tabnames(i, 1))
   lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
   ReDim outarr(1 To lastrow, 1 To 2)
   
    Datar = Range(.Cells(1, 1), .Cells(lastrow, 200)) ' Colums A to Gr
    ' initialise output
    For j = 4 To lastrow
       outarr(j, 1) = 0
       outarr(j, 2) = 0
    Next j


    For j = 4 To lastrow
    cnt1 = 0
    cnt2 = 0
    For k = 1 To 13
     If Variables(k, 1) <> "" Then
      outarr(j, 1) = outarr(j, 1) + Datar(j, Variables(k, 1))
      cnt1 = cnt1 + 1
     End If
     If Variables(k, 2) <> "" Then
      outarr(j, 2) = outarr(j, 2) + Datar(j, Variables(k, 2))
      cnt2 = cnt2 + 1
     End If
    Next k
    ' calc average
    outarr(j, 1) = outarr(j, 1) / cnt1
    outarr(j, 2) = outarr(j, 2) / cnt2
    Next j
    
    Range(.Cells(1, 201), .Cells(lastrow, 202)) = outarr
    Range(.Cells(2, 201), .Cells(2, 201)).Formula = "=CORREL(GS4:GS" & lastrow & ",GT4:GT" & Lastrow & ")"
    
    tabnames(i, 2) = Range(.Cells(2, 201), .Cells(2, 201))
 End With
Next i








Range(Cells(7, 23), Cells(26, 24)) = tabnames




End Sub

 

[cjcass]

That works brilliantly!!
Thank you very much for your time on this

 

[cjcass]

Hi offthelip (if you're online),
I have transferred your code to adapt and use it in another file but have run into an error.
I increased the number of variables up to 20, the number of wksht tabs up to 50 and changed the positions of the variable table and results table also. The data tables in each wksht (1-50) have the same position/size. I followed what you had done before, (you'll see my changes below) and run the code but am getting a 'Run time error 13, Type Mismatch' at the following point in the code:
outarr(j, 1) = outarr(j, 1) + Datar(j, Variables(k, 1))

Is there anything I need to be aware of when adapting this to use elsewhere?

Dim outarr As Variant


Sheet93.Select


tabnames = Range(Cells(75, 11), Cells(124, 12)) 'this range ref, eg. Cells(7, 23) = W7
Variables = Range(Cells(75, 8), Cells(94, 9)) 'this range ref, eg. Cells(7, 23) = W7
For i = 1 To 50 'this is the number of tabs (for simple numerically named tabs)
 With Worksheets(tabnames(i, 1))
   lastRow = .Cells(Rows.Count, "A").End(xlUp).row
   ReDim outarr(1 To lastRow, 1 To 2)
   
    Datar = Range(.Cells(1, 1), .Cells(lastRow, 200)) ' Colums A to Gr
    ' initialise output
    For j = 4 To lastRow
       outarr(j, 1) = 0
       outarr(j, 2) = 0
    Next j


    For j = 4 To lastRow
    cnt1 = 0
    cnt2 = 0
    For k = 1 To 20 'this is the number of variables
     If Variables(k, 1) <> "" Then
      outarr(j, 1) = outarr(j, 1) + Datar(j, Variables(k, 1))
      cnt1 = cnt1 + 1
     End If
     If Variables(k, 2) <> "" Then
      outarr(j, 2) = outarr(j, 2) + Datar(j, Variables(k, 2))
      cnt2 = cnt2 + 1
     End If
    Next k
    ' calc average
    outarr(j, 1) = outarr(j, 1) / cnt1
    outarr(j, 2) = outarr(j, 2) / cnt2
    Next j
    
    Range(.Cells(1, 201), .Cells(lastRow, 202)) = outarr
    Range(.Cells(2, 201), .Cells(2, 201)).Formula = "=CORREL(GS4:GS" & lastRow & ",GT4:GT" & lastRow & ")"
    
    tabnames(i, 2) = Range(.Cells(2, 201), .Cells(2, 201))
 End With
Next i


Range(Cells(75, 11), Cells(124, 12)) = tabnames


End Sub

 

[cjcass]

In offthelip's absence, anyone else feel free to interject
many thanks

 

[offthelip]

Hi CJcass,
looking at your code what you have done is fine, however without being able to look at the data on your spreadsheet, I can't tell. What I suggest is run it until you get the error and then look at what values you have got in j ,k, outarr(j,1) , datar(j) and Variables(k,1) this should narrow down which array is going wrong.
Once you have found which array and where in the array hopefully you should be able to identify cause which is probaby incorrect addressing on the array or incorrect indexing.
Once you have got confident with this style of coding you will find it incredibly powerful

 

[cjcass]

ok, thanks for coming back.
you mentioned above; run the code and then look at what values you have got in j ,k, outarr(j,1) , datar(j) and Variables(k,1), where do I look to see what I have got for these values?
Thanks

 

[offthelip]

You need to use the debug facility see the link in post 12

 

[cjcass]

Am struggling with this as my experience of vba is limited, at the point the code stops there are no values input in any of the wkshts 1-50 and I'm getting the following:
Not sure if this helps anymore?

 

[offthelip]

Looking at the debug window you can see that I=3 this means that you have completed 2 loops of the code and problem in on the 3rd sheet. Also j= 6 so the problem is on the 6th row of this sheet.
K=3 so the problem is in the cells that is pointed to by the 3rd reference in the variables array, so this is the number which is in cells(78,8) {75+3=78} from

Variables = Range(Cells(75, 8), Cells(94, 9))

So look at cell H78 on your sheet, that should give you a column number (call it X ) then look at Row 6 column X on the 3rd sheet in the list and I suspect you will find something in that cell which is not a number
You do need to read more about how to use the debugger in VBA. One really useful facility when you get an error like this is: move your mouse so that it is “hovering” over each of the variables in the highlighted yellow area, as you do this the value of each of the variables will show up, or where there is an error it will show you the error.
The code I have written has got an extra level of indirection due to the use of the Variables array so do look at it carefully to fully understand how it works.