Ok two pieces of code work fine independently but together NOT.

I guess its because they are vboth referencing the same myval value and
counter, but how can I get them both to use the same value.

code 1

myval = Application.CountIf(ActiveSheet.Cells, "NPQ") - 1


Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Range("Q6").Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"


Dim counter As Integer

For counter = 1 To myval


Cells(6 + (counter * 8), 17).Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Cells(6 + (counter * 8), 17).Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"



Range("A6").Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Range("B6").Select
Selection.NumberFormat = "0.0"


Cells(6 + (counter * 8), 1).Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Cells(6 + (counter * 8), 2).Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Cells(6 + (counter * 8), 2).Select
Selection.NumberFormat = "0.0"

Range("Q1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Fc ave"



Next counter

code 2

Sheet1.Select

myval = Application.CountIf(ActiveSheet.Cells, "NPQ") - 1

Sheet8.Select

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)"
Range("b1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c2, (ROW()-1)*8+6)"
Range("c1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+3)"
Range("d1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+4)"
Range("e1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+5)"
Range("f1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+7)"
Range("g1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c5, (ROW()-1)*8+7)"
Range("h1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c6, (ROW()-1)*8+7)"
Range("i1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c6, (ROW()-1)*8+3)"



Dim counter As Integer

For counter = 1 To myval


Cells(1 + (counter * 1), 1).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)"

Cells(1 + (counter * 1), 2).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c2, (ROW()-1)*8+6)"
Cells(1 + (counter * 1), 3).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+3)"
Cells(1 + (counter * 1), 4).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+4)"
Cells(1 + (counter * 1), 5).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+5)"
Cells(1 + (counter * 1), 6).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+7)"
Cells(1 + (counter * 1), 7).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c5, (ROW()-1)*8+7)"
Cells(1 + (counter * 1), 8).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c6, (ROW()-1)*8+7)"
Cells(1 + (counter * 1), 9).Select

ActiveCell.FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c6, (ROW()-1)*8+3)"





Next counter

I joined the two in the vain hope they would work but the second counter
function seems to be messing it up