VBA: check if string in cell has a value. If yes, add 1 to a specific cell, if not, leave it.

[TerenceTitus]

Hi,

sorry I am stuck, your help is very much appreciated. I am struggling to think of how I can use VBA to check for a number in a cell (string - e.g. 8, 11, 18). Then I would like to tabulate the total numbers into a table of how many cells contained that number.

Here is how the data looks like. Act is for the type of activity. The numbers in the data represent the type of material used.
E.g. for person 1 (row 1), for Act 2 he used let's say (number 3) sand. Person 4 (row 4) also used (number 3). It should add up the total number of times each material is used for each activity.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Act1
[/TD]
[TD="align: center"]Act2
[/TD]
[TD="align: center"]Act3
[/TD]
[TD="align: center"]Act4
[/TD]
[TD="align: center"]Act5
[/TD]
[TD="align: center"]Act6
[/TD]
[TD="align: center"]Act7[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6,11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3,5[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4,8[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5,11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]3,10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]11[/TD]
[/TR]
</tbody>[/TABLE]

Here is an example of how the tabulated table would look like based on the data above. Mat is for materials.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mat1
[/TD]
[TD]Mat<strike></strike>2
[/TD]
[TD]Mat<strike></strike>3
[/TD]
[TD]Mat<strike></strike>4
[/TD]
[TD]Mat<strike></strike>5
[/TD]
[TD]Mat6<strike></strike>
[/TD]
[TD]Mat7<strike></strike>
[/TD]
[TD]Mat<strike></strike>8
[/TD]
[TD]Mat<strike></strike>9
[/TD]
[TD]Mat10
[/TD]
[TD]Mat<strike></strike>11[/TD]
[/TR]
[TR]
[TD]Act1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Act<strike></strike>2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Act<strike></strike>3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Act<strike></strike>4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1<strike></strike>[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]Act<strike></strike>5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Act<strike></strike>6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Act<strike></strike>7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

The VBA does not need to generate a pivot table, just create this table in a different sheet is good enough.

 

[Peter_SSs]

Does it really need to be vba?
I think this formula copied across and down does what you want.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Act1	Act2	Act3	Act4	Act5	Act6	Act7
2		3	9		6,11		11
3	3		3,5	11		4,8	3
4		5,11		8	5	11	6
5	9	3,10			9	8	11
6
7
8		Mat1	Mat2	Mat3	Mat4	Mat5	Mat6	Mat7	Mat8	Mat9	Mat10	Mat11
9	Act1	0	0	1	0	0	0	0	0	1	0	0
10	Act2	0	0	2	0	1	0	0	0	0	1	1
11	Act3	0	0	1	0	1	0	0	0	1	0	0
12	Act4	0	0	0	0	0	0	0	1	0	0	1
13	Act5	0	0	0	0	1	1	0	0	1	0	1
14	Act6	0	0	0	1	0	0	0	2	0	0	1
15	Act7	0	0	1	0	0	1	0	0	0	0	2
Count Materials (2)