First, there's a mistake; the line
should not be there at all - I forgot to delete it.
You dont have to declare variables. I declared one (myRng) because I wanted it to be a range (not a variant) because before anything is assigned to it it is Nothing rather than Empty and I wanted to test for that in the line
If Not myRng Is Nothing Then
re:
SexColm = Application.Match("Sex", SceHeaders, 0)
In the attached, on sheet List3 cell F3, I've put the equivalent function:
=MATCH("Sex",G7:O7,0)
It tells me which column number the Sex column is in the source range, and I use it later in:
SexR1C1 = DataBody.Columns(SexColm).Address(ReferenceStyle:=xlR1C1)
SexR1C1 becomes the address of the source range Sex column, in R1C1 style (rather then A1 style eg. R8C8:R23C8 rather than $H$8:$H$23). I could have used the A1 style references but when you record a macro of you entering a formula you get the likes of this (this was on the List3 sheet):
Range("J28:J31").Select
Selection.FormulaR1C1 = "=RC[-1]/SUM(R28C9:R31C9)"
which can be condensed to:
Range("J28:J31").FormulaR1C1 = "=RC[-1]/SUM(R28C9:R31C9)"
so I just stayed with that.
You'll notice the R28C9:R31C9, it's this sort of thing I put into a variable and use in composing a formula.
The variables SexColm, StateColm & AgeColm didn't really need to be variables (I only use each one once elsewhere in the code). In general I put things into variables (a) if I'm going to use it more than once and (b) if it helps me to understand/write my own code! On the other hand, setting those variables separately does allow some error checking in the case that the header is not found (but I haven't done any error checking).
In Module2 of the attached I've added a variation of the macro (blah2) which is a little shorter; it extends the quartile loop from 1 to 3 to 0 to 4 because quartile 0 is the minimum and quartile 4 is the maximum, so we no longer need specific max/min formulae. See the two summary tables in the List3 sheet. Also it does a single fill down of 6 columns instead of doing it 1 column at a time.
To help understand code execution, you can step through the code with F8 on the keyboard while viewing the values of variables in the Locals pane of the VBE:
2021-12-23_171938.jpg
You can also sprinkle some Select statements and MsgBox statements so you can watch on the sheet where things are. I've done that in both macros in the attached and postfixed those lines with 'debug line so that you delete/deactivate them later (because they just get in the way).