Procedure Too Large Error

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
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?


Code:
[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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
For I = 6 To 6+34
 
 wsInv.Cells(WriteRowInv + I,  1) = wsIQ.Cells(1, 24)
       ' rest of code
Next I
 

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 7, 2010
Messages
134
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top