Procedure Too Large Error

[AlanAnderson]

Hi,
I have a procedure that basically consists of the following code repeated 34 times (line numbers change EG the next time the +6 is replaced by +7 and so on). As there is little else in the procedure I have no logical way of splitting the code into separate procedures.

Can anyone suggest how I can take my clumsy code and reduce it in size so I no longer get the Procedure Too Large error?

[SIZE=3][FONT=Times New Roman]       wsInv.Cells(WriteRowInv + 6, 1) = wsIQ.Cells(1, 24)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       wsInv.Cells(WriteRowInv + 6, 2) = wsIQ.Cells(i, 24)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       wsInv.Cells(WriteRowInv + 6, 3) = wsIQ.Cells(i, 25)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       wsInv.Cells(WriteRowInv + 6, 4) = wsIQ.Cells(i, 26)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       wsInv.Cells(WriteRowInv + 6, 5) = Val(wsInv.Cells(WriteRowInv + 6, 2) / 100 * vTotal)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       wsInv.Cells(WriteRowInv + 6, 6) = Val(wsInv.Cells(WriteRowInv + 6, 5) * vFxBase)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           If wsInv.Cells(WriteRowInv + 6, 4) = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 7) = Val(wsInv.Cells(WriteRowInv + 6, 5) * vFXNow)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 7) = wsInv.Cells(WriteRowInv + 6, 6)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           If wsInv.Cells(WriteRowInv + 6, 3) = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 8) = Val(wsInv.Cells(WriteRowInv + 6, 7) / vIndexBase * vIndexNow) ' NEED TO GET BOTH VARS[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 8) = wsInv.Cells(WriteRowInv + 6, 7)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           'This checks if item taxable[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           For u = 2 To FinalRowVar[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               vComp = wsInv.Cells(WriteRowInv + 6, 1)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                   If vComp = Sheets("Variables").Cells(u, 12) Then wsInv.Cells(WriteRowInv + 6, 9) = Sheets("Variables").Cells(u, 13)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           Next u[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           'If Yes looks up Vat rate at calcs <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:City w:st="on">Vat</st1:City> <st1:State w:st="on">Col</st1:State></st1:place> (10) and new total (11)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           If wsInv.Cells(WriteRowInv + 6, 9) = "Y" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 10) = wsInv.Cells(WriteRowInv + 6, 8) * Sheets("Variables").Cells(2, 4) / 100[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 11) = wsInv.Cells(WriteRowInv + 6, 8) + wsInv.Cells(WriteRowInv + 6, 10)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 10) = 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               wsInv.Cells(WriteRowInv + 6, 11) = wsInv.Cells(WriteRowInv + 6, 8)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           FinalRowVar = Sheets("Variables").Cells(Rows.Count, 11).End(xlUp).Row[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           For g = 2 To FinalRowVar[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               If wsIQ.Cells(1, 27) = Sheets("Variables").Cells(g, 11) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                     wsInv.Cells(WriteRowInv + 7, 1) = Sheets("Variables").Cells(g, 12)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]           Next g[/FONT][/SIZE]

Regards

Alan

 

[Norie]

Alan

Why not just use a loop and instead of hardcoding +6, +7 etc use a variable?

Something like this perhaps, though it would only work if the change was as simple as one value.

For I = 6 To 6+34
 
 wsInv.Cells(WriteRowInv + I,  1) = wsIQ.Cells(1, 24)
       ' rest of code
Next I

 

[AlanAnderson]

Hi Norie,
Thanks for suggestion. I don't know how I could do it. I oversimplified my statement earlier. There are other variables that change in each iteration of this code, name those referred to as 1,24; i24; i,25; i,26 in the first 4 lines.
I appreciate your help.

Thanks,

Alan

 

[Norie]

Alan

If the code you've posted is just one of the 34 repeated sections it's hard to tell what changes need to be made.

Actually, when I look at the code again I think there's probably a few other things that might be changed to shorten the code.

 

[AlanAnderson]

Hi Norie,
A little more background to the code. This code draws data from another sheet (wsIQ) and converts it into effectively an invoice. so column 25 is Headed "Transport Fees", 26 tells if if may be Escalated or not, 27 whether it is subject to foreign exchange rate variations. Similarly, 28, 29 and 30 refer to another category.

On the target sheet (wsInv) this code imports the Header, the value from(in above example) Col 25: True or false from 26,: true or false from 27:the next 4 columns are calculations depending on the two True/false conditions.
The next column is a Y or N (taxable or not - obtained from another sheet ). Next column is tax amount (if col 9 was Y) and final col (11) is effectively cols 8 + 9

I see no effective way of splitting code into two or more procedures.

As this routine is repeated so many times any reduction in length is going to make a significant difference.
Do you have any suggestions of how this code can be reduced?

With thanks,

Alan