| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster way
Hi there,
I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Post your code...
-- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Here is the code i am using
Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Range("AI7:AI42").Value = Worksheets("Year at a Glance").Range("C5:C39").Value -- Jim "Carlee" wrote in message ... | Here is the code i am using | | Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) | Case Is = "JANUARY" | Range("AI7") = Worksheets("Year at a Glance").Range("C5") | Range("AI8") = Worksheets("Year at a Glance").Range("C6") | Range("AI9") = Worksheets("Year at a Glance").Range("C7") | Range("AI10") = Worksheets("Year at a Glance").Range("C8") | Range("AI11") = Worksheets("Year at a Glance").Range("C9") | Range("AI12") = Worksheets("Year at a Glance").Range("C10") | Range("AI13") = Worksheets("Year at a Glance").Range("C11") | Range("AI14") = Worksheets("Year at a Glance").Range("C12") | Range("AI16") = Worksheets("Year at a Glance").Range("C14") | Range("AI17") = Worksheets("Year at a Glance").Range("C15") | Range("AI18") = Worksheets("Year at a Glance").Range("C16") | Range("AI19") = Worksheets("Year at a Glance").Range("C17") | Range("AI21") = Worksheets("Year at a Glance").Range("C19") | Range("AI22") = Worksheets("Year at a Glance").Range("C20") | Range("AI23") = Worksheets("Year at a Glance").Range("C21") | Range("AI24") = Worksheets("Year at a Glance").Range("C22") | Range("AI25") = Worksheets("Year at a Glance").Range("C23") | Range("AI26") = Worksheets("Year at a Glance").Range("C24") | Range("AI28") = Worksheets("Year at a Glance").Range("C26") | Range("AI29") = Worksheets("Year at a Glance").Range("C27") | Range("AI30") = Worksheets("Year at a Glance").Range("C28") | Range("AI31") = Worksheets("Year at a Glance").Range("C29") | Range("AI33") = Worksheets("Year at a Glance").Range("C31") | Range("AI34") = Worksheets("Year at a Glance").Range("C32") | Range("AI35") = Worksheets("Year at a Glance").Range("C33") | Range("AI38") = Worksheets("Year at a Glance").Range("C35") | Range("AI39") = Worksheets("Year at a Glance").Range("C36") | Range("AI40") = Worksheets("Year at a Glance").Range("C37") | Range("AI41") = Worksheets("Year at a Glance").Range("C38") | Range("AI42") = Worksheets("Year at a Glance").Range("C39") | Case Is = "FEBRUARY" | Range("AI7") = Worksheets("Year at a Glance").Range("D5") | Range("AI8") = Worksheets("Year at a Glance").Range("D6") | Range("AI9") = Worksheets("Year at a Glance").Range("D7") | Range("AI10") = Worksheets("Year at a Glance").Range("D8") | Range("AI11") = Worksheets("Year at a Glance").Range("D9") | Range("AI12") = Worksheets("Year at a Glance").Range("D10") | Range("AI13") = Worksheets("Year at a Glance").Range("D11") | Range("AI14") = Worksheets("Year at a Glance").Range("D12") | Range("AI16") = Worksheets("Year at a Glance").Range("D14") | Range("AI17") = Worksheets("Year at a Glance").Range("D15") | Range("AI18") = Worksheets("Year at a Glance").Range("D16") | Range("AI19") = Worksheets("Year at a Glance").Range("D17") | Range("AI21") = Worksheets("Year at a Glance").Range("D19") | Range("AI22") = Worksheets("Year at a Glance").Range("D20") | Range("AI23") = Worksheets("Year at a Glance").Range("D21") | Range("AI24") = Worksheets("Year at a Glance").Range("D22") | Range("AI25") = Worksheets("Year at a Glance").Range("D23") | Range("AI26") = Worksheets("Year at a Glance").Range("D24") | Range("AI28") = Worksheets("Year at a Glance").Range("D26") | Range("AI29") = Worksheets("Year at a Glance").Range("D27") | Range("AI30") = Worksheets("Year at a Glance").Range("D28") | Range("AI31") = Worksheets("Year at a Glance").Range("D29") | Range("AI33") = Worksheets("Year at a Glance").Range("D31") | Range("AI34") = Worksheets("Year at a Glance").Range("D32") | Range("AI35") = Worksheets("Year at a Glance").Range("D33") | Range("AI38") = Worksheets("Year at a Glance").Range("D35") | Range("AI39") = Worksheets("Year at a Glance").Range("D36") | Range("AI40") = Worksheets("Year at a Glance").Range("D37") | Range("AI41") = Worksheets("Year at a Glance").Range("D38") | Range("AI42") = Worksheets("Year at a Glance").Range("D39") | Case Is = "MARCH" | Range("AI7") = Worksheets("Year at a Glance").Range("E5") | Range("AI8") = Worksheets("Year at a Glance").Range("E6") | Range("AI9") = Worksheets("Year at a Glance").Range("E7") | Range("AI10") = Worksheets("Year at a Glance").Range("E8") | Range("AI11") = Worksheets("Year at a Glance").Range("E9") | Range("AI12") = Worksheets("Year at a Glance").Range("E10") | Range("AI13") = Worksheets("Year at a Glance").Range("E11") | Range("AI14") = Worksheets("Year at a Glance").Range("E12") | Range("AI16") = Worksheets("Year at a Glance").Range("E14") | Range("AI17") = Worksheets("Year at a Glance").Range("E15") | Range("AI18") = Worksheets("Year at a Glance").Range("E16") | Range("AI19") = Worksheets("Year at a Glance").Range("E17") | Range("AI21") = Worksheets("Year at a Glance").Range("E19") | Range("AI22") = Worksheets("Year at a Glance").Range("E20") | Range("AI23") = Worksheets("Year at a Glance").Range("E21") | Range("AI24") = Worksheets("Year at a Glance").Range("E22") | Range("AI25") = Worksheets("Year at a Glance").Range("E23") | Range("AI26") = Worksheets("Year at a Glance").Range("E24") | Range("AI28") = Worksheets("Year at a Glance").Range("E26") | Range("AI29") = Worksheets("Year at a Glance").Range("E27") | Range("AI30") = Worksheets("Year at a Glance").Range("E28") | Range("AI31") = Worksheets("Year at a Glance").Range("E29") | Range("AI33") = Worksheets("Year at a Glance").Range("E31") | Range("AI34") = Worksheets("Year at a Glance").Range("E32") | Range("AI35") = Worksheets("Year at a Glance").Range("E33") | Range("AI38") = Worksheets("Year at a Glance").Range("E35") | Range("AI39") = Worksheets("Year at a Glance").Range("E36") | Range("AI40") = Worksheets("Year at a Glance").Range("E37") | Range("AI41") = Worksheets("Year at a Glance").Range("E38") | Range("AI42") = Worksheets("Year at a Glance").Range("E39") | Case Is = "APRIL" | Range("AI7") = Worksheets("Year at a Glance").Range("F5") | Range("AI8") = Worksheets("Year at a Glance").Range("F6") | Range("AI9") = Worksheets("Year at a Glance").Range("F7") | Range("AI10") = Worksheets("Year at a Glance").Range("F8") | Range("AI11") = Worksheets("Year at a Glance").Range("F9") | Range("AI12") = Worksheets("Year at a Glance").Range("F10") | Range("AI13") = Worksheets("Year at a Glance").Range("F11") | Range("AI14") = Worksheets("Year at a Glance").Range("F12") | Range("AI16") = Worksheets("Year at a Glance").Range("F14") | Range("AI17") = Worksheets("Year at a Glance").Range("F15") | Range("AI18") = Worksheets("Year at a Glance").Range("F16") | Range("AI19") = Worksheets("Year at a Glance").Range("F17") | Range("AI21") = Worksheets("Year at a Glance").Range("F19") | Range("AI22") = Worksheets("Year at a Glance").Range("F20") | Range("AI23") = Worksheets("Year at a Glance").Range("F21") | Range("AI24") = Worksheets("Year at a Glance").Range("F22") | Range("AI25") = Worksheets("Year at a Glance").Range("F23") | Range("AI26") = Worksheets("Year at a Glance").Range("F24") | Range("AI28") = Worksheets("Year at a Glance").Range("F26") | Range("AI29") = Worksheets("Year at a Glance").Range("F27") | Range("AI30") = Worksheets("Year at a Glance").Range("F28") | Range("AI31") = Worksheets("Year at a Glance").Range("F29") | Range("AI33") = Worksheets("Year at a Glance").Range("F31") | Range("AI34") = Worksheets("Year at a Glance").Range("F32") | Range("AI35") = Worksheets("Year at a Glance").Range("F33") | Range("AI38") = Worksheets("Year at a Glance").Range("F35") | Range("AI39") = Worksheets("Year at a Glance").Range("F36") | Range("AI40") = Worksheets("Year at a Glance").Range("F37") | Range("AI41") = Worksheets("Year at a Glance").Range("F38") | Range("AI42") = Worksheets("Year at a Glance").Range("F39") | | Case Is = "MAY" | Range("AI7") = Worksheets("Year at a Glance").Range("G5") | Range("AI8") = Worksheets("Year at a Glance").Range("G6") | Range("AI9") = Worksheets("Year at a Glance").Range("G7") | Range("AI10") = Worksheets("Year at a Glance").Range("G8") | Range("AI11") = Worksheets("Year at a Glance").Range("G9") | Range("AI12") = Worksheets("Year at a Glance").Range("G10") | Range("AI13") = Worksheets("Year at a Glance").Range("G11") | Range("AI14") = Worksheets("Year at a Glance").Range("G12") | Range("AI16") = Worksheets("Year at a Glance").Range("G14") | Range("AI17") = Worksheets("Year at a Glance").Range("G15") | Range("AI18") = Worksheets("Year at a Glance").Range("G16") | Range("AI19") = Worksheets("Year at a Glance").Range("G17") | Range("AI21") = Worksheets("Year at a Glance").Range("G19") | Range("AI22") = Worksheets("Year at a Glance").Range("G20") | Range("AI23") = Worksheets("Year at a Glance").Range("G21") | Range("AI24") = Worksheets("Year at a Glance").Range("G22") | Range("AI25") = Worksheets("Year at a Glance").Range("G23") | Range("AI26") = Worksheets("Year at a Glance").Range("G24") | Range("AI28") = Worksheets("Year at a Glance").Range("G26") | Range("AI29") = Worksheets("Year at a Glance").Range("G27") | Range("AI30") = Worksheets("Year at a Glance").Range("G28") | Range("AI31") = Worksheets("Year at a Glance").Range("G29") | Range("AI33") = Worksheets("Year at a Glance").Range("G31") | Range("AI34") = Worksheets("Year at a Glance").Range("G32") | Range("AI35") = Worksheets("Year at a Glance").Range("G33") | Range("AI38") = Worksheets("Year at a Glance").Range("G35") | Range("AI39") = Worksheets("Year at a Glance").Range("G36") | Range("AI40") = Worksheets("Year at a Glance").Range("G37") | Range("AI41") = Worksheets("Year at a Glance").Range("G38") | Range("AI42") = Worksheets("Year at a Glance").Range("G39") | | Case Is = "JUNE" | Range("AI7") = Worksheets("Year at a Glance").Range("H5") | Range("AI8") = Worksheets("Year at a Glance").Range("H6") | Range("AI9") = Worksheets("Year at a Glance").Range("H7") | Range("AI10") = Worksheets("Year at a Glance").Range("H8") | Range("AI11") = Worksheets("Year at a Glance").Range("H9") | Range("AI12") = Worksheets("Year at a Glance").Range("H10") | Range("AI13") = Worksheets("Year at a Glance").Range("H11") | Range("AI14") = Worksheets("Year at a Glance").Range("H12") | Range("AI16") = Worksheets("Year at a Glance").Range("H14") | Range("AI17") = Worksheets("Year at a Glance").Range("H15") | Range("AI18") = Worksheets("Year at a Glance").Range("H16") | Range("AI19") = Worksheets("Year at a Glance").Range("H17") | Range("AI21") = Worksheets("Year at a Glance").Range("H19") | Range("AI22") = Worksheets("Year at a Glance").Range("H20") | Range("AI23") = Worksheets("Year at a Glance").Range("H21") | Range("AI24") = Worksheets("Year at a Glance").Range("H22") | Range("AI25") = Worksheets("Year at a Glance").Range("H23") | Range("AI26") = Worksheets("Year at a Glance").Range("H24") | Range("AI28") = Worksheets("Year at a Glance").Range("H26") | Range("AI29") = Worksheets("Year at a Glance").Range("H27") | Range("AI30") = Worksheets("Year at a Glance").Range("H28") | Range("AI31") = Worksheets("Year at a Glance").Range("H29") | Range("AI33") = Worksheets("Year at a Glance").Range("H31") | Range("AI34") = Worksheets("Year at a Glance").Range("H32") | Range("AI35") = Worksheets("Year at a Glance").Range("H33") | Range("AI38") = Worksheets("Year at a Glance").Range("H35") | Range("AI39") = Worksheets("Year at a Glance").Range("H36") | Range("AI40") = Worksheets("Year at a Glance").Range("H37") | Range("AI41") = Worksheets("Year at a Glance").Range("H38") | Range("AI42") = Worksheets("Year at a Glance").Range("H39") | | Case Is = "JULY" | Range("AI7") = Worksheets("Year at a Glance").Range("I5") | Range("AI8") = Worksheets("Year at a Glance").Range("I6") | Range("AI9") = Worksheets("Year at a Glance").Range("I7") | Range("AI10") = Worksheets("Year at a Glance").Range("I8") | Range("AI11") = Worksheets("Year at a Glance").Range("I9") | Range("AI12") = Worksheets("Year at a Glance").Range("I10") | Range("AI13") = Worksheets("Year at a Glance").Range("I11") | Range("AI14") = Worksheets("Year at a Glance").Range("I12") | Range("AI16") = Worksheets("Year at a Glance").Range("I14") | Range("AI17") = Worksheets("Year at a Glance").Range("I15") | Range("AI18") = Worksheets("Year at a Glance").Range("I16") | Range("AI19") = Worksheets("Year at a Glance").Range("I17") | Range("AI21") = Worksheets("Year at a Glance").Range("I19") | Range("AI22") = Worksheets("Year at a Glance").Range("I20") | Range("AI23") = Worksheets("Year at a Glance").Range("I21") | Range("AI24") = Worksheets("Year at a Glance").Range("I22") | Range("AI25") = Worksheets("Year at a Glance").Range("I23") | Range("AI26") = Worksheets("Year at a Glance").Range("I24") | Range("AI28") = Worksheets("Year at a Glance").Range("I26") | Range("AI29") = Worksheets("Year at a Glance").Range("I27") | Range("AI30") = Worksheets("Year at a Glance").Range("I28") | Range("AI31") = Worksheets("Year at a Glance").Range("I29") | Range("AI33") = Worksheets("Year at a Glance").Range("I31") | Range("AI34") = Worksheets("Year at a Glance").Range("I32") | Range("AI35") = Worksheets("Year at a Glance").Range("I33") | Range("AI38") = Worksheets("Year at a Glance").Range("I35") | Range("AI39") = Worksheets("Year at a Glance").Range("I36") | Range("AI40") = Worksheets("Year at a Glance").Range("I37") | Range("AI41") = Worksheets("Year at a Glance").Range("I38") | Range("AI42") = Worksheets("Year at a Glance").Range("I39") | | Case Is = "AUGUST" | Range("AI7") = Worksheets("Year at a Glance").Range("J5") | Range("AI8") = Worksheets("Year at a Glance").Range("J6") | Range("AI9") = Worksheets("Year at a Glance").Range("J7") | Range("AI10") = Worksheets("Year at a Glance").Range("J8") | Range("AI11") = Worksheets("Year at a Glance").Range("J9") | Range("AI12") = Worksheets("Year at a Glance").Range("J10") | Range("AI13") = Worksheets("Year at a Glance").Range("J11") | Range("AI14") = Worksheets("Year at a Glance").Range("J12") | Range("AI16") = Worksheets("Year at a Glance").Range("J14") | Range("AI17") = Worksheets("Year at a Glance").Range("J15") | Range("AI18") = Worksheets("Year at a Glance").Range("J16") | Range("AI19") = Worksheets("Year at a Glance").Range("J17") | Range("AI21") = Worksheets("Year at a Glance").Range("J19") | Range("AI22") = Worksheets("Year at a Glance").Range("J20") | Range("AI23") = Worksheets("Year at a Glance").Range("J21") | Range("AI24") = Worksheets("Year at a Glance").Range("J22") | Range("AI25") = Worksheets("Year at a Glance").Range("J23") | Range("AI26") = Worksheets("Year at a Glance").Range("J24") | Range("AI28") = Worksheets("Year at a Glance").Range("J26") | Range("AI29") = Worksheets("Year at a Glance").Range("J27") | Range("AI30") = Worksheets("Year at a Glance").Range("J28") | Range("AI31") = Worksheets("Year at a Glance").Range("J29") | Range("AI33") = Worksheets("Year at a Glance").Range("J31") | Range("AI34") = Worksheets("Year at a Glance").Range("J32") | Range("AI35") = Worksheets("Year at a Glance").Range("J33") | Range("AI38") = Worksheets("Year at a Glance").Range("J35") | Range("AI39") = Worksheets("Year at a Glance").Range("J36") | Range("AI40") = Worksheets("Year at a Glance").Range("J37") | Range("AI41") = Worksheets("Year at a Glance").Range("J38") | Range("AI42") = Worksheets("Year at a Glance").Range("J39") | | Case Is = "SEPTEMBER" | Range("AI7") = Worksheets("Year at a Glance").Range("K5") | Range("AI8") = Worksheets("Year at a Glance").Range("K6") | Range("AI9") = Worksheets("Year at a Glance").Range("K7") | Range("AI10") = Worksheets("Year at a Glance").Range("K8") | Range("AI11") = Worksheets("Year at a Glance").Range("K9") | Range("AI12") = Worksheets("Year at a Glance").Range("K10") | Range("AI13") = Worksheets("Year at a Glance").Range("K11") | Range("AI14") = Worksheets("Year at a Glance").Range("K12") | Range("AI16") = Worksheets("Year at a Glance").Range("K14") | Range("AI17") = Worksheets("Year at a Glance").Range("K15") | Range("AI18") = Worksheets("Year at a Glance").Range("K16") | Range("AI19") = Worksheets("Year at a Glance").Range("K17") | Range("AI21") = Worksheets("Year at a Glance").Range("K19") | Range("AI22") = Worksheets("Year at a Glance").Range("K20") | Range("AI23") = Worksheets("Year at a Glance").Range("K21") | Range("AI24") = Worksheets("Year at a Glance").Range("K22") | Range("AI25") = Worksheets("Year at a Glance").Range("K23") | Range("AI26") = Worksheets("Year at a Glance").Range("K24") | Range("AI28") = Worksheets("Year at a Glance").Range("K26") | Range("AI29") = Worksheets("Year at a Glance").Range("K27") | Range("AI30") = Worksheets("Year at a Glance").Range("K28") | Range("AI31") = Worksheets("Year at a Glance").Range("K29") | Range("AI33") = Worksheets("Year at a Glance").Range("K31") | Range("AI34") = Worksheets("Year at a Glance").Range("K32") | Range("AI35") = Worksheets("Year at a Glance").Range("K33") | Range("AI38") = Worksheets("Year at a Glance").Range("K35") | Range("AI39") = Worksheets("Year at a Glance").Range("K36") | Range("AI40") = Worksheets("Year at a Glance").Range("K37") | Range("AI41") = Worksheets("Year at a Glance").Range("K38") | Range("AI42") = Worksheets("Year at a Glance").Range("K39") | | Case Is = "OCTOBER" | Range("AI7") = Worksheets("Year at a Glance").Range("L5") | Range("AI8") = Worksheets("Year at a Glance").Range("L6") | Range("AI9") = Worksheets("Year at a Glance").Range("L7") | Range("AI10") = Worksheets("Year at a Glance").Range("L8") | Range("AI11") = Worksheets("Year at a Glance").Range("L9") | Range("AI12") = Worksheets("Year at a Glance").Range("L10") | Range("AI13") = Worksheets("Year at a Glance").Range("L11") | Range("AI14") = Worksheets("Year at a Glance").Range("L12") | Range("AI16") = Worksheets("Year at a Glance").Range("L14") | Range("AI17") = Worksheets("Year at a Glance").Range("L15") | Range("AI18") = Worksheets("Year at a Glance").Range("L16") | Range("AI19") = Worksheets("Year at a Glance").Range("L17") | Range("AI21") = Worksheets("Year at a Glance").Range("L19") | Range("AI22") = Worksheets("Year at a Glance").Range("L20") | Range("AI23") = Worksheets("Year at a Glance").Range("L21") | Range("AI24") = Worksheets("Year at a Glance").Range("L22") | Range("AI25") = Worksheets("Year at a Glance").Range("L23") | Range("AI26") = Worksheets("Year at a Glance").Range("L24") | Range("AI28") = Worksheets("Year at a Glance").Range("L26") | Range("AI29") = Worksheets("Year at a Glance").Range("L27") | Range("AI30") = Worksheets("Year at a Glance").Range("L28") | Range("AI31") = Worksheets("Year at a Glance").Range("L29") | Range("AI33") = Worksheets("Year at a Glance").Range("L31") | Range("AI34") = Worksheets("Year at a Glance").Range("L32") | Range("AI35") = Worksheets("Year at a Glance").Range("L33") | Range("AI38") = Worksheets("Year at a Glance").Range("L35") | Range("AI39") = Worksheets("Year at a Glance").Range("L36") | Range("AI40") = Worksheets("Year at a Glance").Range("L37") | Range("AI41") = Worksheets("Year at a Glance").Range("L38") | Range("AI42") = Worksheets("Year at a Glance").Range("L39") | | | Case Is = "NOVEMBER" | Range("AI7") = Worksheets("Year at a Glance").Range("M5") | Range("AI8") = Worksheets("Year at a Glance").Range("M6") | Range("AI9") = Worksheets("Year at a Glance").Range("M7") | Range("AI10") = Worksheets("Year at a Glance").Range("M8") | Range("AI11") = Worksheets("Year at a Glance").Range("M9") | Range("AI12") = Worksheets("Year at a Glance").Range("M10") | Range("AI13") = Worksheets("Year at a Glance").Range("M11") | Range("AI14") = Worksheets("Year at a Glance").Range("M12") | Range("AI16") = Worksheets("Year at a Glance").Range("M14") | Range("AI17") = Worksheets("Year at a Glance").Range("M15") | Range("AI18") = Worksheets("Year at a Glance").Range("M16") | Range("AI19") = Worksheets("Year at a Glance").Range("M17") | Range("AI21") = Worksheets("Year at a Glance").Range("M19") | Range("AI22") = Worksheets("Year at a Glance").Range("M20") | Range("AI23") = Worksheets("Year at a Glance").Range("M21") | Range("AI24") = Worksheets("Year at a Glance").Range("M22") | Range("AI25") = Worksheets("Year at a Glance").Range("M23") | Range("AI26") = Worksheets("Year at a Glance").Range("M24") | Range("AI28") = Worksheets("Year at a Glance").Range("M26") | Range("AI29") = Worksheets("Year at a Glance").Range("M27") | Range("AI30") = Worksheets("Year at a Glance").Range("M28") | Range("AI31") = Worksheets("Year at a Glance").Range("M29") | Range("AI33") = Worksheets("Year at a Glance").Range("M31") | Range("AI34") = Worksheets("Year at a Glance").Range("M32") | Range("AI35") = Worksheets("Year at a Glance").Range("M33") | Range("AI38") = Worksheets("Year at a Glance").Range("M35") | Range("AI39") = Worksheets("Year at a Glance").Range("M36") | Range("AI40") = Worksheets("Year at a Glance").Range("M37") | Range("AI41") = Worksheets("Year at a Glance").Range("M38") | Range("AI42") = Worksheets("Year at a Glance").Range("M39") | | Case Is = "DECEMBER" | Range("AI7") = Worksheets("Year at a Glance").Range("N5") | Range("AI8") = Worksheets("Year at a Glance").Range("N6") | Range("AI9") = Worksheets("Year at a Glance").Range("N7") | Range("AI10") = Worksheets("Year at a Glance").Range("N8") | Range("AI11") = Worksheets("Year at a Glance").Range("N9") | Range("AI12") = Worksheets("Year at a Glance").Range("N10") | Range("AI13") = Worksheets("Year at a Glance").Range("N11") | Range("AI14") = Worksheets("Year at a Glance").Range("N12") | Range("AI16") = Worksheets("Year at a Glance").Range("N14") | Range("AI17") = Worksheets("Year at a Glance").Range("N15") | Range("AI18") = Worksheets("Year at a Glance").Range("N16") | Range("AI19") = Worksheets("Year at a Glance").Range("N17") | Range("AI21") = Worksheets("Year at a Glance").Range("N19") | Range("AI22") = Worksheets("Year at a Glance").Range("N20") | Range("AI23") = Worksheets("Year at a Glance").Range("N21") | Range("AI24") = Worksheets("Year at a Glance").Range("N22") | Range("AI25") = Worksheets("Year at a Glance").Range("N23") | Range("AI26") = Worksheets("Year at a Glance").Range("N24") | Range("AI28") = Worksheets("Year at a Glance").Range("N26") | Range("AI29") = Worksheets("Year at a Glance").Range("N27") | Range("AI30") = Worksheets("Year at a Glance").Range("N28") | Range("AI31") = Worksheets("Year at a Glance").Range("N29") | Range("AI33") = Worksheets("Year at a Glance").Range("N31") | Range("AI34") = Worksheets("Year at a Glance").Range("N32") | Range("AI35") = Worksheets("Year at a Glance").Range("N33") | Range("AI38") = Worksheets("Year at a Glance").Range("N35") | Range("AI39") = Worksheets("Year at a Glance").Range("N36") | Range("AI40") = Worksheets("Year at a Glance").Range("N37") | Range("AI41") = Worksheets("Year at a Glance").Range("N38") | Range("AI42") = Worksheets("Year at a Glance").Range("N39") | | End Select | -- | Carlee | | | "Jim Thomlinson" wrote: | | Post your code... | -- | HTH... | | Jim Thomlinson | | | "Carlee" wrote: | | Hi there, | I am using a case statement that where a condition state exists, a column of | values are populated for a specific month using: range("AI7") = | Range("J97"). I have about 30 of these statements, for twelve conditions. | | Problem: this takes a long time to execute. Is there a faster way? | -- | Carlee |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
With Application
.ScreenUpdating = False .Calculation = xlCalculationManual End With Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Worksheets("Year at a Glance").Range("C5:C39").Copy Range("AI7") Case Is = "FEBRUARY" Worksheets("Year at a Glance").Range("D5:D39").Copy Range("AI7") Case Is = "MARCH" Worksheets("Year at a Glance").Range("E5:E39").Copy Range("AI7") Case Is = "APRIL" Worksheets("Year at a Glance").Range("F5:F39").Copy Range("AI7") Case Is = "MAY" Worksheets("Year at a Glance").Range("G5:G39").Copy Range("AI7") Case Is = "JUNE" Worksheets("Year at a Glance").Range("H5:H39").Copy Range("AI7") Case Is = "JULY" Worksheets("Year at a Glance").Range("I5:I39").Copy Range("AI7") Case Is = "AUGUST" Worksheets("Year at a Glance").Range("J5:J39").Copy Range("AI7") Case Is = "SEPTEMBER" Worksheets("Year at a Glance").Range("K5:K39").Copy Range("AI7") Case Is = "OCTOBER" Worksheets("Year at a Glance").Range("L5:L39").Copy Range("AI7") Case Is = "NOVEMBERL" Worksheets("Year at a Glance").Range("M5:M39").Copy Range("AI7") Case Is = "DECEMBER" Worksheets("Year at a Glance").Range("N5:N39").Copy Range("AI7") End Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Carlee" wrote in message ... Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
or even:
Dim iMonth%, iCol% iMonth = Month(Trim(Worksheets(destSheet).Range("B4")) & " 1/2000") iCol = 2 + iMonth With Worksheets("Year at a Glance") .Range(.Cells(5, iCol), .Cells(39, iCol)).Copy Range("AI7") End With "Bob Phillips" wrote: With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Worksheets("Year at a Glance").Range("C5:C39").Copy Range("AI7") Case Is = "FEBRUARY" Worksheets("Year at a Glance").Range("D5:D39").Copy Range("AI7") Case Is = "MARCH" Worksheets("Year at a Glance").Range("E5:E39").Copy Range("AI7") Case Is = "APRIL" Worksheets("Year at a Glance").Range("F5:F39").Copy Range("AI7") Case Is = "MAY" Worksheets("Year at a Glance").Range("G5:G39").Copy Range("AI7") Case Is = "JUNE" Worksheets("Year at a Glance").Range("H5:H39").Copy Range("AI7") Case Is = "JULY" Worksheets("Year at a Glance").Range("I5:I39").Copy Range("AI7") Case Is = "AUGUST" Worksheets("Year at a Glance").Range("J5:J39").Copy Range("AI7") Case Is = "SEPTEMBER" Worksheets("Year at a Glance").Range("K5:K39").Copy Range("AI7") Case Is = "OCTOBER" Worksheets("Year at a Glance").Range("L5:L39").Copy Range("AI7") Case Is = "NOVEMBERL" Worksheets("Year at a Glance").Range("M5:M39").Copy Range("AI7") Case Is = "DECEMBER" Worksheets("Year at a Glance").Range("N5:N39").Copy Range("AI7") End Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
OK, I didn't notice skipped cells.
Ignore my post. Carlee wrote: Range("AI14") = Range("AI16") = Dave D-C wrote: or even: Dim iMonth%, iCol% iMonth = Month(Trim(Worksheets(destSheet).Range("B4")) & " 1/2000") iCol = 2 + iMonth With Worksheets("Year at a Glance") .Range(.Cells(5, iCol), .Cells(39, iCol)).Copy Range("AI7") End With |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
If you really care about the cells that you skipped...
Option Explicit Sub testme() Dim myDate As String Dim myMonth As Long Dim DestSheet As Worksheet Dim DestRng As Range Dim OrigRng As Range Dim gCtr As Long 'groupCounter Dim DestAddr As Variant Dim HowMany As Variant Dim FromRow As Variant Dim wks As Worksheet 'what sheet is getting these values? Set wks = ActiveSheet 'six groups starting in 'ai7, ai16, ai21, ai28, ai33, ai38 'for this many cells ' 8, 4, 6, 4, 3, 5 'starting in rows ' 5, 14, 19, 26, 31, 35 DestAddr = Array("ai7", "ai16", "ai21", "ai28", "ai33", "ai38") FromRow = Array(5, 14, 19, 26, 31, 35) HowMany = Array(8, 4, 6, 4, 3, 5) If UBound(DestAddr) = UBound(HowMany) _ And UBound(FromRow) = UBound(HowMany) Then 'whew, they match! Else MsgBox "Design error -- contact Carlee @ xxxx" Exit Sub End If myDate = Trim(Worksheets(DestSheet).Range("B4").Value) myDate = myDate & " 1, 2000" If IsDate(myDate) = False Then MsgBox "Not a month in B4 of " & DestSheet Exit Sub Else myMonth = Month(myDate) End If For gCtr = LBound(DestAddr) To UBound(DestAddr) Set DestRng = wks.Range(DestAddr).Resize(HowMany(gCtr), 1) Set OrigRng = Worksheets("Year at a Glance") _ .Cells(FromRow, "B").Offset(0, myMonth) _ .Resize(HowMany(gCtr), 1) DestRng.Value = OrigRng.Value Next gCtr End Sub Untested, but it did compile. Carlee wrote: Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Hmmm! Replying before the question is asked now Dave?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... If you really care about the cells that you skipped... Option Explicit Sub testme() Dim myDate As String Dim myMonth As Long Dim DestSheet As Worksheet Dim DestRng As Range Dim OrigRng As Range Dim gCtr As Long 'groupCounter Dim DestAddr As Variant Dim HowMany As Variant Dim FromRow As Variant Dim wks As Worksheet 'what sheet is getting these values? Set wks = ActiveSheet 'six groups starting in 'ai7, ai16, ai21, ai28, ai33, ai38 'for this many cells ' 8, 4, 6, 4, 3, 5 'starting in rows ' 5, 14, 19, 26, 31, 35 DestAddr = Array("ai7", "ai16", "ai21", "ai28", "ai33", "ai38") FromRow = Array(5, 14, 19, 26, 31, 35) HowMany = Array(8, 4, 6, 4, 3, 5) If UBound(DestAddr) = UBound(HowMany) _ And UBound(FromRow) = UBound(HowMany) Then 'whew, they match! Else MsgBox "Design error -- contact Carlee @ xxxx" Exit Sub End If myDate = Trim(Worksheets(DestSheet).Range("B4").Value) myDate = myDate & " 1, 2000" If IsDate(myDate) = False Then MsgBox "Not a month in B4 of " & DestSheet Exit Sub Else myMonth = Month(myDate) End If For gCtr = LBound(DestAddr) To UBound(DestAddr) Set DestRng = wks.Range(DestAddr).Resize(HowMany(gCtr), 1) Set OrigRng = Worksheets("Year at a Glance") _ .Cells(FromRow, "B").Offset(0, myMonth) _ .Resize(HowMany(gCtr), 1) DestRng.Value = OrigRng.Value Next gCtr End Sub Untested, but it did compile. Carlee wrote: Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Ugh.
I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). I've been posting with the wrong times for at least the better part of a day! Glad you noticed and yelled! Bob Phillips wrote: Hmmm! Replying before the question is asked now Dave? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... If you really care about the cells that you skipped... Option Explicit Sub testme() Dim myDate As String Dim myMonth As Long Dim DestSheet As Worksheet Dim DestRng As Range Dim OrigRng As Range Dim gCtr As Long 'groupCounter Dim DestAddr As Variant Dim HowMany As Variant Dim FromRow As Variant Dim wks As Worksheet 'what sheet is getting these values? Set wks = ActiveSheet 'six groups starting in 'ai7, ai16, ai21, ai28, ai33, ai38 'for this many cells ' 8, 4, 6, 4, 3, 5 'starting in rows ' 5, 14, 19, 26, 31, 35 DestAddr = Array("ai7", "ai16", "ai21", "ai28", "ai33", "ai38") FromRow = Array(5, 14, 19, 26, 31, 35) HowMany = Array(8, 4, 6, 4, 3, 5) If UBound(DestAddr) = UBound(HowMany) _ And UBound(FromRow) = UBound(HowMany) Then 'whew, they match! Else MsgBox "Design error -- contact Carlee @ xxxx" Exit Sub End If myDate = Trim(Worksheets(DestSheet).Range("B4").Value) myDate = myDate & " 1, 2000" If IsDate(myDate) = False Then MsgBox "Not a month in B4 of " & DestSheet Exit Sub Else myMonth = Month(myDate) End If For gCtr = LBound(DestAddr) To UBound(DestAddr) Set DestRng = wks.Range(DestAddr).Resize(HowMany(gCtr), 1) Set OrigRng = Worksheets("Year at a Glance") _ .Cells(FromRow, "B").Offset(0, myMonth) _ .Resize(HowMany(gCtr), 1) DestRng.Value = OrigRng.Value Next gCtr End Sub Untested, but it did compile. Carlee wrote: Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can this be done faster? | Excel Discussion (Misc queries) | |||
| Any way to do this faster? | Excel Programming | |||
| needs to run faster | Excel Programming | |||
| Faster way to do this? | Excel Programming | |||
| Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Hybrid Mode
