Using myval as counter twice HELP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 to shift to next row 2
Geometric progression in VBA 10
Modify range in VBA 9
SUMPRODUCT 3
Linking Cells Macro 3
WCG Stats Tuesday 03 October 2023 3

Back
Top