Fill numbers after that search character.

[Kishan]

Using Excel 2000

Hi,

Data are the numbers shown in the cells E6:E15, which I want, could be filled in column D,

Part1-counting the cell down to D6, if D6 = 0 than 5 will be filled in cell D10 counting to down 12 will be filled in cell D22 and so on for rest of the numbers are in E6:E15...

Part-2- this is bit awkward where each number is filled in the column D I want to be filled below that number character from the next row of column C
For example if 5 is filled in cell D10 so it find character in column c from the next row and place it below the number 5 in cell D11 and so on...

Data example...

Book1
	A	B	C	D	E	F
1
2
3
4
5			C1	Insert Row n & Char	Data
6			X		5
7			X		12
8			X		11
9			2		4
10			1	5	5
11			X	X	5
12			X		7
13			1		6
14			1		3
15			1		14
16			X
17			1
18			1
19			X
20			1
21			1
22			2	12
23			1	1
24			X
25			1
26			X
27			1
28			1
29			1
30			1
31			1
32			1
33			2	11
34			1	1
35			2
36			1
37			X
38			X	4
39			X	X
40			1
41			X
42			2
43			X
44			X	5
45			1	1
46			1
47			2
48			1
49			X
50			X	5
51			X	X
52			X
53			X
54			2
55			X
56			1
57			X	7
58			1	1
59			1
60			2
61			X
62			2
63			1
64			1	6
65			1	1
66			X
67			X	3
68			1	1
69			X
70			1
71			1
72			1
73			1
74			1
75			1
76			1
77			2
78			1
79			1
80			1
81			2
82			2	14
83			X	X
84			1
85			X
86			1
87			1
88			1
89
Sheet1

Thank you in advance

Regards,
Kishan

 

[wideboydixon]

Re: Bit awkward fill numbers after that search character.

Your description and your sample don't seem to match. According to your description the 4 should be in row 37 (33+4) but you have it in row 38. Assuming that you made a mistake filling out your table then you can use this macro:

Public Sub BitAwkwardFill()

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long

lastRow = Cells(Rows.Count, 5).End(xlUp).Row
nextRow = 5
For thisRow = 6 To lastRow
    nextRow = nextRow + Cells(thisRow, 5).Value
    Cells(nextRow, 4).Value = Cells(thisRow, 5).Value
    Cells(nextRow + 1, 4).Value = Cells(nextRow + 1, 3).Value
Next thisRow

End Sub

WBD

 

[oldbrewer]

Re: Bit awkward fill numbers after that search character.

	A	B	C	D	E	F
1
2
3
4
5			C1	Insert Row nº & Char	Data
6			X		5	10	11
7			X		12	22	23
8			X		11	33	34
9			2		4	37	38
10			1	5	5	42	43
11			X	X	5	47	48
12			X		7	54	55
13			1		6	60	61
14			1		3	63	64
15			1		14	77	78
16			X
17			1
18			1
19			X
20			1
21			1
22			2	12				for j = 7 to 16
23			1	1				cells(cells(j,8),5)=cells(j,6)
24			X					cells(cells(j,8)+1,5)=cells(cells(j,8)+1,4
25			1					next j
26			X
27			1
28			1					not tested but the 2 helper columns make it really simple
29			1
30			1
31			1
32			1
33			2	11
34			1	1
35			2
36			1
37			X
38			X	4
39			X	X
40			1
41			X
42			2
43			X
44			X	5
45			1	1

<colgroup><col span="3"><col><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[Kishan]

Re: Bit awkward fill numbers after that search character.

Your description and your sample don't seem to match. According to your description the 4 should be in row 37 (33+4) but you have it in row 38. Assuming that you made a mistake filling out your table then you can use this macro:

Public Sub BitAwkwardFill()

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long

lastRow = Cells(Rows.Count, 5).End(xlUp).Row
nextRow = 5
For thisRow = 6 To lastRow
    nextRow = nextRow + Cells(thisRow, 5).Value
    Cells(nextRow, 4).Value = Cells(thisRow, 5).Value
    Cells(nextRow + 1, 4).Value = Cells(nextRow + 1, 3).Value
Next thisRow

End Sub

WBD

Amazing!! wideboydixon, I just noticed that being a mistaken in D37 all the down way is incorrect. Sorry for the incontinence.

Your code is resulting without any error and giving the result, as it should be.

I appreciate a lot your help

Kind regards,
Kishan

 

[Kishan]

Re: Bit awkward fill numbers after that search character.

not tested but the 2 helper columns make it really simple

Hi oldbrewer, thank you for your help please could you tell me how this work I could not find the way

Kind regards,
Kishan

 

[oldbrewer]

Re: Bit awkward fill numbers after that search character.

you paste those 4 lines of code into a macro - tools, record macro, select A1, stop macro

then tools macro edit macro, and delete only the line range(A1).select and paste in my 4 lines of code

 

[Kishan]

Re: Bit awkward fill numbers after that search character.

you paste those 4 lines of code into a macro - tools, record macro, select A1, stop macro

then tools macro edit macro, and delete only the line range(A1).select and paste in my 4 lines of code

Hi oldbrewer, thank you for guiding got it work.

Kind regards,
Kishan

 

[oldbrewer]

Re: Bit awkward fill numbers after that search character.

you are most welcome !