Compile Error Procedure Too Large When Trying To Run Macro
May 29, 2013
I am running into a Procedure too large error when running my macro. My macro is designed to replace a cell value with another cell value in a list. When the cell value is replaced a vlookup brings in new data to my workboook. Then the macro refreshes all the pivot tables and saves the workbork. I want my macro to repeat the above 60 times, so I have copied the code 60 times and changed the ActiveCell.FormulaR1C1 to point to the next value in my list. (Is there another way to select the next value from the list without changing the Row and Column number?) This is where my macro fails.
Below is my code.
VB:
Sheets("Data").Select
Range("B2").Select
[COLOR=#FF0000] ActiveCell.FormulaR1C1 = "=RC[25]" ' AGC GRP_ID[/COLOR]
Sheets("Ship pivot and cum triangle").Select
ActiveSheet.PivotTables("PivotTable4").RefreshTable
Application.DisplayAlerts = False
[Code] .....
View 9 Replies
ADVERTISEMENT
Jun 18, 2008
I am new to visual basic editor and I have a new position which right now has me copying and pasting a macro to hide all rows that contain zeros. I have attached a piece of it. There are 1,024 rows total and I got to row 479 and I get the error message.
It is telling me to "break up" the procedure, but I have no idea what that means or how to do it. There has got to be an easier way to do this.
Sub HideZeroCells()
If Range("g18") = "0" Then
Rows("18:18").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g22") = "0" Then
Rows("22:22").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g23") = "0" Then
Rows("23:23").Select.......................
View 9 Replies
View Related
May 20, 2009
I am looking for a more efficient way to write a macro (a sample from the macro is below). This is just the first part of the macro. I need to repeat these same steps (seen for row 5 below) for rows 5 to 50. My script worked until I hit row 35 and then I got the "compile error.." message. There must be a way to use "loop" to write this more efficiently, no?
View 14 Replies
View Related
Aug 8, 2012
The following code expands a cell width to size 42 when it is clicked on, then minimises it to size 9.29 when clicked elsewhere. Just what I need.
For instance, this would work for column 4.
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(4).ColumnWidth = IIf(Target.Column = 4, 42, 9.29)
End Sub
However, I need this to cover the first 1000 columns. More if possible. I can easily copy this 1000 times, changing the column number to then look like this (First 10 shown for example)
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(4).ColumnWidth = IIf(Target.Column = 4, 42, 9.29)
Columns(5).ColumnWidth = IIf(Target.Column = 5, 42, 9.29)
Columns(6).ColumnWidth = IIf(Target.Column = 6, 42, 9.29)
[Code] .....
The problem is if I do this 1000 times i get a Compile error that the procedure is too large. Is it possible to make the code more compact to basically cover all columns from 4 onward?
View 6 Replies
View Related
Mar 16, 2008
Auto Merged Post Until 24 Hrs Passes; Auto Merged Post Until 24 Hrs Passes; Auto Merged Post Until 24 Hrs Passes;
View 9 Replies
View Related
Jan 27, 2009
I keep getting this error:
"Compile Error: Invalid outside procedure"
What I am doing is having a macro execute every time my Pivot table is refreshed, the macro invokes a format change.
View 3 Replies
View Related
Nov 21, 2011
Compile Error: Invalid Outsid Procedure
I have Compile error coming up when I try to run Sub DeleteRecord on Sheet 2. It brings me to the VBE with sheet 1 code brought up and the Compile Error showing, with the xlup portion of the code highlighted. I okay the error and it highlights the Sub DeleteRecord on Sheet 2 with a yellow arrow.
Sheet 1 code as follows:
Dim y As Long
y = Range("A65536").End(xlUp).Row
Range("A1").AutoFill Destination:=Range("A1:A" & y), Type:=xlFillDefault
Range("J1").AutoFill Destination:=Range("J1:J" & y), Type:=xlFillDefault
Range("K1").AutoFill Destination:=Range("K1:K" & y), Type:=xlFillDefault
Range("L1").AutoFill Destination:=Range("L1:L" & y), Type:=xlFillDefault
Range("M1").AutoFill Destination:=Range("M1:M" & y), Type:=xlFillDefault
Range("N1").AutoFill Destination:=Range("N1:N" & y), Type:=xlFillDefault
Range("O1").AutoFill Destination:=Range("O1:O" & y), Type:=xlFillDefault
Range("P1").AutoFill Destination:=Range("P1:P" & y), Type:=xlFillDefault
Sheet 2 code at follows:
Sub DeleteRecord()
'
' DeleteRecord Macro
'
'
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveCell.Offset(4, 3).Range("A1:H2").Select
[code]....
View 3 Replies
View Related
Jan 16, 2009
I'm making changes to row A, but am getting an invalid outside procedure, not sure why. Don't even know what it means. It's not even in the same worksheet....
View 9 Replies
View Related
Aug 8, 2014
I have a VBA shell script that worked just fine on another computer, but now it won't run.
The code that won't execute is:
[Code] ........
View 12 Replies
View Related
Aug 1, 2014
I have 30 different cases working well in one module so far. Now I need to add some more cases, but the system doesn't allow me to start defining any new cases under Select - Case, it shows: compile error: procedure too large.
I'm thinking maybe there's a way to combine multiple cases like Case 30, 31, 32 in one statement, and use if, else to define each one?? So the coding won't return the "Procedure Too Large" error? see my original coding as below:
counter = 0
colkey = 0
For Each cont In PriceForm.HaveFrame.Controls
If TypeName(cont) = "CheckBox" Then
counter = counter + 1
check = cont.Value
If check = True Then
[code].....
View 9 Replies
View Related
Mar 10, 2014
The code I have (without splitting) is in the attached workbook (sheet1).
When you enter something into Column H, the value of =NOW() should appear in Column K.
The original code only went up to row 212 and it works fine. Now I need it to do the same up to row 411 but get the "Procedure too large" error.
I've tried some form of:
[Code] ....
but doesn't seem to work for me.
Attached File: OLE EXAMPLE.zip
View 10 Replies
View Related
Oct 15, 2008
I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working.
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim lng As Long
Application.EnableEvents = False
Select Case Target.Address
Case "$E$3"
If Target.Value = "Yes" Then
SelectNumber:
lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)
If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber
Range("G3") = lng
Else: Range("G3") = 0
.................................................(above statements repeat more than 300 times for different cells)..............................
Application.EnableEvents = True
End Sub
View 15 Replies
View Related
Apr 26, 2006
I have some code that, although works fine in Excel 2003, does not in Excel 1997. I receive this error when I try running it:
COMPILE ERROR:
NAMED ARGUMENT NOT FOUND
Sub HPVAL()
Dim r As Range, myStr As String
myStr = "HP"
Set r = Cells. Find(What:=myStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not r Is Nothing Then
r = r.Value
While Not r Is Nothing
Set r = Cells.FindNext(r)
If Not r Is Nothing Then
r = r.Value
End If
Wend
End If
End Sub
It looks like Excel is getting hung up on the "SearchFormat:=" portion of the code.
View 2 Replies
View Related
May 17, 2014
VB:
Selection.FormulaArray = _
"=IF(RC[-7]=""Weekly"",RC[-1],IF((ParentCode=RC[-10])*(ClassType=""Active""),IF((EndDate=EOMONTH(EndDate,0))*(RC[-7]
={""Monthly"",""Quarterly""}),RC[-1],0),IF(RC[-7]=""Daily"",IF(SUMPRODUCT(((ParentCode=RC[-10])*(FundType=""C"")*
(ClassType=""Inactive Class"")*(TermDate<>"""")*(TermDate>=StartDate)*(TermDate<=EndDate))+((ParentCode=RC[-10])*
[Code] .....
I recorded macro for this formula its giving me syntax error i did " _" after the break but its still not working..
View 4 Replies
View Related
Feb 1, 2010
"Compile error - Syntax Error"
and the following line highlighted in yellow
"Sub CreateWorkbooks()"
It worked on Excel 2000 but not now and dont work either in Excel 2007.
Heres the complete code .....
View 9 Replies
View Related
Feb 18, 2008
I'm adding a new macro and getting the error message Compile Error:Invalid outside procedure. I'm using the following code
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet
Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function
View 9 Replies
View Related
Dec 3, 2013
Basically I want the macro to look through a column of values and if any cell in that column has the value 'LOCK' the macro would hardcode 'n' into the adjacent cell. Additionally, because the column in which the LOCK cells would appear is part of a vlookup, it returns #N/A which I would like my macro to ignore and then continue to evaluate the next cell all the way to the end of the column.
The code is the following:
Sub Hard_Code_Cells()
On Error Resume Next
lastrow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[Code] .......
I've been receiving the error message 400 when I trigger the macro and I'm not sure how to correct my code to resolve the issue.
View 8 Replies
View Related
Jul 30, 2014
I created three macros to work with reporting for my company.
One sorts a field, another deletes unwanted columns, and the third double checks columns for answers--I work for a telecom polling firm.
It was working last week on my computer, and it still works on another computer at the office. However, I keep getting an error message when I try to run it.
The message is "Compile Error: Expected Function or Variable". The code is below, and bolded where it breaks down.
--selection.Autofilter
[Code] .......
What could be the rationale for the sudden breakdown? I was testing it on a file that I have tested with before without issue. I also tried a second file, and even restarted my computer. I am currently trying to write a fourth macro for counting responses and giving percentages, so while I don't need to have the perfectly cleaned data to do so it would be nice to have.
View 4 Replies
View Related
Feb 11, 2010
I am creating some code to unprotect a workbook, unhide columns, delete a series of cells, rehide columns and then reprotect the workbook:
View 14 Replies
View Related
Jun 21, 2009
i'm trying to run a macro I recorded then amended to define a final row, but when I step in to the macro it says Compile Error - variable not defined. Please can someone help, surely I am defining it so I don't know why it doesn't work? Is there anything wrong with my FinalRow line in this macro?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/06/2009 by Richard Shaffer
'
'
Sheets("Core Finance").Select
FinalRow = Cells(65536, 1).End(xlUp).Row
Range("A3:O" & FinalRow).Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub
View 9 Replies
View Related
Apr 1, 2014
I'm trying to use the macro below (twice but with different variables) but everytime I get the error "Compile error: Constant expression required".
[Code]....
View 3 Replies
View Related
Jun 18, 2009
I'm trying to use MATCH to identify the Column number that contains "DL_Error", then I'll use this Column # to select a cell (found Column # & "34"), but I get the error shown in the title of this post.
Here's my current
Sub Macro15()
' Macro recorded 6/18/2009 by me
Range(Match("DL_ERROR", "A31:CW31", 0) & "34").Select
End Sub
View 9 Replies
View Related
Aug 24, 2007
I have recorded a simple macro ( copying a cell, and then pasting the formula into various others), and I get the following error
Compile Error: Argument Not Optional
I have highlighted where the error first happens
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/08/2007 by Michael Traynor
'
'
Range("K7:K8").Select
Selection.Copy
Range("K167:AJ168").Select
Range("AJ167").Activate
Range("K167:AJ168,K175:AJ176").Select
Range("AJ175").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184").Select
Range("AJ183").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184,K191:AJ192").Select
As I've said I didn't write this, it was recorded from Excel.
View 7 Replies
View Related
Jun 13, 2014
I'm trying to use the "clear contents macro" for merge cell, but I keep receiving this "compile error : Expected End Sub" error.
FYI, I have named my merge cells to "myMergedCells"
[Code] .....
View 3 Replies
View Related
Feb 7, 2007
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
View 6 Replies
View Related
May 4, 2008
Can some see why this Code would fail intermittently?
This gets executed after a Commandbutton is selected.
The Error Message is attached.
View 14 Replies
View Related
Sep 14, 2009
I am using the following code to put a combo box in my userform. When I try to run it I get the following Compile Error: For without next. What does it mean and how do I fix it?
View 9 Replies
View Related
May 2, 2008
just make some files to make my work easier. Few too many watching the game last night, and I think im lost here. Am I attempting to do too much here ? I ran the first For statement Fine, but I cant get the second to work. I dont understand where my next statement should be entered.
View 4 Replies
View Related
Feb 13, 2007
In an error handling procedure, I want to be able to capture the name of the Module and Sub procedure (macro) that the error has occurred in.
Is there any way to do this, outside of storing the name of the Sub somehow in a variable?
The Err.Source method seems to only contain very broad information, such as "VBA Project" when an error is encountered.
View 9 Replies
View Related
Jul 20, 2009
VBA but familiar with other programming languages.
I have looked through previous posts with the same problem and I see that most people forget to add the "endIf" before looping. From what I can tell, I have ended all of my "If" statements.
The goal of this macro is to take temporary data and finding a match in other sheets and copying from "Temp" and pasting into the other sheets (possibly in the first blank cell, depending on the case) then deleting the row and moving on to the next row and repeating the process until "Temp" is empty.
View 9 Replies
View Related
