Using myval as counter twice HELP

J

JBW

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to index every line 1
conditonal formatting in VB 2
Macro 3
Modify range in VBA 9
Creating Pivot Table with Macro using Multiple Sheets 0
Geometric progression in VBA 10
multiple match 4
Macro to shift to next row 2

Top