ExcelBanter - Using myval as counter twice HELP

How about this

Sub NMB()
Dim counter As Integer

With Sheet1
.Range("Q6").FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
.Range("Q6").FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
End With

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

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

For counter = 1 To myval
With Sheet1
.Cells(6 + (counter * 8), 17).FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
.Cells(6 + (counter * 8), 17).FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"

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

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

Application.CutCopyMode = False
.Range("Q1").FormulaR1C1 = "Fc ave"
End With

With Sheet8
.Cells(1 + (counter * 1), 1).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)"
.Cells(1 + (counter * 1), 2).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c2, (ROW()-1)*8+6)"
.Cells(1 + (counter * 1), 3).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+3)"
.Cells(1 + (counter * 1), 4).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+4)"
.Cells(1 + (counter * 1), 5).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+5)"
.Cells(1 + (counter * 1), 6).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c4, (ROW()-1)*8+7)"
.Cells(1 + (counter * 1), 7).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c5, (ROW()-1)*8+7)"
.Cells(1 + (counter * 1), 8).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c6, (ROW()-1)*8+7)"
.Cells(1 + (counter * 1), 9).FormulaR1C1 = _
"=INDEX('bB SCM 22 OCTOBER 2007'!c6, (ROW()-1)*8+3)"
End With
Next counter
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JBW" wrote in message
...
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