Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Using myval as counter twice HELP
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JBW JBW is offline
external usenet poster
  
Posts: 42
Default Using myval as counter twice HELP
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10,593
Default 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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add A Day Counter EisPanzer Excel Discussion (Misc queries) 3 May 9th 07 01:24 AM
how do I set up a counter? John T Excel Discussion (Misc queries) 3 October 20th 06 09:01 PM
counter Haza Excel Discussion (Misc queries) 2 January 20th 06 08:30 PM
Counter Sdbenn90 Excel Discussion (Misc queries) 4 January 9th 06 11:43 AM
Counter Polina Excel Discussion (Misc queries) 2 May 27th 05 09:52 PM


All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement