Help with visual basic code for my data

[wesrockin]

VB help please....
in the follow in spreadsheet I would like to have an inection level expressed as a percent of trees which have a 2 in the bleed column. one of the tricky things is that some nodes have less that 4 trees. So I would need to divide by 2 or 3 in those cases instead of 4 . So the bleed column is eith 1, 2 or blank and there are always 4 rows for each node. I need one infection level to coorespond to the each numbered node in column B.
If possible I would like to write it in visual basic instead of a complicated if/then statement.
Thanks for any help you can provide.

fuel_worksheets_2_23_04_perm2.xls
	A	B	C	D	E	F	G	H	I	J
1	transect	node	id	tree	species	dist(m)	dbh1(in)	dbh2(in)	bleed	infection
2	1	1	1	1193	clo	18.9	9		1
3	1			1195		32.9	12.5		1
4	1			1198	bo	10.7	23.8		2
5	1			1200	bo	7	22.5		1
6	1	2	2	1191	bo	9.2	8		1
7	1			1190	clo	739	2.9
8	1			1189	clo	15.3	8.5
9	1			1192	bo	1.7	6.6		1
10	1	3	3	1188	bo	1.7	11.4		2
11	1			1187	bo	1.7	3.1		1
12	1			1186	clo	5.1	22		2
13	1			1185	bo	10.8	8.1		1
14	1	4	4	1181	clo	15.6	9.8		1
15	1			1182	clo	9.3	7.2		1
16	1			1183	bo	9.7	8.5		1
17	1			1184	clo	8.2	7.1		1
Sheet2

 

[Ahnold]

A pivot table will definitly give you the results you are looking for. Will that work for you?

 

[wesrockin]

I will have to learn how to use a pivot table ... thanks for the tip... I am also curious how you might write a macro for that.

Thanks!!!

 

[Ahnold]

I use this site to practise code writing. Let me know if this works for you and if not what modifications need to be made.

Sub Macro1()
    Dim myTable As PivotTable
    Dim myField As PivotField
    Dim myRange As Range
    On Error Resume Next
    With Range("A2", Range("A2").End(xlDown)).Offset(0, 1).Resize(, 2)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    Err.Clear
    Set myRange = Range("A2").CurrentRegion
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        myRange, TableDestination:=Range("L1"), TableName:= _
        "PivotTable1"
    Set myTable = ActiveSheet.PivotTables("PivotTable1")
    myTable.AddFields "node", "bleed", "transect"
    Set myField = myTable.PivotFields("bleed")
    myField.PivotItems("(blank)").Visible = False
    myField.Orientation = xlDataField
    myField.Calculation = xlPercentOfRow
End Sub