Compile Error (Constant Expression Required) When Using Macro Twice

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".


View 3 Replies


Error / Constant Expression Required

Jan 22, 2013

I am getting 'Constant Expression Required' and this line of code highlighted.

Const sFile As String = ThisWorkbook.Path & "Survey.xlsm"

View 5 Replies View Related

Compile Error: Object Required ()

Dec 4, 2007

I found this code in the Mr. Excel archives. It is designed to add a sequential number to a cell if the cell next to it contains text and then stop as soon as it encounters a blank. Problem is that I keep getting a Compile Error: Object Required on the Set nos line. I tried using a qualifier and also expirimented with CreatObject but still received the error.

Sub AddNos()
Set nos = Range("B1", Range("B1").End(xlDown)).Offset(0,-1)
nos.Resize(1,1).Value = 1
nos.Resize(1,1).AutoFill nos, xlFillSeries
nos.NumberFormat = "General""."""
End Sub

View 4 Replies View Related

Vba Macro Error: Compile Error Named Argument Not Found

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:


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
End If
End Sub

It looks like Excel is getting hung up on the "SearchFormat:=" portion of the code.

View 2 Replies View Related

Compile Error Syntax Error In Macro

May 17, 2014

Selection.FormulaArray = _
(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

Macro: Compile Error - Syntax Error

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

VBA Macro Throwing Compile Error

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

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.

[COLOR=#FF0000] ActiveCell.FormulaR1C1 = "=RC[25]" ' AGC GRP_ID[/COLOR]
Sheets("Ship pivot and cum triangle").Select
Application.DisplayAlerts = False

[Code] .....

View 9 Replies View Related

Macro No Longer Works - Getting Compile Error?

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.


[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

Delete Cells Macro Compile Error

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

Macro - Compile Error When Define FinalRow

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
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

View 9 Replies View Related

Object Required Error When Running Macro

Apr 28, 2010

I am trying to run the following macro to copy a data range(A1:HX1) range from one sheet(sheet 6) and past it into the next available blank row in another sheet called New_Overall_Input_File but get the following error when I try and run it......."Object Required"?

New_Overall_Input_File.Range("D" & Rows.Count).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

View 9 Replies View Related

Compile Error: Sub Or Function Not Defined - 3-line Macro

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

Macro Recorder :: Compile Error: Argument Not Optional

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


As I've said I didn't write this, it was recorded from Excel.

View 7 Replies View Related

Clear Contents Macro For Merge Cell (Compile Error - Expected End Sub)

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

Compile Error: Invalid Outside Procedure (execute Macro On Pivot Refresh)

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

Time Error '16' Expression Too Complex Run

Dec 7, 2009

Using Excel 2007 the below code causes Run Time Error '16', Expression too complex.

It works fine in Excel 97, 2000 and 2003.
why this would now cause an error?
My search on google only refers to this error in relation to charts.

View 6 Replies View Related

VLOOKUP Gives Error With False Expression

Mar 1, 2010

I have a formula that has been working and it looks like this

=VLOOKUP(A13;'IFS export'!$A$1:$F$19000;4;FALSE)

Now I want to use the same formula in an other workbook and it gives an error


When I type this formula I get the window that says
"This formula contains an error.
*For information about fixing common formula problems, press help.

And if I go and try to change the first formula that has been work it gives me the same error.

View 9 Replies View Related

Macro That Prompts To Chose Files And Copies Them Based On Criteria - Error 424 / Object Required

Mar 6, 2013

I have written a macro that prompts the user to chose some files and if they meet certain criteria, it copies them to a specified folder.However, I have run into this 424 error "Object required".

Dim FileNames As Variant
Dim I As Integer
Dim fso As Variant
Dim Data As String
ChDrive "G:"
ChDir "G:TEST"


The error is in this line:

If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then

View 2 Replies View Related

Copy And Paste Special Values But Getting Error 'Compile Error - Expected Function Or Variable'

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()
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

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

Constant Variables - Declare Name In Separate UserForm As Constant

May 8, 2012

I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.

Code for Form2: Const myName As String = Form1.txtName.Value

View 5 Replies View Related

Compile Error ...

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

Compile Error: For Without Next ..

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

Compile Error: For Without Next

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

Compile Error: Loop Without Do

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

Unable To Get Rid Of Compile Error In VBA

May 11, 2014

I am new to VBA. I have been getting compile error on the below code:

Public Sub hourCalculation()
Dim rcount As Integer
Dim rindex As Integer
Dim logintime As Date
Dim logoutime As Date

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

View 9 Replies View Related

Compile Error - Annying Pop Up

Jun 11, 2008

Every time I wrote something wrong I stupid and annoying pop windows appears and I have to press ok different times and be quick to correct the problem, otherwise it appears again.

View 9 Replies View Related

Error '424' Object Required

Jul 28, 2007

Sub Macro2()
' Macro2 Macro
' Macro recorded 7/28/2007 by i8ig

If Target.Column = 1 Then
If Target.Value = "Med" Then
Rows(Target.Row).Interior.ColorIndex = 4
ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
If Target.Value = "Tasc" Then
Rows("4:4").Interior.ColorIndex = 44
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
If Target.Range = "NBAR" Then
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
End If
End If
End If
End If
End Sub
I continue to receive an error '424' object required and I cant find it

View 9 Replies View Related

Error Object Required

Nov 3, 2009

I have two worksheets one called "invoice" and one called "tenants"
"Invoice" has a userform where I want to enter a tenancy number and then lookup the tenants name and address from "tenants"

I have put the following macro in

Sub lookup()
Dim res As Variant
res = Application.VLookup("A1", Tenants.xlsx("Sheet1"), "$A:$H", 2, False)
If IsError(res) Then
MsgBox "not found"
MsgBox "found at pos: " & res
End If
End Sub

but get the following error - "object required"

View 9 Replies View Related

Copyrights 2005-15, All rights reserved