Link to home
Start Free TrialLog in
Avatar of justearth
justearthFlag for United States of America

asked on

Use Column Header Names Instead of Column Letters in Formula in a Macro

Hello,
The code below was in response to this question:

I would like to compute correlation matrices (using CORRELATE) for each group in a worksheet. The correlation matrices should be by group (5,6,7,12,13, or 14) on columns D - H.  Ideally the new sheet would be named with at least the group number.

The fact that it only works on D - H, is hanging me up.  Instead of using the "Column Letters" D - H, I'd like to insert the column header names (i.e. those in row 1).  

I have attached an example worksheet showing the 'data' and example output (e.g. CORREL on Columns D - H on Group 5).  I have been asking a few questions on this topic. I am trying to get at the core idea of how to make the macro more adaptable. I hope this is clear and explains my goal.

I thought if I could enter the column names then I could easily change what data is included in the CORREL formula within the macro.

Please advise,
Thanks,
Cheers,
JE

previous questions:
https://www.experts-exchange.com/questions/27040912/Update-Excel-Macro-for-More-Flexibility-Update-which-Columns-to-Use.html
https://www.experts-exchange.com/questions/27025172/Create-Correlation-Matrices-based-on-Groups-CORRELATE.html
Sub MakeMatrix()
    
    Dim arr() As Long
    Dim CurrentGroup As Long
    Dim SourceWs As Worksheet
    Dim LastR As Long
    Dim Counter As Long
    Dim DestWs As Worksheet
    
    Set SourceWs = ThisWorkbook.Worksheets("data")
    With SourceWs
        .[a1].Sort Key1:=.[a1], Order1:=xlAscending, Header:=xlYes
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        CurrentGroup = .Cells(2, 1)
        ReDim arr(1 To 3, 1 To 1) As Long
        arr(1, 1) = CurrentGroup
        arr(2, 1) = 2
        For Counter = 3 To LastR
            If .Cells(Counter, 1) <> CurrentGroup Then
                arr(3, UBound(arr, 2)) = Counter - 1
                ReDim Preserve arr(1 To 3, 1 To 1 + UBound(arr, 2)) As Long
                arr(1, UBound(arr, 2)) = .Cells(Counter, 1)
                arr(2, UBound(arr, 2)) = Counter
                CurrentGroup = .Cells(Counter, 1)
            ElseIf Counter = LastR Then
                arr(3, UBound(arr, 2)) = Counter
            End If
        Next
    End With
    
    For Counter = 1 To UBound(arr, 2)
        Set DestWs = ThisWorkbook.Worksheets.Add
        With DestWs
            .Name = "Group " & arr(1, Counter) & " Matrix"
           .[a2:a6] = Application.Transpose(SourceWs.[d1:h1])
           .[b1:f1].Value = SourceWs.[d1:h1].Value
           .Range("b2, c3, d4, e5, f6") = 1
           .[b3].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
                ",Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & ")"
           .[b4].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
                ",Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & ")"
           .[b5].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
                ",Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & ")"
           .[b6].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
                ",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
           .[c4].Formula = "=CORREL(Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & _
                ",Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & ")"
           .[c5].Formula = "=CORREL(Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & _
                ",Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & ")"
           .[c6].Formula = "=CORREL(Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & _
                ",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
           .[d5].Formula = "=CORREL(Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & _
                ",Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & ")"
           .[d6].Formula = "=CORREL(Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & _
                ",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
           .[e6].Formula = "=CORREL(Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & _
                ",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
        End With
    Next
    
    MsgBox "Done"
    
End Sub

Open in new window

JE-EE-corr-matrix-by-group.xlsx
ASKER CERTIFIED SOLUTION
Avatar of m4trix
m4trix
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm working on this (have to run to a meeting but will be back, shortly).   I'm going to assume a selection of random columns, then update the code based on that selection.

The selection will be an array of column headers:  dim columnNames() as string.

So if you select AREA1, AREA2, and SAREA5, you want the CORREL matrix based on that 3 x 3, correct?

Should not take long.

Dave
Dave, that approach sounds very familiar ;-) lol
Avatar of justearth

ASKER

m4trix:
thanks testing now.
dlmille:
Yes.

Cheers,
JE
m4trix:
Thanks this works how I wanted. It took me awhile to explain well I think.

Cheers,
JE
Thanks again.
no problem. It was actually a fun little challenge :)
nicely done :)

Dave