VBA to enter text into blank cells within a defined range

[excelgeek2023]

I have looked and looked but cant find a solution to this one.

I have a defined range of cells and wherever there is a blank cell, I want to enter text.

I am sure it will be so simple but I only know wo write macros from recording my actions, but this range, although will remain the same, the data within will change and therefore the blanks cells will be different each time.

This macro will need to run within another macro, but essentially my data will have column headers, thought about putting a filter on within the macro, but the cells that are blank will not be the same each time.

Please can anyone assist?

Thanks in advance!

 

[Joe4]

Welcome to the Board!

What exactly do you want to put in these blank cells?
How do we determine which range to look for the blank cells that need to be populated (we really need to know/understand your data structure; a detailed example may be helpful here)?

 

[excelgeek2023]

My range will be greater than the example below, but for this purpose, I would need to enter any text into the blank cells. Really it only needs to be Col.E as my next step would be to enter "1" in cell F3 and autofill down. It is all part or a bigger macro that i am working on....which the way I have worked out how to do it, requires the action above. I will explain further below the full story! If someone has the time to advise on simplifying my whole process then even better and a great learning experience for me! As previously said, I can only write VBA by recording macros.....

I need to get my month by month data populated by column, eg. Jan>Feb>Mar etc

 

[excelgeek2023]

to

Jan
Feb
Mar etc

I have built the attached table from numerous other sheets, collated here but for another report, it needs to be in column fashion. I can't filter to "do not show" blanks and then copy the data as each time this values etc are updated the number of rows/range to be copied and pasted will be different.

For this example, I know that my data rows will never exceed 100 rows, I had therefore created a number of Named ranges for each month and the other columns (all the yellow ones), but to do this i had to include columns that had blank cells.

My macro created a new sheet, copied/named the column headers i needed, then copied the named ranges for El1to3 and CostDriver. I then wanted to add 1 to colF (as above) and autofill down but i wanted the autofill to go all the way to row 100 so that when i told the macro to go to range Copy_Jan, it would all be aligned.....copy all the other months and use autofill for the periods, add another column for a lookup in another report and create a pivot table. I have attached my current macro that "works" to a degree....
Not sure if i am explaining myself well enough! I have attached what i have and what i need to get to

Data:

MrExcel.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	EL1	EL2	EL3	Cost Driver	2024_GBP 1	2024_GBP 2	2024_GBP 3	2024_GBP 4	2024_GBP 5	2024_GBP 6	2024_GBP 7	2024_GBP 8	2024_GBP 9	2024_GBP 10	2024_GBP 11	2024_GBP 12
2	40001	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
3	40004	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
4	40005	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
5	40010	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
6	40051	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
7	40121	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
8	40201	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
9	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
10	40401	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
11	40402	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
12	40600	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
13	40601	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
14	40602	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
15	40605	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
16	42003	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
17	42005	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
18	43002	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
19	43046	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
20	43062	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
21	44001	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
22	44004	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
23	44005	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
24	44010	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
25	44101	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
26	44201	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
27	44202	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
28	44210	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
29	44211	9312	503	POF	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
30	44212	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
31	44301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
32	45001	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
33	45002	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
34	45003	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
35	45004	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
36	45005	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
37	46003	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
38	47001	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
39	47012	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
40	47013	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
41	47014	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
42	47101	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
43	47301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
44	48001	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
45	48020	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
46	48201	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
47	48952	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
48	49049	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
49	49059	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
50	49099	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
51	49990	9312	503	POF	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
52	40001	9481	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
53	40005	9481	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
54	40111	9481	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
55	42003	9481	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
56	44201	9481	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
57	48952	9481	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
58	40602	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
59	40301	9312	503	DPW	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
60	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
61	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
62	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
63	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
64	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
65	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
66	40301	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
67	40601	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
68	40601	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
69	40601	9312	503	POFM	4.44	8.89	4.44	8.89	13.33	17.78	22.22	26.67	31.11	35.56	40.00	44.44
70					-	-	-	-	-	-	-	-	-	-	-	-
71					-	-	-	-	-	-	-	-	-	-	-	-
72					-	-	-	-	-	-	-	-	-	-	-	-
73					-	-	-	-	-	-	-	-	-	-	-	-
74					-	-	-	-	-	-	-	-	-	-	-	-
75					-	-	-	-	-	-	-	-	-	-	-	-
76					-	-	-	-	-	-	-	-	-	-	-	-
77					-	-	-	-	-	-	-	-	-	-	-	-
78					-	-	-	-	-	-	-	-	-	-	-	-
79					-	-	-	-	-	-	-	-	-	-	-	-
80					-	-	-	-	-	-	-	-	-	-	-	-
81					-	-	-	-	-	-	-	-	-	-	-	-
82					-	-	-	-	-	-	-	-	-	-	-	-
83					-	-	-	-	-	-	-	-	-	-	-	-
84					-	-	-	-	-	-	-	-	-	-	-	-
85					-	-	-	-	-	-	-	-	-	-	-	-
86					-	-	-	-	-	-	-	-	-	-	-	-
87					-	-	-	-	-	-	-	-	-	-	-	-
88					-	-	-	-	-	-	-	-	-	-	-	-
89					-	-	-	-	-	-	-	-	-	-	-	-
90					-	-	-	-	-	-	-	-	-	-	-	-
91					-	-	-	-	-	-	-	-	-	-	-	-
92					-	-	-	-	-	-	-	-	-	-	-	-
93					-	-	-	-	-	-	-	-	-	-	-	-
94					-	-	-	-	-	-	-	-	-	-	-	-
95					-	-	-	-	-	-	-	-	-	-	-	-
96					-	-	-	-	-	-	-	-	-	-	-	-
97					-	-	-	-	-	-	-	-	-	-	-	-
98					-	-	-	-	-	-	-	-	-	-	-	-
99					-	-	-	-	-	-	-	-	-	-	-	-
100					-	-	-	-	-	-	-	-	-	-	-	-
Mr Excel Query

 

[excelgeek2023]

Desired outcome:

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1										Sum of Value
2	Co-EL1.EL2.EL3-Cost Driver:Period	Co	EL1	EL2	EL3	Cost Driver	Period	Value		Co-EL1.EL2.EL3-Cost Driver:Period	Total
3	01-40001.9312.503-POFM:1	01	40001	9312	503	POFM	1	4.44444		01-.....-.:1	0
4	01-40004.9312.503-POFM:1	01	40004	9312	503	POFM	1	4.44444		01-.....-.:2	0
5	01-40005.9312.503-POFM:1	01	40005	9312	503	POFM	1	4.44444		01-.....-.:3	0
6	01-40010.9312.503-POFM:1	01	40010	9312	503	POFM	1	4.44444		01-.....-.:4	0
7	01-40051.9312.503-POFM:1	01	40051	9312	503	POFM	1	4.44444		01-.....-.:5	0
8	01-40121.9312.503-POFM:1	01	40121	9312	503	POFM	1	4.44444		01-.....-.:6	0
9	01-40201.9312.503-POFM:1	01	40201	9312	503	POFM	1	4.44444		01-.....-.:7	0
10	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-.....-.:8	0
11	01-40401.9312.503-POFM:1	01	40401	9312	503	POFM	1	4.44444		01-.....-.:9	0
12	01-40402.9312.503-POFM:1	01	40402	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:1	4.444444444
13	01-40600.9312.503-POFM:1	01	40600	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:10	35.55555556
14	01-40601.9312.503-POFM:1	01	40601	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:11	40
15	01-40602.9312.503-POFM:1	01	40602	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:12	44.44444444
16	01-40605.9312.503-POFM:1	01	40605	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:2	8.888888889
17	01-42003.9312.503-POFM:1	01	42003	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:3	4.444444444
18	01-42005.9312.503-POFM:1	01	42005	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:4	8.888888889
19	01-43002.9312.503-POFM:1	01	43002	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:5	13.33333333
20	01-43046.9312.503-POFM:1	01	43046	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:6	17.77777778
21	01-43062.9312.503-POFM:1	01	43062	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:7	22.22222222
22	01-44001.9312.503-POFM:1	01	44001	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:8	26.66666667
23	01-44004.9312.503-POFM:1	01	44004	9312	503	POFM	1	4.44444		01-40001.9312.503-POFM:9	31.11111111
24	01-44005.9312.503-POFM:1	01	44005	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:1	4.444444444
25	01-44010.9312.503-POFM:1	01	44010	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:10	35.55555556
26	01-44101.9312.503-POFM:1	01	44101	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:11	40
27	01-44201.9312.503-POFM:1	01	44201	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:12	44.44444444
28	01-44202.9312.503-POFM:1	01	44202	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:2	8.888888889
29	01-44210.9312.503-POFM:1	01	44210	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:3	4.444444444
30	01-44211.9312.503-POF:1	01	44211	9312	503	POF	1	4.44444		01-40001.9481.503-POFM:4	8.888888889
31	01-44212.9312.503-POFM:1	01	44212	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:5	13.33333333
32	01-44301.9312.503-POFM:1	01	44301	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:6	17.77777778
33	01-45001.9312.503-POFM:1	01	45001	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:7	22.22222222
34	01-45002.9312.503-POFM:1	01	45002	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:8	26.66666667
35	01-45003.9312.503-POFM:1	01	45003	9312	503	POFM	1	4.44444		01-40001.9481.503-POFM:9	31.11111111
36	01-45004.9312.503-POFM:1	01	45004	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:1	4.444444444
37	01-45005.9312.503-POFM:1	01	45005	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:10	35.55555556
38	01-46003.9312.503-POFM:1	01	46003	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:11	40
39	01-47001.9312.503-POFM:1	01	47001	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:12	44.44444444
40	01-47012.9312.503-POFM:1	01	47012	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:2	8.888888889
41	01-47013.9312.503-POFM:1	01	47013	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:3	4.444444444
42	01-47014.9312.503-POFM:1	01	47014	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:4	8.888888889
43	01-47101.9312.503-POFM:1	01	47101	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:5	13.33333333
44	01-47301.9312.503-POFM:1	01	47301	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:6	17.77777778
45	01-48001.9312.503-POFM:1	01	48001	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:7	22.22222222
46	01-48020.9312.503-POFM:1	01	48020	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:8	26.66666667
47	01-48201.9312.503-POFM:1	01	48201	9312	503	POFM	1	4.44444		01-40004.9312.503-POFM:9	31.11111111
48	01-48952.9312.503-POFM:1	01	48952	9312	503	POFM	1	4.44444		01-40005.9312.503-POFM:1	4.444444444
49	01-49049.9312.503-POFM:1	01	49049	9312	503	POFM	1	4.44444		01-40005.9312.503-POFM:10	35.55555556
50	01-49059.9312.503-POFM:1	01	49059	9312	503	POFM	1	4.44444		01-40005.9312.503-POFM:11	40
51	01-49099.9312.503-POFM:1	01	49099	9312	503	POFM	1	4.44444		01-40005.9312.503-POFM:12	44.44444444
52	01-49990.9312.503-POF:1	01	49990	9312	503	POF	1	4.44444		01-40005.9312.503-POFM:2	8.888888889
53	01-40001.9481.503-POFM:1	01	40001	9481	503	POFM	1	4.44444		01-40005.9312.503-POFM:3	4.444444444
54	01-40005.9481.503-POFM:1	01	40005	9481	503	POFM	1	4.44444		01-40005.9312.503-POFM:4	8.888888889
55	01-40111.9481.503-POFM:1	01	40111	9481	503	POFM	1	4.44444		01-40005.9312.503-POFM:5	13.33333333
56	01-42003.9481.503-POFM:1	01	42003	9481	503	POFM	1	4.44444		01-40005.9312.503-POFM:6	17.77777778
57	01-44201.9481.503-POFM:1	01	44201	9481	503	POFM	1	4.44444		01-40005.9312.503-POFM:7	22.22222222
58	01-48952.9481.503-POFM:1	01	48952	9481	503	POFM	1	4.44444		01-40005.9312.503-POFM:8	26.66666667
59	01-40602.9312.503-POFM:1	01	40602	9312	503	POFM	1	4.44444		01-40005.9312.503-POFM:9	31.11111111
60	01-40301.9312.503-DPW:1	01	40301	9312	503	DPW	1	4.44444		01-40005.9481.503-POFM:1	4.444444444
61	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:10	35.55555556
62	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:11	40
63	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:12	44.44444444
64	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:2	8.888888889
65	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:3	4.444444444
66	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:4	8.888888889
67	01-40301.9312.503-POFM:1	01	40301	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:5	13.33333333
68	01-40601.9312.503-POFM:1	01	40601	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:6	17.77777778
69	01-40601.9312.503-POFM:1	01	40601	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:7	22.22222222
70	01-40601.9312.503-POFM:1	01	40601	9312	503	POFM	1	4.44444		01-40005.9481.503-POFM:8	26.66666667
71	01-.....-.:1	01	.	.	.	.	1	0		01-40005.9481.503-POFM:9	31.11111111
72	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:1	4.444444444
73	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:10	35.55555556
74	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:11	40
75	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:12	44.44444444
76	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:2	8.888888889
77	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:3	4.444444444
78	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:4	8.888888889
79	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:5	13.33333333
80	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:6	17.77777778
81	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:7	22.22222222
82	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:8	26.66666667
83	01-.....-.:1	01	.	.	.	.	1	0		01-40010.9312.503-POFM:9	31.11111111
84	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:1	4.444444444
85	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:10	35.55555556
86	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:11	40
87	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:12	44.44444444
88	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:2	8.888888889
89	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:3	4.444444444
90	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:4	8.888888889
91	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:5	13.33333333
92	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:6	17.77777778
93	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:7	22.22222222
94	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:8	26.66666667
95	01-.....-.:1	01	.	.	.	.	1	0		01-40051.9312.503-POFM:9	31.11111111
96	01-.....-.:1	01	.	.	.	.	1	0		01-40111.9481.503-POFM:1	4.444444444
97	01-.....-.:1	01	.	.	.	.	1	0		01-40111.9481.503-POFM:10	35.55555556
98	01-.....-.:1	01	.	.	.	.	1	0		01-40111.9481.503-POFM:11	40
99	01-.....-.:1	01	.	.	.	.	1	0		01-40111.9481.503-POFM:12	44.44444444
100	01-.....-.:1	01	.	.	.	.	1	0		01-40111.9481.503-POFM:2	8.888888889
101	01-.....-.:1	01	.	.	.	.	1	0		01-40111.9481.503-POFM:3	4.444444444
102	01-40001.9312.503-POFM:2	01	40001	9312	503	POFM	2	8.88889		01-40111.9481.503-POFM:4	8.888888889
103	01-40004.9312.503-POFM:2	01	40004	9312	503	POFM	2	8.88889		01-40111.9481.503-POFM:5	13.33333333
104	01-40005.9312.503-POFM:2	01	40005	9312	503	POFM	2	8.88889		01-40111.9481.503-POFM:6	17.77777778
105	01-40010.9312.503-POFM:2	01	40010	9312	503	POFM	2	8.88889		01-40111.9481.503-POFM:7	22.22222222
106	01-40051.9312.503-POFM:2	01	40051	9312	503	POFM	2	8.88889		01-40111.9481.503-POFM:8	26.66666667
107	01-40121.9312.503-POFM:2	01	40121	9312	503	POFM	2	8.88889		01-40111.9481.503-POFM:9	31.11111111
108	01-40201.9312.503-POFM:2	01	40201	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:1	4.444444444
109	01-40301.9312.503-POFM:2	01	40301	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:10	35.55555556
110	01-40401.9312.503-POFM:2	01	40401	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:11	40
111	01-40402.9312.503-POFM:2	01	40402	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:12	44.44444444
112	01-40600.9312.503-POFM:2	01	40600	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:2	8.888888889
113	01-40601.9312.503-POFM:2	01	40601	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:3	4.444444444
114	01-40602.9312.503-POFM:2	01	40602	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:4	8.888888889
115	01-40605.9312.503-POFM:2	01	40605	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:5	13.33333333
116	01-42003.9312.503-POFM:2	01	42003	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:6	17.77777778
117	01-42005.9312.503-POFM:2	01	42005	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:7	22.22222222
118	01-43002.9312.503-POFM:2	01	43002	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:8	26.66666667
119	01-43046.9312.503-POFM:2	01	43046	9312	503	POFM	2	8.88889		01-40121.9312.503-POFM:9	31.11111111
120	01-43062.9312.503-POFM:2	01	43062	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:1	4.444444444
121	01-44001.9312.503-POFM:2	01	44001	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:10	35.55555556
122	01-44004.9312.503-POFM:2	01	44004	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:11	40
123	01-44005.9312.503-POFM:2	01	44005	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:12	44.44444444
124	01-44010.9312.503-POFM:2	01	44010	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:2	8.888888889
125	01-44101.9312.503-POFM:2	01	44101	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:3	4.444444444
126	01-44201.9312.503-POFM:2	01	44201	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:4	8.888888889
127	01-44202.9312.503-POFM:2	01	44202	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:5	13.33333333
128	01-44210.9312.503-POFM:2	01	44210	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:6	17.77777778
129	01-44211.9312.503-POF:2	01	44211	9312	503	POF	2	8.88889		01-40201.9312.503-POFM:7	22.22222222
130	01-44212.9312.503-POFM:2	01	44212	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:8	26.66666667
131	01-44301.9312.503-POFM:2	01	44301	9312	503	POFM	2	8.88889		01-40201.9312.503-POFM:9	31.11111111
132	01-45001.9312.503-POFM:2	01	45001	9312	503	POFM	2	8.88889		01-40301.9312.503-DPW:1	4.444444444
133	01-45002.9312.503-POFM:2	01	45002	9312	503	POFM	2	8.88889		01-40301.9312.503-DPW:10	35.55555556
134	01-45003.9312.503-POFM:2	01	45003	9312	503	POFM	2	8.88889		01-40301.9312.503-DPW:11	40
135	01-45004.9312.503-POFM:2	01	45004	9312	503	POFM	2	8.88889		01-40301.9312.503-DPW:12	44.44444444
Mr Excel Desired outcome

Cell Formulas
Range	Formula
A2:A135	A2	=B2&"-"&C2&"."&D2&"."&E2&"-"&F2&":"&G2

 

[Joe4]

OK, since you are adept at recording macros, there may be something you can do here to do what you want.

If you select a range (i.e. E3:E100) and then hit the F5 key, select "Special", then click the "Blanks" option, and click "OK", it will select ALL blank cells in that range.
Then, if you enter a value in and hit CTRL+ENTER, it will populate all those blank cells with that value.
It sounds like that may be what you are looking for.

If you record that code and fix it up a little, it can be done in one line of VBA code like this:

Range("E3:E100").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=1"

 

[excelgeek2023]

OK, since you are adept at recording macros, there may be something you can do here to do what you want.

If you select a range (i.e. E3:E100) and then hit the F5 key, select "Special", then click the "Blanks" option, and click "OK", it will select ALL blank cells in that range.
Then, if you enter a value in and hit CTRL+ENTER, it will populate all those blank cells with that value.
It sounds like that may be what you are looking for.

If you record that code and fix it up a little, it can be done in one line of VBA code like this:

Range("E3:E100").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=1"

thank you so much!! Solved my problem without having to rewrite my macros

 

[Joe4]

You are welcome!
Glad I was able to help!