Procedure Too Large problems
I'm writing a procedure that adds entries from a userform to different
worksheets, based on the value in a combobox. The first portion of the code is: Private Sub cmdADD_Click() ' need to write a proc for each vendor to handle the ranges - ugh ' Dim wks As Worksheet Dim vendorstr As String Dim shtname As String Dim totalrows As Integer Dim toprow Dim newrow As Integer If cbxVendorID.TextLength = 0 Then GoTo enderr01 shtname = "VCB" & cbxVendorID.Value ' 89 possible variants since 89 vendors If cbxVendorID.Value = "ADOR" Then GoTo vADOR Each add procedure to each wks is... vADOR: With Range("rADOR") toprow = .row totalrows = .Rows.Count newrow = toprow + totalrows .Cells(newrow, 2) = txtClaimNbr.Value .Cells(newrow, 3) = txtUnitSN.Value .Cells(newrow, 4) = txtDOP.Value .Cells(newrow, 5) = txtPartDescr.Value .Cells(newrow, 6) = txtPartNbr.Value .Cells(newrow, 7) = txtAppMod.Value .Cells(newrow, 8) = txtAppSN.Value .Cells(newrow, 10) = txtSquawk.Value .Cells(newrow, 9) = txtVCBCN.Value .Cells(newrow, 11) = txtPartCost.Value .Cells(newrow, 12) = txtDlrMkup.Value .Cells(newrow, 13) = txtLabor.Value .Cells(newrow, 14) = txtFrt.Value .Cells(newrow, 15) = txtTotal.Value .Resize(.Rows.Count + 1).Name = "rADOR" End With GoTo end01 And repeat that for each of 89 vendors. When I compile it, I get the Procedure Too Large error. Help sez "When compiled, the code for a procedure can't exceed 64K. This error has the following cause and solution: Code for this procedure exceeds 64K when compiled. Break this, and any other large procedures, into two or more smaller procedures." Any ideas on how to do this and get around the size limitation? I've thought of eliminating the ranges, and of making all the ..cells(newrow, x) instructions in a separate label, but then I get an Unqualified reference error. Ideas? The cmdADD_Click is fired from a single command button. |
All times are GMT +1. The time now is 11:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com