Macro for Excel Automatically Sort When Data Changes

[keranali]

need help with a macro or formula to automatically sort data as data is changed

the project.xlsx
	G	H
1	Number Frequency
2	23	4
3	26	5
4	1	6
5	16	6
6	25	6
7	9	7
8	17	7
9	29	7
10	31	7
11	2	8
12	7	8
13	24	8
14	8	9
15	12	9
16	15	9
17	28	9
18	3	10
19	4	10
20	5	10
21	6	10
22	22	10
23	33	10
24	36	10
25	27	11
26	32	11
27	14	12
28	18	12
29	19	12
30	20	12
31	21	12
32	34	12
33	11	13
34	35	13
35	10	14
36	13	14
37	30	15
Data

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(Data!D:D,23)
H3	H3	=COUNTIF(Data!D:D,26)
H4	H4	=COUNTIF(Data!D:D,1)
H5	H5	=COUNTIF(Data!D:D,16)
H6	H6	=COUNTIF(Data!D:D,25)
H7	H7	=COUNTIF(Data!D:D,9)
H8	H8	=COUNTIF(Data!D:D,17)
H9	H9	=COUNTIF(Data!D:D,29)
H10	H10	=COUNTIF(Data!D:D,31)
H11	H11	=COUNTIF(Data!D:D,2)
H12	H12	=COUNTIF(Data!D:D,7)
H13	H13	=COUNTIF(Data!D:D,24)
H14	H14	=COUNTIF(Data!D:D,8)
H15	H15	=COUNTIF(Data!D:D,12)
H16	H16	=COUNTIF(Data!D:D,15)
H17	H17	=COUNTIF(Data!D:D,28)
H18	H18	=COUNTIF(Data!D:D,3)
H19	H19	=COUNTIF(Data!D:D,4)
H20	H20	=COUNTIF(Data!D:D,5)
H21	H21	=COUNTIF(Data!D:D,6)
H22	H22	=COUNTIF(Data!D:D,22)
H23	H23	=COUNTIF(Data!D:D,33)
H24	H24	=COUNTIF(Data!D:D,36)
H25	H25	=COUNTIF(Data!D:D,27)
H26	H26	=COUNTIF(Data!D:D,32)
H27	H27	=COUNTIF(Data!D:D,14)
H28	H28	=COUNTIF(Data!D:D,18)
H29	H29	=COUNTIF(Data!D:D,19)
H30	H30	=COUNTIF(Data!D:D,20)
H31	H31	=COUNTIF(Data!D:D,21)
H32	H32	=COUNTIF(Data!D:D,34)
H33	H33	=COUNTIF(Data!D:D,11)
H34	H34	=COUNTIF(Data!D:D,35)
H35	H35	=COUNTIF(Data!D:D,10)
H36	H36	=COUNTIF(Data!D:D,13)
H37	H37	=COUNTIF(Data!D:D,30)

 

[keranali]

Ok thanks I have also re named "Number" to "Frequency" like this:

Set rNumberHeaderCell = .Cells.Find("Frequency", LookIn:=xlValues, SearchOrder:=xlByColumns)

I am assuming this script line finds the column to sort. When I run the VBA it is sorting the column however its not sorting column G so the numbers are becoming mispalced.

After the sort:

the project.xlsx
	G	H
1	Number	Frequency
2	23	4
3	26	5
4	1	6
5	16	6
6	25	6
7	9	7
8	17	7
9	31	7
10	29	8
11	2	8
12	7	8
13	24	9
14	8	9
15	12	9
16	15	9
17	28	10
18	5	10
19	22	10
20	33	10
21	36	10
22	3	11
23	4	11
24	6	11
25	32	11
26	27	12
27	14	12
28	19	12
29	20	12
30	21	12
31	18	13
32	34	13
33	11	13
34	35	14
35	10	15
36	13	15
37	30	18
Data

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(Data!D:D,23)
H3	H3	=COUNTIF(Data!D:D,26)
H4	H4	=COUNTIF(Data!D:D,1)
H5	H5	=COUNTIF(Data!D:D,16)
H6	H6	=COUNTIF(Data!D:D,25)
H7	H7	=COUNTIF(Data!D:D,9)
H8	H8	=COUNTIF(Data!D:D,17)
H9	H9	=COUNTIF(Data!D:D,31)
H10	H10	=COUNTIF(Data!D:D,29)
H11	H11	=COUNTIF(Data!D:D,2)
H12	H12	=COUNTIF(Data!D:D,7)
H13	H13	=COUNTIF(Data!D:D,24)
H14	H14	=COUNTIF(Data!D:D,8)
H15	H15	=COUNTIF(Data!D:D,12)
H16	H16	=COUNTIF(Data!D:D,15)
H17	H17	=COUNTIF(Data!D:D,28)
H18	H18	=COUNTIF(Data!D:D,5)
H19	H19	=COUNTIF(Data!D:D,22)
H20	H20	=COUNTIF(Data!D:D,33)
H21	H21	=COUNTIF(Data!D:D,36)
H22	H22	=COUNTIF(Data!D:D,3)
H23	H23	=COUNTIF(Data!D:D,4)
H24	H24	=COUNTIF(Data!D:D,6)
H25	H25	=COUNTIF(Data!D:D,32)
H26	H26	=COUNTIF(Data!D:D,27)
H27	H27	=COUNTIF(Data!D:D,14)
H28	H28	=COUNTIF(Data!D:D,19)
H29	H29	=COUNTIF(Data!D:D,20)
H30	H30	=COUNTIF(Data!D:D,21)
H31	H31	=COUNTIF(Data!D:D,18)
H32	H32	=COUNTIF(Data!D:D,34)
H33	H33	=COUNTIF(Data!D:D,11)
H34	H34	=COUNTIF(Data!D:D,35)
H35	H35	=COUNTIF(Data!D:D,13)
H36	H36	=COUNTIF(Data!D:D,30)
H37	H37	=COUNTIF(Data!D:D,10)

Before the sort :

the project.xlsx
	G	H
1	Number	Frequency
2	23	4
3	26	5
4	1	6
5	16	6
6	25	6
7	9	7
8	17	7
9	31	7
10	29	8
11	2	8
12	7	8
13	24	9
14	8	9
15	12	9
16	15	9
17	28	10
18	5	10
19	22	10
20	33	10
21	36	10
22	3	11
23	4	11
24	6	11
25	32	11
26	27	12
27	14	12
28	19	12
29	20	12
30	21	12
31	18	13
32	34	13
33	11	13
34	35	14
35	10	18
36	13	15
37	30	15
Data

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(Data!D:D,23)
H3	H3	=COUNTIF(Data!D:D,26)
H4	H4	=COUNTIF(Data!D:D,1)
H5	H5	=COUNTIF(Data!D:D,16)
H6	H6	=COUNTIF(Data!D:D,25)
H7	H7	=COUNTIF(Data!D:D,9)
H8	H8	=COUNTIF(Data!D:D,17)
H9	H9	=COUNTIF(Data!D:D,31)
H10	H10	=COUNTIF(Data!D:D,29)
H11	H11	=COUNTIF(Data!D:D,2)
H12	H12	=COUNTIF(Data!D:D,7)
H13	H13	=COUNTIF(Data!D:D,24)
H14	H14	=COUNTIF(Data!D:D,8)
H15	H15	=COUNTIF(Data!D:D,12)
H16	H16	=COUNTIF(Data!D:D,15)
H17	H17	=COUNTIF(Data!D:D,28)
H18	H18	=COUNTIF(Data!D:D,5)
H19	H19	=COUNTIF(Data!D:D,22)
H20	H20	=COUNTIF(Data!D:D,33)
H21	H21	=COUNTIF(Data!D:D,36)
H22	H22	=COUNTIF(Data!D:D,3)
H23	H23	=COUNTIF(Data!D:D,4)
H24	H24	=COUNTIF(Data!D:D,6)
H25	H25	=COUNTIF(Data!D:D,32)
H26	H26	=COUNTIF(Data!D:D,27)
H27	H27	=COUNTIF(Data!D:D,14)
H28	H28	=COUNTIF(Data!D:D,19)
H29	H29	=COUNTIF(Data!D:D,20)
H30	H30	=COUNTIF(Data!D:D,21)
H31	H31	=COUNTIF(Data!D:D,18)
H32	H32	=COUNTIF(Data!D:D,34)
H33	H33	=COUNTIF(Data!D:D,11)
H34	H34	=COUNTIF(Data!D:D,35)
H35	H35	=COUNTIF(Data!D:D,10)
H36	H36	=COUNTIF(Data!D:D,13)
H37	H37	=COUNTIF(Data!D:D,30)

Column G number 10 has a mismatch

 

[OaklandJim]

I'll try to look at the issue later today or tomorrow.

 

[OaklandJim]

First, it seems that the before and after you provided are the same?

I am curious, why did you change that part of the code? What are you trying to accomplish by doing that?

Anyway, as far as I can tell from your post that is the problem. The way the code is set up it looks for the Number column in column G and then assumes that the Frequency column is next to it in column H. By changing the header text sought -- from Number to Frequency -- the data in the Frequency column (column H) is treated as Number column by the sort and the Frequency column then becomes column I.

 

[keranali]

Your code worked perfect Changing the name of the Column header caused the issue thanks so much for the code.