Count 1 with VBA

[Kishan]

Using Excel 2000

Hi,

I am looking VBA that can count 1 from the specific point, the idea is simple add +1 if find next character 1 is followed by 1 subtract if next cheater is followed by either X or 2

Example sheet1 there are my data in column C,
Step-1 check C6 if it is not 1 result in E6= -1, check C7 if it is not 1 result in E7= -2, check C8 if it is not 1 result in E8= -3, check C9 if it is not 1 result in E9= -4, check C10 if it is find 1 result in E11= -3, for this I am using formulas. E6=IF(C6=1,1,-1) , E7=IF(C7=1,E6+1,IF(C7="X",E6-1,IF(C7=2,E6-1))) Copied to down.

Book1
	A	B	C	D	E	F
1
2
3
4
5	Jor	Temp	P1		P1
6			X		-1
7			X		-2
8			X		-3
9			2		-4
10			1		-3
11			X		-4
12			X		-5
13			1		-4
14			1		-3
15			1		-2
16			X		-3
17			1		-2
18			1		-1
19			X		-2
20			1		-1
21			1		0
22			2		-1
23			1		0
24			X		-1
25			1		0
26			X		-1
27			1		0
28			1		1
29			1		2
30			1		3
31			1		4
32			1		5
33			2		4
34			1		5
35			2		4
36			1		5
37			X		4
38			X		3
39			X		2
40			1		3
41			X		2
42			2		1
43			X		0
44			X		-1
45
Sheet1

This is what I want as shown in the below sheet2 VBA solution for example in the column D where ever I mark 1 counting start from that row to down as shown below

Book1
	A	B	C	D	E	F
1
2
3
4
5	Jor	Temp	P1		P1
6			X
7			X
8			X
9			2
10			1
11			X
12			X
13			1
14			1
15			1
16			X
17			1
18			1
19			X
20			1
21			1
22			2
23			1
24			X
25			1	1	1
26			X		0
27			1		1
28			1		2
29			1		3
30			1		4
31			1		5
32			1		6
33			2		5
34			1		6
35			2		5
36			1		6
37			X		5
38			X		4
39			X		3
40			2		2
41			X		1
42			2		0
43			X		-1
44			X		-2
45
Sheet2

Thank you in advance

Regards,
Kishan

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Nov09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Rw = Range("D:D").Find(1, LookIn:=xlValues).Row
[COLOR="Navy"]Set[/COLOR] Rng = Range("C" & Rw, Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Address = Rng(1).Address [COLOR="Navy"]Then[/COLOR]
    Dn.Offset(, 2).Value = IIf(Dn.Value = 1, 1, -1)
[COLOR="Navy"]Else[/COLOR]
    Dn.Offset(, 2).Value = IIf(Dn.Value = 1, Dn.Offset(-1, 2).Value + 1, Dn.Offset(-1, 2).Value - 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG12Nov09
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Rw = Range("D:D").Find(1, LookIn:=xlValues).Row
[COLOR=navy]Set[/COLOR] Rng = Range("C" & Rw, Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Dn.Address = Rng(1).Address [COLOR=navy]Then[/COLOR]
    Dn.Offset(, 2).Value = IIf(Dn.Value = 1, 1, -1)
[COLOR=navy]Else[/COLOR]
    Dn.Offset(, 2).Value = IIf(Dn.Value = 1, Dn.Offset(-1, 2).Value + 1, Dn.Offset(-1, 2).Value - 1)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thanks MickG,

This is doing exactly what I require. I checked it and it works as demand.

Regards,
Kishan

 

[MickG]

You're welcome