VBA Select Case Error / Procedure Too Large

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


ADVERTISEMENT

Case Select And Loop Procedure

Jun 10, 2007

I am learning about Case Select-
I want to loop through every cell in col. V the range of
i = 6 to LRow
and find any value that equals 13, if it equals 13 then the value in (i,"W") will be "True"

View 9 Replies View Related

Error Message (Compile Error, Procedure Too Large)

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

Compile Error: Procedure Too Large

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

Macro Error: Procedure Too Large

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

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 View Related

Expand Cell Width - Compile Error Procedure Too Large

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

Select Case Error "Case Without Select Case"

Jul 20, 2006

For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....

View 9 Replies View Related

VBA Implementing Select Case Statement - Compile Error

Apr 25, 2013

I'm having problems implementing a Select Case statement. Keep getting case without select case at Case condition2.

Code:
Select Case Condition
Case condition1
If Cells(rsRow, rsCol).Value = "" Then
Cells(rsRow, rsCol) = TextBox2.Value

[Code] .......

View 3 Replies View Related

Check For Blank / Empty / Error Cells In Select Case

Aug 3, 2012

I am using a Uderform in order to check if some cells in several columns are higher or lower than values I set in my textboxes.

However, I think I am having a mistake in my code (it runs but doesnt return the correct informations) as I have blank cells in certain columns.

Concretely I am giving 1 point if the criteria is valid (if for example the value in the cell is <= 1) but some of these criteria are 1 when they should be 0.

As I said it runs but I need to add a fix for empty cells / blank cells and if possible N/A error cells in this so that the code gives 0 to the criteria and moves to the next column (next select case)..

View 2 Replies View Related

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 View Related

Select Case, Case Else Copy From Above Cell

Jun 3, 2009

I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.

Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"

Rather then returning "N/A", how could I reference the cell above and just copy it instead?

View 9 Replies View Related

Select Case With Between Value?

May 5, 2014

I want to fill up specific text to cell according to values in columns before. I wrote this, but it doesn't work:

Sub test()
Dim Lastrow As Long
Lastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).row - 1
Dim i As Long

[Code].....

View 2 Replies View Related

Select Case Or If

Oct 24, 2008

I have the following:

Column A---------Column G
EACH-------------(Formula = (B1*C1)*.4536
CASE-------------(Formula = (B1*C1)*.4536
SHTB-------------(Formula = (B1*C1*D1)
SHTD-------------(Formula = (B1*C1*D1)
MFG--------------(Formula = B1*C1)
NO---------------(Formula = B1*C1)

I need a macro that when the worksheet is open if the text in Column A is as presented in the sample then the Mathmatical calculations will be performed in Column G. Currently I use a Do While Loop which works (takes a long time), but I know there must be a faster more efficient way.

View 9 Replies View Related

Select Case Instead Of Using IF

Dec 19, 2008

How to I change this code to use 'select case' instead of using IF? I have about 20 more if's and I though select case may be easier . .


'20
If ComboBox8.Value = "20" Then
MyPlace.Formula = "=IF(OR(RIGHT($AU16,2)=""ge"",COUNTIF(X16:aq16,""X"")=20),""Reject"",""Accept"")"
End If
' '19
If ComboBox8.Value = "19" Then
MyPlace.Formula = "=IF(OR(RIGHT($AU16,2)=""ge"",COUNTIF(X16:ap16,""X"")=19),""Reject"",""Accept"")"
End If
' '18
If ComboBox8.Value = "18" Then
MyPlace.Formula = "=IF(OR(RIGHT($AU16,2)=""ge"",COUNTIF(X16:ao16,""X"")=18),""Reject"",""Accept"")"
End If

View 9 Replies View Related

Compile Error / Invalid Outside Procedure

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

VBA - Error Handling: Determining The Sub Procedure

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

Macro Error: Invalid Outside Procedure

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

Using Userform For Select Case?

Feb 12, 2014

I have a report and users will need to key in password while opening. Currently, i am using inputbox and it works exactly like what i expected. However, the only downside is that i cannot mask the password.

I am aware that I have to use userform to do so. How to replace the input box with userform and still deliver the same result.

I know how to create a user from with title, text box, OK and Cancel buttons, and that's it. How to put it in my current code.

Below are the codes that I currently have. There are 4 possible outputs.

[Code].....

View 14 Replies View Related

Select Case Condition Not Being Met

Oct 15, 2008

Select Case Condition Not Being Met

View 4 Replies View Related

Select Case With Msgbox Pop Up

Oct 16, 2008

I don't see what I am doing wrong in this code using "Select Case". When activated and the correct string is entered I should get a msgbox pop up.

View 8 Replies View Related

Select Case Construction

Feb 2, 2009

I seem to be unable to crack the bug in my macro... I want to define 2 variables before launching the next macro with the defined variables.

When I run this macro - the debugger points all the time to the following line: ...

View 4 Replies View Related

Is There A Difference Between Select Case And If..Then

Apr 17, 2009

Is there any difference between these 2 codes:

View 12 Replies View Related

Case Select With A Timer

May 5, 2009

If i have a case select say like this

View 4 Replies View Related

Simplifing Select Case

May 8, 2009

I was wondering if any one can help me with simplifying my Select Case, i have provided 3 examples , 101 , 102 and 103. I will be going all the way from 101 to 199 and I dont want to write every single one. and note that the range values change for each one as well.

View 14 Replies View Related

Select Case Using A Range

Jul 31, 2009

I'm creating a small spreadsheet for client data in Excel and I want it formatted a certain way, I did consider data validation but it proved to just be annoying.

I've been working on some VBA code to automatically change whatever text is typed into a cell to the correct case (ucase, lcase or proper) and while I can get it working for a single range of cells getting it to work for more is proving difficult.

View 6 Replies View Related

Long Select Case With If/Then

Sep 29, 2009

If there is a different way, like to select the value in the selected range and cross reference it against a list. i'm not sure... trying to learn and be efficient.

View 3 Replies View Related

Loop Select Case

Sep 24, 2009

How do I loop a select case so that it ends at a specific cell.

View 14 Replies View Related

Select Case Combine With Like

Feb 9, 2012

I have a multiple condition and i try using Select case rather than If else. However i try to combine Select case with Like operator but it's not running as it should.

Code:

Sub SetHeader (FuncName as String)
Dim xSheet as Worksheet

Select Case FuncName
Case Is = "_EXCRT"
Set xSheet = Activeworkbooks.Sheets(FuncName)

[Code] .........

This code running good with first case but it will not goto second case even the FuncName string are : "_MB51_00" or "_MB51_01".

View 7 Replies View Related

Select Case On Array?

Aug 29, 2012

Can I do a select case on an array?

Something like?

Code:
Dim Array As Variant
Dim Range as Range
Array = Array(Worksheets("sheet1"), ("sheet2"), ("sheet3"))
Select Case Array

[Code] ........

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved