Fill formula in the alternate rows and live values

[motilulla]

Fill formula in the alternate rows and live values

Hello,

I am looking VBA solution, which can fill formula in the alternate row and live only values

For example this formula =COUNTIF(Sheet1!$F$6:$F$63,$C6&" | "&$D6&" | "&F$4)
I want to fill in the range F6:T28 but using alternate rows what I mean fill row 6 in cells F6:T6 than leave the row 7 and fill in cells F8:T8 and so on in all the cells are shown with 0

Sample Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3
4					Data C->	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
5			Data A	Data B
6			0	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7
8			12	5		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9
10			2	9		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11
12			12	12		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13
14			13	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15
16			5	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17
18			6	5		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19
20			7	9		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21
22			8	25		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23
24			9	74		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25
26			10	18		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27
28			10	12		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
29
30
31
Sheet3

Cell Formulas
Range	Formula
F6	=COUNTIF(Sheet1!$F$6:$F$63,$C6&" | "&$D6&" | "&F$4)

Thank you all
Excel 2000
Regards,
Moti

 

[AlphaFrog]

[color=darkblue]Sub[/color] Fill_Formula()
    [color=darkblue]With[/color] Intersect(Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow, Columns("F:T"))
        .Formula = "=COUNTIF(Sheet1!$F$6:$F$63,$C6&"" | ""&$D6&"" | ""&F$4)"
        .Value = .Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[motilulla]

[COLOR=darkblue]Sub[/COLOR] Fill_Formula()
    [COLOR=darkblue]With[/COLOR] Intersect(Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow, Columns("F:T"))
        .Formula = "=COUNTIF(Sheet1!$F$6:$F$63,$C6&"" | ""&$D6&"" | ""&F$4)"
        .Value = .Value
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hello AlphaFrog,

Thank you for giving VBA solution, after I run the VBA getting only values 0

I tried using key F8 find at the line (.Formula = "=COUNTIF(Sheet1!$F$6:$F$63,$C6&"" | ""&$D6&"" | ""&F$4)" ) it fills formula in the alternate range and gives count where the value matches.
but as I hit F8 to line (.Value = .Value) all even count results converted in 0, I don't know what I am doing wrong

Please could you check?

Regards,
Moti

 

[motilulla]

Hello, here is sheet1 & and I want after the formulas fills, Results in sheet3 as shown below may this help

Data Sheet....

Book1
	E	F	G
1
2
3
4
5		Data
6		0 | 1 | 1
7		12 | 5 | 5
8		2 | 9 | 7
9		12 | 12 | 13
10		13 | 1 | 12
11		5 | 1 | 4
12		6 | 5 | 8
13		7 | 9 | 5
14		8 | 25 | 3
15		9 | 74 | 0
16		10 | 18 | 8
17		10 | 12 | 13
18		0 | 1 | 7
19		12 | 5 | 2
20		2 | 9 | 1
21		12 | 12 | 11
22		13 | 1 | 9
23		5 | 1 | 9
24		6 | 5 | 6
25		7 | 9 | 6
26		8 | 25 | 3
27		9 | 74 | 2
28		10 | 18 | 1
29		10 | 12 | 7
30
31
Sheet1

Result Expected

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3
4					Data C->	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
5			Data A	Data B
6			0	1		0	1	0	0	0	0	0	1	0	0	0	0	0	0	0
7
8			12	5		0	0	1	0	0	1	0	0	0	0	0	0	0	0	0
9
10			2	9		0	1	0	0	0	0	0	1	0	0	0	0	0	0	0
11
12			12	12		0	0	0	0	0	0	0	0	0	0	0	1	0	1	0
13
14			13	1		0	0	0	0	0	0	0	0	0	1	0	0	1	0	0
15
16			5	1		0	0	0	0	1	0	0	0	0	1	0	0	0	0	0
17
18			6	5		0	0	0	0	0	0	1	0	1	0	0	0	0	0	0
19
20			7	9		0	0	0	0	0	1	1	0	0	0	0	0	0	0	0
21
22			8	25		0	0	0	2	0	0	0	0	0	0	0	0	0	0	0
23
24			9	74		1	0	1	0	0	0	0	0	0	0	0	0	0	0	0
25
26			10	18		0	1	0	0	0	0	0	0	1	0	0	0	0	0	0
27
28			10	12		0	0	0	0	0	0	0	1	0	0	0	0	0	1	0
29
30
31
Sheet3

Regards,
Moti

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Sub[/COLOR] Fill_Formula()
    [COLOR=darkblue]Dim[/COLOR] rngArea As Range
    [COLOR=darkblue]With[/COLOR] Intersect(Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow, Columns("F:T"))
        .Formula = "=COUNTIF(Sheet1!$F$6:$F$63,$C6&"" | ""&$D6&"" | ""&F$4)"
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rngArea [COLOR=darkblue]In[/COLOR] .Areas
            rngArea.Value = rngArea.Value
        [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[motilulla]

Try this...

[COLOR=darkblue]Sub[/COLOR] Fill_Formula()
    [COLOR=darkblue]Dim[/COLOR] rngArea As Range
    [COLOR=darkblue]With[/COLOR] Intersect(Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow, Columns("F:T"))
        .Formula = "=COUNTIF(Sheet1!$F$6:$F$63,$C6&"" | ""&$D6&"" | ""&F$4)"
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rngArea [COLOR=darkblue]In[/COLOR] .Areas
            rngArea.Value = rngArea.Value
        [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

AlphaFrog, spot on it is resulting perfect!

Thank you so much for your kind help

Have a nice weekend

Regards,
Moti