| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
There's got to be a better way
I'm trying to collect the contents from a range of cell on several worksheets
(e.g., goal1, goal2, goal3, etc) and place them on my sheet named Yearly%. While the code below works I was hoping there was a shorter or better way to write it. Perhaps an array of some sort. Dim August As Variant Dim September As Variant Dim October As Variant Sub CollectandEnterFirstQuarter() If Range("'Goal1'!E5") = "August" Then August = Range("'Goal1'!BC9:BC17") Range("'Yearly%'!B7:B15") = August August = Range("'Goal1'!BC27:BC35") Range("'Yearly%'!B16:B24") = August August = Range("'Goal2'!BC9:BC17") Range("'Yearly%'!P7:P15") = August August = Range("'Goal2'!BC27:BC35") Range("'Yearly%'!P16:P24") = August August = Range("'Goal3'!BC9:BC17") Range("'Yearly%'!AD7:AD15") = August August = Range("'Goal3'!BC27:BC35") Range("'Yearly%'!AD16:AD24") = August August = Range("'Goal4'!BC9:BC17") Range("'Yearly%'!AR30:AR38") = August August = Range("'Goal4'!BC27:BC35") Range("'Yearly%'!AR39:AR47") = August August = Range("'Goal5'!BC9:BC17") Range("'Yearly%'!B30:B38") = August August = Range("'Goal5'!BC27:BC35") Range("'Yearly%'!B39:B47") = August August = Range("'Goal6'!BC9:BC17") Range("'Yearly%'!P30:P38") = August August = Range("'Goal6'!BC27:BC35") Range("'Yearly%'!P39:P47") = August August = Range("'Goal7'!BC9:BC17") Range("'Yearly%'!AD30:AD38") = August August = Range("'Goal7'!BC27:BC35") Range("'Yearly%'!AD39:AD47") = August August = Range("'Goal8'!BC9:BC17") Range("'Yearly%'!AR30:AR38") = August August = Range("'Goal8'!BC27:BC35") Range("'Yearly%'!AR39:AR47") = August ElseIf Range("'Goal1'!E5") = "September" Then September = Range("'Goal1'!BC9:BC17") Range("'Yearly%'!C7:C15") = September September = Range("'Goal1'!BC27:BC35") Range("'Yearly%'!C16:C24") = September September = Range("'Goal2'!BC9:BC17") Range("'Yearly%'!Q7:Q15") = September September = Range("'Goal2'!BC27:BC35") Range("'Yearly%'!Q16:Q24") = September September = Range("'Goal3'!BC9:BC17") Range("'Yearly%'!AE7:AE15") = September September = Range("'Goal3'!BC27:BC35") Range("'Yearly%'!AE16:AE24") = September September = Range("'Goal4'!BC9:BC17") Range("'Yearly%'!AS70:AS15") = September September = Range("'Goal4'!BC27:BC35") Range("'Yearly%'!AS16:AS24") = September September = Range("'Goal5'!BC9:BC17") Range("'Yearly%'!C30:C38") = September September = Range("'Goal5'!BC27:BC35") Range("'Yearly%'!C39:C47") = September September = Range("'Goal6'!BC9:BC17") Range("'Yearly%'!Q30:Q38") = September September = Range("'Goal6'!BC27:BC35") Range("'Yearly%'!Q39:Q47") = September September = Range("'Goal7'!BC9:BC17") Range("'Yearly%'!AE30:AE38") = September September = Range("'Goal7'!BC27:BC35") Range("'Yearly%'!AE39:AE47") = September September = Range("'Goal8'!BC9:BC17") Range("'Yearly%'!AS30:AS38") = September September = Range("'Goal8'!BC27:BC35") Range("'Yearly%'!AS39:AS47") = September ElseIf Range("'Goal1'!E5") = "October" Then October = Range("'Goal1'!BC9:BC17") Range("'Yearly%'!D7:D15") = October October = Range("'Goal1'!BC27:BC35") Range("'Yearly%'!D16:D24") = October October = Range("'Goal2'!BC9:BC17") Range("'Yearly%'!R7:R15") = October October = Range("'Goal2'!BC27:BC35") Range("'Yearly%'!R16:R24") = October October = Range("'Goal3'!BC9:BC17") Range("'Yearly%'!AF7:AF15") = October October = Range("'Goal3'!BC27:BC35") Range("'Yearly%'!AF16:AF24") = October October = Range("'Goal4'!BC9:BC17") Range("'Yearly%'!AT30:AT38") = October October = Range("'Goal4'!BC27:BC35") Range("'Yearly%'!AT39:AT47") = October October = Range("'Goal5'!BC9:BC17") Range("'Yearly%'!D30:D38") = October October = Range("'Goal5'!BC27:BC35") Range("'Yearly%'!D39:D47") = October October = Range("'Goal6'!BC9:BC17") Range("'Yearly%'!R30:R38") = October October = Range("'Goal6'!BC27:BC35") Range("'Yearly%'!R39:R47") = October October = Range("'Goal7'!BC9:BC17") Range("'Yearly%'!AF30:AF38") = October October = Range("'Goal7'!BC27:BC35") Range("'Yearly%'!AF39:AF47") = October October = Range("'Goal8'!BC9:BC17") Range("'Yearly%'!AT30:AT38") = October October = Range("'Goal8'!BC27:BC35") Range("'Yearly%'!AT39:AT47") = October End If End Sub -- Mike Mast Special Education Preschool Teacher |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
There's got to be a better way
|
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
There's got to be a better way
|
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
There's got to be a better way
One replacement for a multiple If..ElseIf...ElseIf...Else...End If block is
Select Case ... End Select. It offers the advantage of being a little easier to read and is a bit more speedy in processing. Here's a dummy example: Select Case Range("'Goal1'!E5") Case Is = "August" '...code for what to for August Case Is = "September" '...code '...to '...deal with '...September Case Is = "November" '...more code to '...handle November Case Is = "October" '... you can have '... any number of '... Case Is = '... statements, plus '... the Case Else 'this is not required 'but can be used as 'an error handler or 'catch-all End Select "Preschool Mike" wrote: I'm trying to collect the contents from a range of cell on several worksheets (e.g., goal1, goal2, goal3, etc) and place them on my sheet named Yearly%. While the code below works I was hoping there was a shorter or better way to write it. Perhaps an array of some sort. Dim August As Variant Dim September As Variant Dim October As Variant Sub CollectandEnterFirstQuarter() If Range("'Goal1'!E5") = "August" Then August = Range("'Goal1'!BC9:BC17") Range("'Yearly%'!B7:B15") = August August = Range("'Goal1'!BC27:BC35") Range("'Yearly%'!B16:B24") = August August = Range("'Goal2'!BC9:BC17") Range("'Yearly%'!P7:P15") = August August = Range("'Goal2'!BC27:BC35") Range("'Yearly%'!P16:P24") = August August = Range("'Goal3'!BC9:BC17") Range("'Yearly%'!AD7:AD15") = August August = Range("'Goal3'!BC27:BC35") Range("'Yearly%'!AD16:AD24") = August August = Range("'Goal4'!BC9:BC17") Range("'Yearly%'!AR30:AR38") = August August = Range("'Goal4'!BC27:BC35") Range("'Yearly%'!AR39:AR47") = August August = Range("'Goal5'!BC9:BC17") Range("'Yearly%'!B30:B38") = August August = Range("'Goal5'!BC27:BC35") Range("'Yearly%'!B39:B47") = August August = Range("'Goal6'!BC9:BC17") Range("'Yearly%'!P30:P38") = August August = Range("'Goal6'!BC27:BC35") Range("'Yearly%'!P39:P47") = August August = Range("'Goal7'!BC9:BC17") Range("'Yearly%'!AD30:AD38") = August August = Range("'Goal7'!BC27:BC35") Range("'Yearly%'!AD39:AD47") = August August = Range("'Goal8'!BC9:BC17") Range("'Yearly%'!AR30:AR38") = August August = Range("'Goal8'!BC27:BC35") Range("'Yearly%'!AR39:AR47") = August ElseIf Range("'Goal1'!E5") = "September" Then September = Range("'Goal1'!BC9:BC17") Range("'Yearly%'!C7:C15") = September September = Range("'Goal1'!BC27:BC35") Range("'Yearly%'!C16:C24") = September September = Range("'Goal2'!BC9:BC17") Range("'Yearly%'!Q7:Q15") = September September = Range("'Goal2'!BC27:BC35") Range("'Yearly%'!Q16:Q24") = September September = Range("'Goal3'!BC9:BC17") Range("'Yearly%'!AE7:AE15") = September September = Range("'Goal3'!BC27:BC35") Range("'Yearly%'!AE16:AE24") = September September = Range("'Goal4'!BC9:BC17") Range("'Yearly%'!AS70:AS15") = September September = Range("'Goal4'!BC27:BC35") Range("'Yearly%'!AS16:AS24") = September September = Range("'Goal5'!BC9:BC17") Range("'Yearly%'!C30:C38") = September September = Range("'Goal5'!BC27:BC35") Range("'Yearly%'!C39:C47") = September September = Range("'Goal6'!BC9:BC17") Range("'Yearly%'!Q30:Q38") = September September = Range("'Goal6'!BC27:BC35") Range("'Yearly%'!Q39:Q47") = September September = Range("'Goal7'!BC9:BC17") Range("'Yearly%'!AE30:AE38") = September September = Range("'Goal7'!BC27:BC35") Range("'Yearly%'!AE39:AE47") = September September = Range("'Goal8'!BC9:BC17") Range("'Yearly%'!AS30:AS38") = September September = Range("'Goal8'!BC27:BC35") Range("'Yearly%'!AS39:AS47") = September ElseIf Range("'Goal1'!E5") = "October" Then October = Range("'Goal1'!BC9:BC17") Range("'Yearly%'!D7:D15") = October October = Range("'Goal1'!BC27:BC35") Range("'Yearly%'!D16:D24") = October October = Range("'Goal2'!BC9:BC17") Range("'Yearly%'!R7:R15") = October October = Range("'Goal2'!BC27:BC35") Range("'Yearly%'!R16:R24") = October October = Range("'Goal3'!BC9:BC17") Range("'Yearly%'!AF7:AF15") = October October = Range("'Goal3'!BC27:BC35") Range("'Yearly%'!AF16:AF24") = October October = Range("'Goal4'!BC9:BC17") Range("'Yearly%'!AT30:AT38") = October October = Range("'Goal4'!BC27:BC35") Range("'Yearly%'!AT39:AT47") = October October = Range("'Goal5'!BC9:BC17") Range("'Yearly%'!D30:D38") = October October = Range("'Goal5'!BC27:BC35") Range("'Yearly%'!D39:D47") = October October = Range("'Goal6'!BC9:BC17") Range("'Yearly%'!R30:R38") = October October = Range("'Goal6'!BC27:BC35") Range("'Yearly%'!R39:R47") = October October = Range("'Goal7'!BC9:BC17") Range("'Yearly%'!AF30:AF38") = October October = Range("'Goal7'!BC27:BC35") Range("'Yearly%'!AF39:AF47") = October October = Range("'Goal8'!BC9:BC17") Range("'Yearly%'!AT30:AT38") = October October = Range("'Goal8'!BC27:BC35") Range("'Yearly%'!AT39:AT47") = October End If End Sub -- Mike Mast Special Education Preschool Teacher |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
