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]

Hi, thanks for your time on this.
It's a combination of being both new to debugging and also being able to fully grasp your code. Given your comment about a cell containing something other than a number and that I've adapted your code into a different spsht, I wanted to make sure that the code is still structured to deal with the tables in my new spsht, so in the pic below you can see what the tables look like. Although there are text entries in some columns these columns will never be chosen in the variable criteria - the criteria will only target columns with numerical values. To tie in with my debugging picture above, the new pic below shows wksht '3' and exactly what was/is in the table when the error occurred. I have a hunch that my table structure is an issue? Re. H78, you'll see there was no entry in this cell.

 

[offthelip]

You are going have to use the debugger to find out where this is going wrong, have you tried doing what I suggested and hovering over the variables in when the error occurs?

 

[cjcass]

Yes but I can't follow it see below...

 

[offthelip]

It clearly show the error: looking at your first yellow line outarr(j,1)=0, the next yellow bit show that Datar(j,(variables(k,1))="question"
so that line is:
outarr(j,1)= 0 + "question"
which is obbviously going to fail because you can't add "question" to any number.
The variables(k,1)=7 shows that the variables array is giving you a sensible column number, check the value in J to get the row number, then on the local window open up the Datar variable and check you have got the correct stuff in there. It certainly looks to me that this is where the error is. One clue is wher on your spreadsheet have got the text "question" because you are ending with that instead of a number.

 

[cjcass]

Ok, I think I may have uncovered the problem...
I have opened up the Datar variable as you have mentioned and found that the errors are all reading off a worksheet that isn't one of my worksheets '1-50'.
The worksheet it is looking at is named 'QBank' and it's codename is Sheet6, so in the VBEditor it's... Sheet6 (QBank). This sheet is the 3rd sheet in the workbook running left to right, which probably accounts for debugger targeting the 3rd sheet?
In my initial brief I described my worksheets as having tabnames '1-50' so that was a mistake on my part - I should have used proper terminology.
For clarity, the worksheet names '1-50' I'm targeting are as below:
Sheet8 (1)
Sheet9 (2)
Sheet10 (3)
Sheet12 (4)
Sheet13 (5)
Sheet15 (6)
and so on...
(notice that the codenames aren't fully sequential).
So, assuming this is the issue, is it possible to have the code target the 'Names' (written into the bottom tabs) rather than 'CodeNames' or the running order of the worksheets?
As always, any help much appreciated!

 

[offthelip]

The code does pick up the "names" as written at the bottom on the tabs. I tested it with names like Test1 and Test2 etc and it works fine. I think the problem is that Excel gets bit confused with names which are just a number and defaults to using that as an index into the worksheets
So I suggest renaming all the sheets with some textual name. This subroutine will rename all the sheets in your workbook by adding the text "data" on front. Do that and then change all the names in your list of sheet names. ( you do that using the formula ="Data" & W7 ) assuming a sheet name in w7
then run the code again

Sub test3()
Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
    shn = ws.Name
    ws.Name = "Data" & shn
 Next ws
 
End Sub

 

[cjcass]

Oh ok.
Unfortunately I can't realistically rename the sheets as I have a huge amount of other codes reading from them.
An example extract of one is below (the bit that refers to the wksht names) but I haven't got the ability/experience to be able to adapt your code using other codes in my wkbk.
Any last thoughts on it?

Dim wsReport As Worksheet
Dim wsData As Worksheet
Dim dataSheet As Long


'Clear previous results
Range("I15:L64").Select
    Selection.ClearContents
    Range("I15").Select


' Find the report sheet
Set wsReport = Worksheets("Levels")


' Process all data sheets
For dataSheet = 1 To 50
    ' Find the data sheet
    Set wsData = Nothing
    On Error Resume Next
    Set wsData = Worksheets(CStr(dataSheet))
    On Error GoTo 0
    
    ' Did we find the sheet
    If Not wsData Is Nothing Then
        ' Clear out the buckets
        For thisCol = 0 To 3
            buckets(thisCol) = 0
        Next thisCol

 

[offthelip]

The only way then is to change the number in your list of worksheets to the index number of the worksheet ( which is actually what is happening )
this little macro should help you sort out the relationship between index numbers and tab names
the problem is that

Worksheets ("Test")

is fairly obviously the tab

worksheets(1)

is obviously the index
however the reference

With Worksheets(tabnames(i, 1))

actually can be either if tabnames(i,1) is a number then it treats it as an index, if it is text then it treats it as a tabname

Sub test4()
cnt = ActiveWorkbook.Sheets.Count
 For i = 1 To cnt
  nam = Worksheets(i).Name
  MsgBox ("index=" & i & " tab=" & nam)
 Next i

End Sub

 

[offthelip]

I have just spotted the way you address the worksheets which should work for this macros as well try changing

With Worksheets(tabnames(i, 1))

to

With Worksheets(cstr(tabnames(i, 1)))

hopefully that will work. This is a problem I have never come across before. I live and learn: rule 125A : don't use numbers as Tab names

 

[cjcass]

Ok, have done just that and I think we're in business, it's looking good!!
Will run some tests on the correlations that are coming through but it looks sound
Cannot thank you enough for your time and persevering with me on this - I've learned a lot!
Many thanks indeed.