Macro - Procedure Too Large
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
ADVERTISEMENT
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
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
View Related
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
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
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
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
Sep 2, 2009
I have the following macros,but want them to run in sequence but i don't know how.
I plus won't another macro to save the archive as .txt
1. I use the following macro to open .txts so the cells go where i want to
Sub NewTextImport()
myfile = Application. GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText Filename:=myfile _
, Origin:=1253, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array( _
Array(0, 2), Array(17, 2), Array(34, 2), Array(56, 2), Array(102, 1)), _
TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
End Sub
2.Remove the unwanted zeros from Column E.............
View 2 Replies
View Related
Feb 28, 2006
When i open the MS-excell and i go to see the VBeditor i can see the following in the project window:
- spacereport(space.xla)
-microsoft excel objects
sheet1
[Code].....
All the code in spacereport project, let me go to File-->Space and then run a report previously added. what i would like to do is, being in the book3 project, execute modules and forms from spacereport project.
View 6 Replies
View Related
Apr 11, 2003
VBA - I want to run a set of statements every hour on the hour for the lifetime of the workbook being open
View 4 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
May 29, 2008
I there a way to get a text of the user defined VBA function. Smth like Application.Function("MyFunc").Body.Text;
View 7 Replies
View Related
Jun 26, 2008
When I run the following code, if "If exists = True" then I want the routine to start again at the very beginning. Currently, 'as is' it re-starts at the beginning but enters a loop on itself; I understand why this is but it's not what I want but can't get round this! How do I make the "If exists = True" condition (if true) restart the routine - ie call AllNEWActions() without then going into a loop on itself? NB: I can't use "Exit Sub" (see it commented out) as I don't want to exit routine, just restart it.
Sub AllNEWActions()
ShowCalendar
GetDateFromCalendar
SheetAlreadyExists
If exists = True Then
boolRestart = True
AllNEWActions
' Exit Sub
Else
MsgBox (" Date selected/new sheet doesn't exist")
InsertNewSheet
End If
If boolRestart = False Then
ShowCalendar
GetDateFromCalendar
End If
MsgBox ("Do something ...)
End Sub
View 2 Replies
View Related
Jan 14, 2010
I have a VBA application in Excel.
In a certain time, when the code is executed in the
Private Sub CB_ValidaHoras_Click()
View 11 Replies
View Related
Mar 14, 2008
I have constructed a Userform with two button options - Run a Sub that I have already written and runs fine, the other to close the userform.
how to correctly assign the completed macro ( Name: "Run_Report") to the button click action. The code below is what I have, but it does not run:
' This is the run macro code
Private Sub CommandButton1_Click()
Run_Report
End Sub
' This is the quit UserForm code
Private Sub CommandButton2_Click()
Unload Me
End Sub
The quit command works, just not the run macro - do I need quote marks, parenthesis, or a .Run command or something?
View 5 Replies
View Related
May 15, 2008
I have a worksheet with 7 macros each run from a seperate button. I would like to tidy up the sheet by having a combo box containing a description of each macro and one button to run the macro currently shown in the box.
View 4 Replies
View Related
May 27, 2009
I am trying to accomplish is to display a dropdown or combobox with a list of choices. I want the backcolor to be shaded light green to match instructional text that appears in the cell above. That I have working with the selections appearing. Once a choice is made I want a separate procedure to run that will somehow know which choice was made.
I can't put the code within the module for this particular sheet as it is dynamically recreated each time the data is refreshed and the code will disappear. This may seem to be an odd practice but this is how 25+ workbooks are coded within this system for my employer's customer and it is a required practice.
View 9 Replies
View Related
Feb 26, 2008
I have a program that screen scrapes data fom a mainframe emulation program. There are many loops used in order to slow the program down checking for things to load before taking the information. I would like to build some kind of display showing the user what the program is doing and allow them to stop the program if they like. Right now while the program is running everything is frozen and unresponsive.
View 3 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
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Jan 6, 2010
Need the code to put into a command button which jumps from the cell selected, X number of cells to the right. Also will need the code to jump back again when clicking on a different command button.
View 5 Replies
View Related
Dec 6, 2006
I wondered if there is a possibility to make this
Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
View 6 Replies
View Related
May 25, 2007
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
View 2 Replies
View Related
Nov 23, 2009
DoneyOte on this but i have added to the range and im now getting an error that i can't seem to fix
View 12 Replies
View Related
Feb 9, 2012
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
View 3 Replies
View Related
Jul 23, 2013
I need to add something to my macro to delete a sheet if it doesn't have more than 40 rows of data. If it has more than that, it needs to do a whole bunch of stuff to it. I've already got the whole bunch of stuff written, I'm just not sure how to do the if function. I took a stab at it, but it wants the count to be an object. I am not very good at writing code.
Code:
Dim count As Integer
Set count = "COUNT('33M & 33RUL'!A:A)"
If waffle Is "
View 1 Replies
View Related
Mar 18, 2009
I've been looking around to find something like this for a while and I'm pretty new to VBA, so I haven't figured it out myself yet.
Basically, I need a macro that can take a 7-column range and replace the values in all cells containing 0 (but not 10, 20, etc) with a blank cell so that a count function in another column can function.
When I just select the range and use 'Find and Replace' to remove the 0s, it alters 10s, 20s, 30s, any number that even ends in zero instead of just the value of zero itself,
View 9 Replies
View Related
Jul 13, 2006
What i'm trying to do is to check a large number of cells, if "0" is written down in that cell it stays as it is and if the number is different from "0" i want to put a "1" instead.
View 5 Replies
View Related
Mar 3, 2014
I am trying to use excel tools to clean dirty data and compare the two cells. The information is there but tainted with additional information that is not relevant. I have tried to use Left/Right tools to capture alpha characters leading an address number with no real success. Also, when I get the data it seems to have some embedded breaks that I can't seem to get rid of that cause my tasks to error too.
View 4 Replies
View Related
Feb 18, 2008
I would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).
I don't want this to be a straight swap thought, I'd like it to contain the following formula:
=IF(Working!G8="a","a","")
The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.
ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc
View 9 Replies
View Related