Help with visual basic code for my data

wesrockin · Mar 5, 2004

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

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

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

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

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 · Mar 5, 2004

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

wesrockin · Mar 5, 2004

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 · Mar 6, 2004

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.

Code:

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

Help with visual basic code for my data

wesrockin

New Member

Excel Facts

Ahnold

Well-known Member

wesrockin

New Member

Ahnold

Well-known Member

Similar threads

Forum statistics

Share this page

Help with visual basic code for my data

wesrockin

New Member

Excel Facts

Ahnold

Well-known Member

wesrockin

New Member

Ahnold

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock