  # 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.

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
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)
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
``````
JE-EE-corr-matrix-by-group.xlsx
Microsoft ExcelScripting LanguagesVisual Basic Classic Last Comment
dlmille m4trix  THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform. dlmille 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 m4trix Dave, that approach sounds very familiar ;-) lol justearth m4trix:
thanks testing now.
dlmille:
Yes.

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

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

Dave Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY           