justearth
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:
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
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
JE-EE-corr-matrix-by-group.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dave, that approach sounds very familiar ;-) lol
ASKER
m4trix:
thanks testing now.
dlmille:
Yes.
Cheers,
JE
thanks testing now.
dlmille:
Yes.
Cheers,
JE
ASKER
m4trix:
Thanks this works how I wanted. It took me awhile to explain well I think.
Cheers,
JE
Thanks this works how I wanted. It took me awhile to explain well I think.
Cheers,
JE
ASKER
Thanks again.
no problem. It was actually a fun little challenge :)
nicely done :)
Dave
Dave
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