| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting Procedures
When I run the code below I get a compile error message
that states procedure to large. The knowledge base tells me to split the procedure. How do i do this? Here is the procedure below. Any suggestions or help is extremely appreciated. This code is repeated until Case 14 where only the Worksheet name changes "QA1"-"QA15". How can I split it and still update the cells when the Up1 command button is clicked? Private Sub Up1_Click() Select Case LB1.ListIndex Case 0 'First item in list Range("C5").Value = Worksheets("QA1").Range ("B4").Value Range("C6").Value = Worksheets("QA1").Range ("E4").Value Range("C13").Value = Worksheets("QA1").Range ("D6").Value Range("D13").Value = Worksheets("QA1").Range ("E6").Value Range("E13").Value = Worksheets("QA1").Range ("F6").Value Range("F13").Value = Worksheets("QA1").Range ("G6").Value Range("G13").Value = Worksheets("QA1").Range ("H6").Value Range("H13").Value = Worksheets("QA1").Range ("I6").Value Range("I13").Value = Worksheets("QA1").Range ("J6").Value Range("J13").Value = Worksheets("QA1").Range ("K6").Value Range("K13").Value = Worksheets("QA1").Range ("L6").Value Range("L13").Value = Worksheets("QA1").Range ("M6").Value Range("M13").Value = Worksheets("QA1").Range ("N6").Value Range("N13").Value = Worksheets("QA1").Range ("O6").Value Range("C14").Value = Worksheets("QA1").Range ("D9").Value Range("D14").Value = Worksheets("QA1").Range ("E9").Value Range("E14").Value = Worksheets("QA1").Range ("F9").Value Range("F14").Value = Worksheets("QA1").Range ("G9").Value Range("G14").Value = Worksheets("QA1").Range ("H9").Value Range("H14").Value = Worksheets("QA1").Range ("I9").Value Range("I14").Value = Worksheets("QA1").Range ("J9").Value Range("J14").Value = Worksheets("QA1").Range ("K9").Value Range("K14").Value = Worksheets("QA1").Range ("L9").Value Range("L14").Value = Worksheets("QA1").Range ("M9").Value Range("M14").Value = Worksheets("QA1").Range ("N9").Value Range("N14").Value = Worksheets("QA1").Range ("O9").Value Range("C31").Value = Worksheets("QA1").Range ("D11").Value Range("D31").Value = Worksheets("QA1").Range ("E11").Value Range("E31").Value = Worksheets("QA1").Range ("F11").Value Range("F31").Value = Worksheets("QA1").Range ("G11").Value Range("G31").Value = Worksheets("QA1").Range ("H11").Value Range("H31").Value = Worksheets("QA1").Range ("I11").Value Range("I31").Value = Worksheets("QA1").Range ("J11").Value Range("J31").Value = Worksheets("QA1").Range ("K11").Value Range("K31").Value = Worksheets("QA1").Range ("L11").Value Range("L31").Value = Worksheets("QA1").Range ("M11").Value Range("M31").Value = Worksheets("QA1").Range ("N11").Value Range("N31").Value = Worksheets("QA1").Range ("O11").Value Range("C49").Value = Worksheets("QA1").Range ("D12").Value Range("D49").Value = Worksheets("QA1").Range ("E12").Value Range("E49").Value = Worksheets("QA1").Range ("F12").Value Range("F49").Value = Worksheets("QA1").Range ("G12").Value Range("G49").Value = Worksheets("QA1").Range ("H12").Value Range("H49").Value = Worksheets("QA1").Range ("I12").Value Range("I49").Value = Worksheets("QA1").Range ("J12").Value Range("J49").Value = Worksheets("QA1").Range ("K12").Value Range("K49").Value = Worksheets("QA1").Range ("L12").Value Range("L49").Value = Worksheets("QA1").Range ("M12").Value Range("M49").Value = Worksheets("QA1").Range ("N12").Value Range("N49").Value = Worksheets("QA1").Range ("O12").Value Range("C78").Value = Worksheets("QA1").Range ("D13").Value Range("D78").Value = Worksheets("QA1").Range ("E13").Value Range("E78").Value = Worksheets("QA1").Range ("F13").Value Range("F78").Value = Worksheets("QA1").Range ("G13").Value Range("G78").Value = Worksheets("QA1").Range ("H13").Value Range("H78").Value = Worksheets("QA1").Range ("I13").Value Range("I78").Value = Worksheets("QA1").Range ("J13").Value Range("J78").Value = Worksheets("QA1").Range ("K13").Value Range("K78").Value = Worksheets("QA1").Range ("L13").Value Range("L78").Value = Worksheets("QA1").Range ("M13").Value Range("M78").Value = Worksheets("QA1").Range ("N13").Value Range("N78").Value = Worksheets("QA1").Range ("O13").Value Range("C96").Value = Worksheets("QA1").Range ("D14").Value Range("D96").Value = Worksheets("QA1").Range ("E14").Value Range("E96").Value = Worksheets("QA1").Range ("F14").Value Range("F96").Value = Worksheets("QA1").Range ("G14").Value Range("G96").Value = Worksheets("QA1").Range ("H14").Value Range("H96").Value = Worksheets("QA1").Range ("I14").Value Range("I96").Value = Worksheets("QA1").Range ("J14").Value Range("J96").Value = Worksheets("QA1").Range ("K14").Value Range("K96").Value = Worksheets("QA1").Range ("L14").Value Range("L96").Value = Worksheets("QA1").Range ("M14").Value Range("M96").Value = Worksheets("QA1").Range ("N14").Value Range("N96").Value = Worksheets("QA1").Range ("O14").Value Range("C114").Value = Worksheets("QA1").Range ("D16").Value Range("D114").Value = Worksheets("QA1").Range ("E16").Value Range("E114").Value = Worksheets("QA1").Range ("F16").Value Range("F114").Value = Worksheets("QA1").Range ("G16").Value Range("G114").Value = Worksheets("QA1").Range ("H16").Value Range("H114").Value = Worksheets("QA1").Range ("I16").Value Range("I114").Value = Worksheets("QA1").Range ("J16").Value Range("J114").Value = Worksheets("QA1").Range ("K16").Value Range("K114").Value = Worksheets("QA1").Range ("L16").Value Range("L114").Value = Worksheets("QA1").Range ("M16").Value Range("M114").Value = Worksheets("QA1").Range ("N16").Value Range("N114").Value = Worksheets("QA1").Range ("O16").Value End Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Splitting Procedures
Mark,
See your previous post for some other suggestions. Please don't post duplicate questions in the ng if you don't get an answer right away to the first one. Be patient. If you're question is answerable (and most are) we'll eventually get to it. John Markus wrote: When I run the code below I get a compile error message that states procedure to large. The knowledge base tells me to split the procedure. How do i do this? Here is the procedure below. Any suggestions or help is extremely appreciated. This code is repeated until Case 14 where only the Worksheet name changes "QA1"-"QA15". How can I split it and still update the cells when the Up1 command button is clicked? Private Sub Up1_Click() Select Case LB1.ListIndex Case 0 'First item in list Range("C5").Value = Worksheets("QA1").Range ("B4").Value Range("C6").Value = Worksheets("QA1").Range ("E4").Value Range("C13").Value = Worksheets("QA1").Range ("D6").Value Range("D13").Value = Worksheets("QA1").Range ("E6").Value Range("E13").Value = Worksheets("QA1").Range ("F6").Value Range("F13").Value = Worksheets("QA1").Range ("G6").Value Range("G13").Value = Worksheets("QA1").Range ("H6").Value Range("H13").Value = Worksheets("QA1").Range ("I6").Value Range("I13").Value = Worksheets("QA1").Range ("J6").Value Range("J13").Value = Worksheets("QA1").Range ("K6").Value Range("K13").Value = Worksheets("QA1").Range ("L6").Value Range("L13").Value = Worksheets("QA1").Range ("M6").Value Range("M13").Value = Worksheets("QA1").Range ("N6").Value Range("N13").Value = Worksheets("QA1").Range ("O6").Value Range("C14").Value = Worksheets("QA1").Range ("D9").Value Range("D14").Value = Worksheets("QA1").Range ("E9").Value Range("E14").Value = Worksheets("QA1").Range ("F9").Value Range("F14").Value = Worksheets("QA1").Range ("G9").Value Range("G14").Value = Worksheets("QA1").Range ("H9").Value Range("H14").Value = Worksheets("QA1").Range ("I9").Value Range("I14").Value = Worksheets("QA1").Range ("J9").Value Range("J14").Value = Worksheets("QA1").Range ("K9").Value Range("K14").Value = Worksheets("QA1").Range ("L9").Value Range("L14").Value = Worksheets("QA1").Range ("M9").Value Range("M14").Value = Worksheets("QA1").Range ("N9").Value Range("N14").Value = Worksheets("QA1").Range ("O9").Value Range("C31").Value = Worksheets("QA1").Range ("D11").Value Range("D31").Value = Worksheets("QA1").Range ("E11").Value Range("E31").Value = Worksheets("QA1").Range ("F11").Value Range("F31").Value = Worksheets("QA1").Range ("G11").Value Range("G31").Value = Worksheets("QA1").Range ("H11").Value Range("H31").Value = Worksheets("QA1").Range ("I11").Value Range("I31").Value = Worksheets("QA1").Range ("J11").Value Range("J31").Value = Worksheets("QA1").Range ("K11").Value Range("K31").Value = Worksheets("QA1").Range ("L11").Value Range("L31").Value = Worksheets("QA1").Range ("M11").Value Range("M31").Value = Worksheets("QA1").Range ("N11").Value Range("N31").Value = Worksheets("QA1").Range ("O11").Value Range("C49").Value = Worksheets("QA1").Range ("D12").Value Range("D49").Value = Worksheets("QA1").Range ("E12").Value Range("E49").Value = Worksheets("QA1").Range ("F12").Value Range("F49").Value = Worksheets("QA1").Range ("G12").Value Range("G49").Value = Worksheets("QA1").Range ("H12").Value Range("H49").Value = Worksheets("QA1").Range ("I12").Value Range("I49").Value = Worksheets("QA1").Range ("J12").Value Range("J49").Value = Worksheets("QA1").Range ("K12").Value Range("K49").Value = Worksheets("QA1").Range ("L12").Value Range("L49").Value = Worksheets("QA1").Range ("M12").Value Range("M49").Value = Worksheets("QA1").Range ("N12").Value Range("N49").Value = Worksheets("QA1").Range ("O12").Value Range("C78").Value = Worksheets("QA1").Range ("D13").Value Range("D78").Value = Worksheets("QA1").Range ("E13").Value Range("E78").Value = Worksheets("QA1").Range ("F13").Value Range("F78").Value = Worksheets("QA1").Range ("G13").Value Range("G78").Value = Worksheets("QA1").Range ("H13").Value Range("H78").Value = Worksheets("QA1").Range ("I13").Value Range("I78").Value = Worksheets("QA1").Range ("J13").Value Range("J78").Value = Worksheets("QA1").Range ("K13").Value Range("K78").Value = Worksheets("QA1").Range ("L13").Value Range("L78").Value = Worksheets("QA1").Range ("M13").Value Range("M78").Value = Worksheets("QA1").Range ("N13").Value Range("N78").Value = Worksheets("QA1").Range ("O13").Value Range("C96").Value = Worksheets("QA1").Range ("D14").Value Range("D96").Value = Worksheets("QA1").Range ("E14").Value Range("E96").Value = Worksheets("QA1").Range ("F14").Value Range("F96").Value = Worksheets("QA1").Range ("G14").Value Range("G96").Value = Worksheets("QA1").Range ("H14").Value Range("H96").Value = Worksheets("QA1").Range ("I14").Value Range("I96").Value = Worksheets("QA1").Range ("J14").Value Range("J96").Value = Worksheets("QA1").Range ("K14").Value Range("K96").Value = Worksheets("QA1").Range ("L14").Value Range("L96").Value = Worksheets("QA1").Range ("M14").Value Range("M96").Value = Worksheets("QA1").Range ("N14").Value Range("N96").Value = Worksheets("QA1").Range ("O14").Value Range("C114").Value = Worksheets("QA1").Range ("D16").Value Range("D114").Value = Worksheets("QA1").Range ("E16").Value Range("E114").Value = Worksheets("QA1").Range ("F16").Value Range("F114").Value = Worksheets("QA1").Range ("G16").Value Range("G114").Value = Worksheets("QA1").Range ("H16").Value Range("H114").Value = Worksheets("QA1").Range ("I16").Value Range("I114").Value = Worksheets("QA1").Range ("J16").Value Range("J114").Value = Worksheets("QA1").Range ("K16").Value Range("K114").Value = Worksheets("QA1").Range ("L16").Value Range("L114").Value = Worksheets("QA1").Range ("M16").Value Range("M114").Value = Worksheets("QA1").Range ("N16").Value Range("N114").Value = Worksheets("QA1").Range ("O16").Value End Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Splitting Procedures
You need to learn about loops!
1. If the only difference between the Select Case blocks is the sheet name, you can construct the sheet name from the ListIndex, i.e. SheetName = "QA" & LB1.ListIndex + 1 and you don't need a select case block at all. 2. You can replace the 12 statements that set C13:N13 with data from D6:O6 with one: Range("C13:N13").Value = Worksheets(SheetName).Range("D6:O6").Value Similarly Range("C14:N14").Value = worksheets(SheetName).Range("D9:09").Value 3. But you can copy the rows more efficiently with a For/Next loop. Now you won't have any problem with the size of your code <vbg. Please let me know if the following does what you want. BTW, if you really wanted to split the procedure, you would have to create 15 different subs, in 2 or 3 modules, and in your Click event procedure you would call the right routine based on the list index. So the Select Case block would look like Select Case LB1.List.Index Case 0: DoSheetQA1 Case 1: DoSheetQA2 etc. But 15 subs ISN'T the right way to do this! Here's the code: Private Sub Up1_Click() Dim QA As Worksheet Dim SourceRows As Variant Dim DestRows As Variant Dim I As Long Set QA = Worksheets("QA" & LB1.ListIndex + 1) SourceRows = Array(6, 9, 11, 12, 13, 14, 16) 'check that I have these right! DestRows = Array(13, 14, 31, 49, 78, 96, 114) Range("C5").Value = QA.Range("B4").Value Range("C6").Value = QA.Range("E4").Value For I = LBound(SourceRows) To Ubound(SourceRows) 'copy all 12 cells from source row column D 'to destination row column C Cells(DestRows(I), "C").Resize(1, 12).Value = _ QA.Cells(SourceRows(I), "D").Resize(1, 12).Value Next I End Sub On Tue, 26 Aug 2003 07:29:50 -0700, "Markus" wrote: When I run the code below I get a compile error message that states procedure to large. The knowledge base tells me to split the procedure. How do i do this? Here is the procedure below. Any suggestions or help is extremely appreciated. This code is repeated until Case 14 where only the Worksheet name changes "QA1"-"QA15". How can I split it and still update the cells when the Up1 command button is clicked? Private Sub Up1_Click() Select Case LB1.ListIndex Case 0 'First item in list Range("C5").Value = Worksheets("QA1").Range ("B4").Value Range("C6").Value = Worksheets("QA1").Range ("E4").Value Range("C13").Value = Worksheets("QA1").Range ("D6").Value Range("D13").Value = Worksheets("QA1").Range ("E6").Value Range("E13").Value = Worksheets("QA1").Range ("F6").Value Range("F13").Value = Worksheets("QA1").Range ("G6").Value Range("G13").Value = Worksheets("QA1").Range ("H6").Value Range("H13").Value = Worksheets("QA1").Range ("I6").Value Range("I13").Value = Worksheets("QA1").Range ("J6").Value Range("J13").Value = Worksheets("QA1").Range ("K6").Value Range("K13").Value = Worksheets("QA1").Range ("L6").Value Range("L13").Value = Worksheets("QA1").Range ("M6").Value Range("M13").Value = Worksheets("QA1").Range ("N6").Value Range("N13").Value = Worksheets("QA1").Range ("O6").Value Range("C14").Value = Worksheets("QA1").Range ("D9").Value Range("D14").Value = Worksheets("QA1").Range ("E9").Value Range("E14").Value = Worksheets("QA1").Range ("F9").Value Range("F14").Value = Worksheets("QA1").Range ("G9").Value Range("G14").Value = Worksheets("QA1").Range ("H9").Value Range("H14").Value = Worksheets("QA1").Range ("I9").Value Range("I14").Value = Worksheets("QA1").Range ("J9").Value Range("J14").Value = Worksheets("QA1").Range ("K9").Value Range("K14").Value = Worksheets("QA1").Range ("L9").Value Range("L14").Value = Worksheets("QA1").Range ("M9").Value Range("M14").Value = Worksheets("QA1").Range ("N9").Value Range("N14").Value = Worksheets("QA1").Range ("O9").Value Range("C31").Value = Worksheets("QA1").Range ("D11").Value Range("D31").Value = Worksheets("QA1").Range ("E11").Value Range("E31").Value = Worksheets("QA1").Range ("F11").Value Range("F31").Value = Worksheets("QA1").Range ("G11").Value Range("G31").Value = Worksheets("QA1").Range ("H11").Value Range("H31").Value = Worksheets("QA1").Range ("I11").Value Range("I31").Value = Worksheets("QA1").Range ("J11").Value Range("J31").Value = Worksheets("QA1").Range ("K11").Value Range("K31").Value = Worksheets("QA1").Range ("L11").Value Range("L31").Value = Worksheets("QA1").Range ("M11").Value Range("M31").Value = Worksheets("QA1").Range ("N11").Value Range("N31").Value = Worksheets("QA1").Range ("O11").Value Range("C49").Value = Worksheets("QA1").Range ("D12").Value Range("D49").Value = Worksheets("QA1").Range ("E12").Value Range("E49").Value = Worksheets("QA1").Range ("F12").Value Range("F49").Value = Worksheets("QA1").Range ("G12").Value Range("G49").Value = Worksheets("QA1").Range ("H12").Value Range("H49").Value = Worksheets("QA1").Range ("I12").Value Range("I49").Value = Worksheets("QA1").Range ("J12").Value Range("J49").Value = Worksheets("QA1").Range ("K12").Value Range("K49").Value = Worksheets("QA1").Range ("L12").Value Range("L49").Value = Worksheets("QA1").Range ("M12").Value Range("M49").Value = Worksheets("QA1").Range ("N12").Value Range("N49").Value = Worksheets("QA1").Range ("O12").Value Range("C78").Value = Worksheets("QA1").Range ("D13").Value Range("D78").Value = Worksheets("QA1").Range ("E13").Value Range("E78").Value = Worksheets("QA1").Range ("F13").Value Range("F78").Value = Worksheets("QA1").Range ("G13").Value Range("G78").Value = Worksheets("QA1").Range ("H13").Value Range("H78").Value = Worksheets("QA1").Range ("I13").Value Range("I78").Value = Worksheets("QA1").Range ("J13").Value Range("J78").Value = Worksheets("QA1").Range ("K13").Value Range("K78").Value = Worksheets("QA1").Range ("L13").Value Range("L78").Value = Worksheets("QA1").Range ("M13").Value Range("M78").Value = Worksheets("QA1").Range ("N13").Value Range("N78").Value = Worksheets("QA1").Range ("O13").Value Range("C96").Value = Worksheets("QA1").Range ("D14").Value Range("D96").Value = Worksheets("QA1").Range ("E14").Value Range("E96").Value = Worksheets("QA1").Range ("F14").Value Range("F96").Value = Worksheets("QA1").Range ("G14").Value Range("G96").Value = Worksheets("QA1").Range ("H14").Value Range("H96").Value = Worksheets("QA1").Range ("I14").Value Range("I96").Value = Worksheets("QA1").Range ("J14").Value Range("J96").Value = Worksheets("QA1").Range ("K14").Value Range("K96").Value = Worksheets("QA1").Range ("L14").Value Range("L96").Value = Worksheets("QA1").Range ("M14").Value Range("M96").Value = Worksheets("QA1").Range ("N14").Value Range("N96").Value = Worksheets("QA1").Range ("O14").Value Range("C114").Value = Worksheets("QA1").Range ("D16").Value Range("D114").Value = Worksheets("QA1").Range ("E16").Value Range("E114").Value = Worksheets("QA1").Range ("F16").Value Range("F114").Value = Worksheets("QA1").Range ("G16").Value Range("G114").Value = Worksheets("QA1").Range ("H16").Value Range("H114").Value = Worksheets("QA1").Range ("I16").Value Range("I114").Value = Worksheets("QA1").Range ("J16").Value Range("J114").Value = Worksheets("QA1").Range ("K16").Value Range("K114").Value = Worksheets("QA1").Range ("L16").Value Range("L114").Value = Worksheets("QA1").Range ("M16").Value Range("M114").Value = Worksheets("QA1").Range ("N16").Value Range("N114").Value = Worksheets("QA1").Range ("O16").Value End Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Splitting Procedures
for i = 0 to 7
should be for i = 0 to 6 or for i = lbound(varr) to ubound(varr) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Private Sub Up1_Click() Dim i as long, idex as long Dim rngSource as Range, rngDest as Range Dim sName as String Dim varr as variant, varr1 as variant idex = LB1.ListIndex+1 SName = "QA"&idex Range("C5").Value = Worksheets(sName).Range("B4").Value Range("C6").Value = Worksheets(sName).Range("E4").Value varr = Array(13,14,31,49,78,96,114) varr1 = Array(6,9,11,12,13,14,16) for i = 0 to 7 set rngSource = worksheets(sName).Cells(varr1(i),4).Resize(1,12) set rngDest = Cells(varr(i),3).Resize(1,12) rngDest.Value = rngSource.value Next End Sub -- Regards, Tom Ogilvy "Markus" wrote in message ... When I run the code below I get a compile error message that states procedure to large. The knowledge base tells me to split the procedure. How do i do this? Here is the procedure below. Any suggestions or help is extremely appreciated. This code is repeated until Case 14 where only the Worksheet name changes "QA1"-"QA15". How can I split it and still update the cells when the Up1 command button is clicked? Private Sub Up1_Click() Select Case LB1.ListIndex Case 0 'First item in list Range("C5").Value = Worksheets("QA1").Range ("B4").Value Range("C6").Value = Worksheets("QA1").Range ("E4").Value Range("C13").Value = Worksheets("QA1").Range ("D6").Value Range("D13").Value = Worksheets("QA1").Range ("E6").Value Range("E13").Value = Worksheets("QA1").Range ("F6").Value Range("F13").Value = Worksheets("QA1").Range ("G6").Value Range("G13").Value = Worksheets("QA1").Range ("H6").Value Range("H13").Value = Worksheets("QA1").Range ("I6").Value Range("I13").Value = Worksheets("QA1").Range ("J6").Value Range("J13").Value = Worksheets("QA1").Range ("K6").Value Range("K13").Value = Worksheets("QA1").Range ("L6").Value Range("L13").Value = Worksheets("QA1").Range ("M6").Value Range("M13").Value = Worksheets("QA1").Range ("N6").Value Range("N13").Value = Worksheets("QA1").Range ("O6").Value Range("C14").Value = Worksheets("QA1").Range ("D9").Value Range("D14").Value = Worksheets("QA1").Range ("E9").Value Range("E14").Value = Worksheets("QA1").Range ("F9").Value Range("F14").Value = Worksheets("QA1").Range ("G9").Value Range("G14").Value = Worksheets("QA1").Range ("H9").Value Range("H14").Value = Worksheets("QA1").Range ("I9").Value Range("I14").Value = Worksheets("QA1").Range ("J9").Value Range("J14").Value = Worksheets("QA1").Range ("K9").Value Range("K14").Value = Worksheets("QA1").Range ("L9").Value Range("L14").Value = Worksheets("QA1").Range ("M9").Value Range("M14").Value = Worksheets("QA1").Range ("N9").Value Range("N14").Value = Worksheets("QA1").Range ("O9").Value Range("C31").Value = Worksheets("QA1").Range ("D11").Value Range("D31").Value = Worksheets("QA1").Range ("E11").Value Range("E31").Value = Worksheets("QA1").Range ("F11").Value Range("F31").Value = Worksheets("QA1").Range ("G11").Value Range("G31").Value = Worksheets("QA1").Range ("H11").Value Range("H31").Value = Worksheets("QA1").Range ("I11").Value Range("I31").Value = Worksheets("QA1").Range ("J11").Value Range("J31").Value = Worksheets("QA1").Range ("K11").Value Range("K31").Value = Worksheets("QA1").Range ("L11").Value Range("L31").Value = Worksheets("QA1").Range ("M11").Value Range("M31").Value = Worksheets("QA1").Range ("N11").Value Range("N31").Value = Worksheets("QA1").Range ("O11").Value Range("C49").Value = Worksheets("QA1").Range ("D12").Value Range("D49").Value = Worksheets("QA1").Range ("E12").Value Range("E49").Value = Worksheets("QA1").Range ("F12").Value Range("F49").Value = Worksheets("QA1").Range ("G12").Value Range("G49").Value = Worksheets("QA1").Range ("H12").Value Range("H49").Value = Worksheets("QA1").Range ("I12").Value Range("I49").Value = Worksheets("QA1").Range ("J12").Value Range("J49").Value = Worksheets("QA1").Range ("K12").Value Range("K49").Value = Worksheets("QA1").Range ("L12").Value Range("L49").Value = Worksheets("QA1").Range ("M12").Value Range("M49").Value = Worksheets("QA1").Range ("N12").Value Range("N49").Value = Worksheets("QA1").Range ("O12").Value Range("C78").Value = Worksheets("QA1").Range ("D13").Value Range("D78").Value = Worksheets("QA1").Range ("E13").Value Range("E78").Value = Worksheets("QA1").Range ("F13").Value Range("F78").Value = Worksheets("QA1").Range ("G13").Value Range("G78").Value = Worksheets("QA1").Range ("H13").Value Range("H78").Value = Worksheets("QA1").Range ("I13").Value Range("I78").Value = Worksheets("QA1").Range ("J13").Value Range("J78").Value = Worksheets("QA1").Range ("K13").Value Range("K78").Value = Worksheets("QA1").Range ("L13").Value Range("L78").Value = Worksheets("QA1").Range ("M13").Value Range("M78").Value = Worksheets("QA1").Range ("N13").Value Range("N78").Value = Worksheets("QA1").Range ("O13").Value Range("C96").Value = Worksheets("QA1").Range ("D14").Value Range("D96").Value = Worksheets("QA1").Range ("E14").Value Range("E96").Value = Worksheets("QA1").Range ("F14").Value Range("F96").Value = Worksheets("QA1").Range ("G14").Value Range("G96").Value = Worksheets("QA1").Range ("H14").Value Range("H96").Value = Worksheets("QA1").Range ("I14").Value Range("I96").Value = Worksheets("QA1").Range ("J14").Value Range("J96").Value = Worksheets("QA1").Range ("K14").Value Range("K96").Value = Worksheets("QA1").Range ("L14").Value Range("L96").Value = Worksheets("QA1").Range ("M14").Value Range("M96").Value = Worksheets("QA1").Range ("N14").Value Range("N96").Value = Worksheets("QA1").Range ("O14").Value Range("C114").Value = Worksheets("QA1").Range ("D16").Value Range("D114").Value = Worksheets("QA1").Range ("E16").Value Range("E114").Value = Worksheets("QA1").Range ("F16").Value Range("F114").Value = Worksheets("QA1").Range ("G16").Value Range("G114").Value = Worksheets("QA1").Range ("H16").Value Range("H114").Value = Worksheets("QA1").Range ("I16").Value Range("I114").Value = Worksheets("QA1").Range ("J16").Value Range("J114").Value = Worksheets("QA1").Range ("K16").Value Range("K114").Value = Worksheets("QA1").Range ("L16").Value Range("L114").Value = Worksheets("QA1").Range ("M16").Value Range("M114").Value = Worksheets("QA1").Range ("N16").Value Range("N114").Value = Worksheets("QA1").Range ("O16").Value End Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Splitting Procedures
I apologize for that...the first time i posted the message
i placed the entire code in there and the post would not pull up on my PC. So i just re-entered the post again. -----Original Message----- Mark, See your previous post for some other suggestions. Please don't post duplicate questions in the ng if you don't get an answer right away to the first one. Be patient. If you're question is answerable (and most are) we'll eventually get to it. John Markus wrote: When I run the code below I get a compile error message that states procedure to large. The knowledge base tells me to split the procedure. How do i do this? Here is the procedure below. Any suggestions or help is extremely appreciated. This code is repeated until Case 14 where only the Worksheet name changes "QA1"-"QA15". How can I split it and still update the cells when the Up1 command button is clicked? Private Sub Up1_Click() Select Case LB1.ListIndex Case 0 'First item in list Range("C5").Value = Worksheets("QA1").Range ("B4").Value Range("C6").Value = Worksheets("QA1").Range ("E4").Value Range("C13").Value = Worksheets("QA1").Range ("D6").Value Range("D13").Value = Worksheets("QA1").Range ("E6").Value Range("E13").Value = Worksheets("QA1").Range ("F6").Value Range("F13").Value = Worksheets("QA1").Range ("G6").Value Range("G13").Value = Worksheets("QA1").Range ("H6").Value Range("H13").Value = Worksheets("QA1").Range ("I6").Value Range("I13").Value = Worksheets("QA1").Range ("J6").Value Range("J13").Value = Worksheets("QA1").Range ("K6").Value Range("K13").Value = Worksheets("QA1").Range ("L6").Value Range("L13").Value = Worksheets("QA1").Range ("M6").Value Range("M13").Value = Worksheets("QA1").Range ("N6").Value Range("N13").Value = Worksheets("QA1").Range ("O6").Value Range("C14").Value = Worksheets("QA1").Range ("D9").Value Range("D14").Value = Worksheets("QA1").Range ("E9").Value Range("E14").Value = Worksheets("QA1").Range ("F9").Value Range("F14").Value = Worksheets("QA1").Range ("G9").Value Range("G14").Value = Worksheets("QA1").Range ("H9").Value Range("H14").Value = Worksheets("QA1").Range ("I9").Value Range("I14").Value = Worksheets("QA1").Range ("J9").Value Range("J14").Value = Worksheets("QA1").Range ("K9").Value Range("K14").Value = Worksheets("QA1").Range ("L9").Value Range("L14").Value = Worksheets("QA1").Range ("M9").Value Range("M14").Value = Worksheets("QA1").Range ("N9").Value Range("N14").Value = Worksheets("QA1").Range ("O9").Value Range("C31").Value = Worksheets("QA1").Range ("D11").Value Range("D31").Value = Worksheets("QA1").Range ("E11").Value Range("E31").Value = Worksheets("QA1").Range ("F11").Value Range("F31").Value = Worksheets("QA1").Range ("G11").Value Range("G31").Value = Worksheets("QA1").Range ("H11").Value Range("H31").Value = Worksheets("QA1").Range ("I11").Value Range("I31").Value = Worksheets("QA1").Range ("J11").Value Range("J31").Value = Worksheets("QA1").Range ("K11").Value Range("K31").Value = Worksheets("QA1").Range ("L11").Value Range("L31").Value = Worksheets("QA1").Range ("M11").Value Range("M31").Value = Worksheets("QA1").Range ("N11").Value Range("N31").Value = Worksheets("QA1").Range ("O11").Value Range("C49").Value = Worksheets("QA1").Range ("D12").Value Range("D49").Value = Worksheets("QA1").Range ("E12").Value Range("E49").Value = Worksheets("QA1").Range ("F12").Value Range("F49").Value = Worksheets("QA1").Range ("G12").Value Range("G49").Value = Worksheets("QA1").Range ("H12").Value Range("H49").Value = Worksheets("QA1").Range ("I12").Value Range("I49").Value = Worksheets("QA1").Range ("J12").Value Range("J49").Value = Worksheets("QA1").Range ("K12").Value Range("K49").Value = Worksheets("QA1").Range ("L12").Value Range("L49").Value = Worksheets("QA1").Range ("M12").Value Range("M49").Value = Worksheets("QA1").Range ("N12").Value Range("N49").Value = Worksheets("QA1").Range ("O12").Value Range("C78").Value = Worksheets("QA1").Range ("D13").Value Range("D78").Value = Worksheets("QA1").Range ("E13").Value Range("E78").Value = Worksheets("QA1").Range ("F13").Value Range("F78").Value = Worksheets("QA1").Range ("G13").Value Range("G78").Value = Worksheets("QA1").Range ("H13").Value Range("H78").Value = Worksheets("QA1").Range ("I13").Value Range("I78").Value = Worksheets("QA1").Range ("J13").Value Range("J78").Value = Worksheets("QA1").Range ("K13").Value Range("K78").Value = Worksheets("QA1").Range ("L13").Value Range("L78").Value = Worksheets("QA1").Range ("M13").Value Range("M78").Value = Worksheets("QA1").Range ("N13").Value Range("N78").Value = Worksheets("QA1").Range ("O13").Value Range("C96").Value = Worksheets("QA1").Range ("D14").Value Range("D96").Value = Worksheets("QA1").Range ("E14").Value Range("E96").Value = Worksheets("QA1").Range ("F14").Value Range("F96").Value = Worksheets("QA1").Range ("G14").Value Range("G96").Value = Worksheets("QA1").Range ("H14").Value Range("H96").Value = Worksheets("QA1").Range ("I14").Value Range("I96").Value = Worksheets("QA1").Range ("J14").Value Range("J96").Value = Worksheets("QA1").Range ("K14").Value Range("K96").Value = Worksheets("QA1").Range ("L14").Value Range("L96").Value = Worksheets("QA1").Range ("M14").Value Range("M96").Value = Worksheets("QA1").Range ("N14").Value Range("N96").Value = Worksheets("QA1").Range ("O14").Value Range("C114").Value = Worksheets ("QA1").Range ("D16").Value Range("D114").Value = Worksheets ("QA1").Range ("E16").Value Range("E114").Value = Worksheets ("QA1").Range ("F16").Value Range("F114").Value = Worksheets ("QA1").Range ("G16").Value Range("G114").Value = Worksheets ("QA1").Range ("H16").Value Range("H114").Value = Worksheets ("QA1").Range ("I16").Value Range("I114").Value = Worksheets ("QA1").Range ("J16").Value Range("J114").Value = Worksheets ("QA1").Range ("K16").Value Range("K114").Value = Worksheets ("QA1").Range ("L16").Value Range("L114").Value = Worksheets ("QA1").Range ("M16").Value Range("M114").Value = Worksheets ("QA1").Range ("N16").Value Range("N114").Value = Worksheets ("QA1").Range ("O16").Value End Select End Sub . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Separating sub procedures | Excel Discussion (Misc queries) | |||
| Pivotcharts and stored procedures | Charts and Charting in Excel | |||
| Can I import SQL Stored Procedures into Excel? | Excel Discussion (Misc queries) | |||
| VBA function Procedures.... | Excel Worksheet Functions | |||
| VBA functions and Procedures.... | Excel Worksheet Functions | |||
Linear Mode
