Procedure too large?

H

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
I have a macro which is filtering a sheet based on a number of criteria specified by a user via drop downs. The code was working fine until I just added a small amount of code today, and now it says "Procedure too Large" and highlights "End Sub" in my code. Here is a portion of my old code:

Code: 
If Period1 = "All Periods" And Period2 = "All Periods" And DurObs = "All Durations of Observation" And DurPos = "All Durations in Position" Then
For RowNums = 2 To DataRangeNum
    With Sheets("Data_Import_Infopath")
        If .Range("AE" & RowNums).Value <> "TEST PATTERN" And .Range("AA" & RowNums).Value = 1 Then
            Sheets("Filtered_Data").Range("A" & FilteredDataRow).Value = .Range("AE" & RowNums).Value & " " & .Range("AC" & RowNums).Value & " " & .Range("AB" & RowNums).Value
            Sheets("Filtered_Data").Range("B" & FilteredDataRow).Value = .Range("AS" & RowNums).Value
            Sheets("Filtered_Data").Range("C" & FilteredDataRow).Value = .Range("AU" & RowNums).Value
            Sheets("Filtered_Data").Range("D" & FilteredDataRow).Value = .Range("AW" & RowNums).Value
            Sheets("Filtered_Data").Range("E" & FilteredDataRow).Value = .Range("AY" & RowNums).Value
            Sheets("Filtered_Data").Range("F" & FilteredDataRow).Value = .Range("BA" & RowNums).Value
            Sheets("Filtered_Data").Range("G" & FilteredDataRow).Value = .Range("BC" & RowNums).Value
            Sheets("Filtered_Data").Range("H" & FilteredDataRow).Value = .Range("BE" & RowNums).Value
            Sheets("Filtered_Data").Range("I" & FilteredDataRow).Value = .Range("BG" & RowNums).Value
            Sheets("Filtered_Data").Range("J" & FilteredDataRow).Value = .Range("BI" & RowNums).Value
            Sheets("Filtered_Data").Range("K" & FilteredDataRow).Value = .Range("BK" & RowNums).Value
            Sheets("Filtered_Data").Range("L" & FilteredDataRow).Value = .Range("BM" & RowNums).Value
            Sheets("Filtered_Data").Range("M" & FilteredDataRow).Value = .Range("BO" & RowNums).Value
            Sheets("Filtered_Data").Range("P" & FilteredDataRow).Value = .Range("AE" & RowNums).Value & " " & .Range("AC" & RowNums).Value & " " & .Range("AB" & RowNums).Value
            Sheets("Filtered_Data").Range("Q" & FilteredDataRow).Value = .Range("AR" & RowNums).Value
            Sheets("Filtered_Data").Range("R" & FilteredDataRow).Value = .Range("AT" & RowNums).Value
            Sheets("Filtered_Data").Range("S" & FilteredDataRow).Value = .Range("AV" & RowNums).Value
            Sheets("Filtered_Data").Range("T" & FilteredDataRow).Value = .Range("AX" & RowNums).Value
            Sheets("Filtered_Data").Range("U" & FilteredDataRow).Value = .Range("AZ" & RowNums).Value
            Sheets("Filtered_Data").Range("V" & FilteredDataRow).Value = .Range("BB" & RowNums).Value
            Sheets("Filtered_Data").Range("W" & FilteredDataRow).Value = .Range("BD" & RowNums).Value
            Sheets("Filtered_Data").Range("X" & FilteredDataRow).Value = .Range("BF" & RowNums).Value
            Sheets("Filtered_Data").Range("Y" & FilteredDataRow).Value = .Range("BH" & RowNums).Value
            Sheets("Filtered_Data").Range("Z" & FilteredDataRow).Value = .Range("BJ" & RowNums).Value
            Sheets("Filtered_Data").Range("AA" & FilteredDataRow).Value = .Range("BL" & RowNums).Value
            Sheets("Filtered_Data").Range("AB" & FilteredDataRow).Value = .Range("BN" & RowNums).Value
            FilteredDataRow = FilteredDataRow + 1
            End If
    End With
Next RowNums

My code is basically just this repeated 15 more times, with a slightly different elseif statement at the beginning of the code. This is what I added right before the FilteredDataRow = FilteredDataRow + 1 line:

Code: 
Sheets("Filtered_Data").Range("AD" & FilteredDataRow).Value = .Range("AE" & RowNums).Value
            Sheets("Filtered_Data").Range("AE" & FilteredDataRow).Value = .Range("AC" & RowNums).Value & " " & .Range("AB" & RowNums).Value


I also added:

Code: 
Application.ScreenUpdating = False

at the beginning of the code and set it back to true at the end of my code.

Does anyone have any clue why it is telling me that the procedure is too large????

Any help would be greatly appreciated.

Thanks!

Hank
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
OK. The problem is not with the Application.ScreenUpdating lines. I took out the additional 2 lines I added to each if statement and the problem went away. Why would it not allow me to add two more lines to each if statement, though? This is very confusing to me?
 
Upvote 0
Strange -- it doesn't seem that your code x 15 is too large. Nevetheless, you should put the For-Next loop you've posted into its own Sub which takes DataRangeNum and FilteredDataRow as parameters. FilteredDataRow will be updated after it's done, because parameters are passed by reference. Then write 14 more similar procedures which are similar, as you said. Finally, in the main program, just have a 15-way If-ElseIf-EndIf that just calls the correct procedure based on the condition.
 
Upvote 0
Hmmm ok I made those changes. This is my New Sub which I have in Module1:

Code: 
Sub FilterData1(DataRangeNum As Long, FilteredDataRow As Long, RowNums As Long)
For RowNums = 2 To DataRangeNum
    With Sheets("Data_Import_Infopath")
        If .Range("AE" & RowNums).Value <> "TEST PATTERN" And .Range("AA" & RowNums).Value = 1 Then
            Sheets("Filtered_Data").Range("A" & FilteredDataRow).Value = .Range("AE" & RowNums).Value & " " & .Range("AC" & RowNums).Value & " " & .Range("AB" & RowNums).Value
            Sheets("Filtered_Data").Range("B" & FilteredDataRow).Value = .Range("AS" & RowNums).Value
            Sheets("Filtered_Data").Range("C" & FilteredDataRow).Value = .Range("AU" & RowNums).Value
            Sheets("Filtered_Data").Range("D" & FilteredDataRow).Value = .Range("AW" & RowNums).Value
            Sheets("Filtered_Data").Range("E" & FilteredDataRow).Value = .Range("AY" & RowNums).Value
            Sheets("Filtered_Data").Range("F" & FilteredDataRow).Value = .Range("BA" & RowNums).Value
            Sheets("Filtered_Data").Range("G" & FilteredDataRow).Value = .Range("BC" & RowNums).Value
            Sheets("Filtered_Data").Range("H" & FilteredDataRow).Value = .Range("BE" & RowNums).Value
            Sheets("Filtered_Data").Range("I" & FilteredDataRow).Value = .Range("BG" & RowNums).Value
            Sheets("Filtered_Data").Range("J" & FilteredDataRow).Value = .Range("BI" & RowNums).Value
            Sheets("Filtered_Data").Range("K" & FilteredDataRow).Value = .Range("BK" & RowNums).Value
            Sheets("Filtered_Data").Range("L" & FilteredDataRow).Value = .Range("BM" & RowNums).Value
            Sheets("Filtered_Data").Range("M" & FilteredDataRow).Value = .Range("BO" & RowNums).Value
            Sheets("Filtered_Data").Range("P" & FilteredDataRow).Value = .Range("AE" & RowNums).Value & " " & .Range("AC" & RowNums).Value & " " & .Range("AB" & RowNums).Value
            Sheets("Filtered_Data").Range("Q" & FilteredDataRow).Value = .Range("AR" & RowNums).Value
            Sheets("Filtered_Data").Range("R" & FilteredDataRow).Value = .Range("AT" & RowNums).Value
            Sheets("Filtered_Data").Range("S" & FilteredDataRow).Value = .Range("AV" & RowNums).Value
            Sheets("Filtered_Data").Range("T" & FilteredDataRow).Value = .Range("AX" & RowNums).Value
            Sheets("Filtered_Data").Range("U" & FilteredDataRow).Value = .Range("AZ" & RowNums).Value
            Sheets("Filtered_Data").Range("V" & FilteredDataRow).Value = .Range("BB" & RowNums).Value
            Sheets("Filtered_Data").Range("W" & FilteredDataRow).Value = .Range("BD" & RowNums).Value
            Sheets("Filtered_Data").Range("X" & FilteredDataRow).Value = .Range("BF" & RowNums).Value
            Sheets("Filtered_Data").Range("Y" & FilteredDataRow).Value = .Range("BH" & RowNums).Value
            Sheets("Filtered_Data").Range("Z" & FilteredDataRow).Value = .Range("BJ" & RowNums).Value
            Sheets("Filtered_Data").Range("AA" & FilteredDataRow).Value = .Range("BL" & RowNums).Value
            Sheets("Filtered_Data").Range("AB" & FilteredDataRow).Value = .Range("BN" & RowNums).Value
         
            FilteredDataRow = FilteredDataRow + 1
            End If
    End With
Next RowNums
End Sub

I have one of those for each of the if-elseif statements in my worksheet code. Here is how I am calling each of the subs in the module from the worksheet code:

Code: 
Call FilterData1(DataRangeNum, FilteredDataRow, RowNums)

The code is, for some reason, not working. I have this code at the end of my procedure:

Code: 
If DataRangeNum = 17 Then
MsgBox "There are no survey results that satisfy your given criteria."

Because I have 17 fixed rows on my sheet that are never cleared, and DataRangeNum is set to equal to however many rows are filled out on the sheet. When I run this code, regardless of what drop downs are selected, it is telling me "There are no survey results that satisfy your given criteria." But I know that there are survey results that satisfy my criteria.


When I step through it, it gets to the correct sub in the module as it should, but for some reason it never says that this statement is true:

Code: 
If .Range("AE" & RowNums).Value <> "TEST PATTERN" And .Range("AA" & RowNums).Value = 1 Then


Any help you can give me would be greatly appreciated, as I've been looking at this for quite a while and can't seem to figure out what is wrong. Thanks a lot for the looks and the help.

Hank
 
Upvote 0
First, you don't have to have RowNums as a parameter to the 15 subs -- just declare it as a long in each sub. As to why your If's aren't working, it must be that RowNums is set wrong or it's looking at the wrong sheet. Put a few Debug.Print statements before each If (now that they'll fit!), and see what it thinks is going on:
Debug.Print "I'm now in Sub (name the sub)"

With .Range("AE" & RowNums)
Debug.Print "Worksheet is " & .Parent.Name
Debug.Print "Address is " & .Address
Debug.Print "Value is " & .Value
​
End With
​
Make sure you copy exactly the .Range expression the If is asking about: AE, AD, whatever. To see the results, hit Ctrl-G in the VBE to view the Immediate window.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Fixing the macro for text splitting (rows being skipped)
Replies
6
Views
463
Georgiboy
Georgiboy
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
885
Gajendran Yadhav
G
D
  • Solved
VBA issue copying data to the same sheet
Replies
4
Views
485
Diagoro
D
O
  • Question Question
Change MS Word Doc Properties via MS Excel VBA Macro
Replies
3
Views
685
NdNoviceHlp
NdNoviceHlp
B
  • Question Question
Error message when copying and renaming a worksheet
2
Replies
13
Views
340
smozgur
smozgur

Forum statistics

Threads
1,224,598
Messages
6,179,818
Members
452,946
Latest member
JoseDavid

Share this page

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
Back
Top