Over 1 million tech questions and answers.

Solved: Excel: Run-time Error 1004 During Macro

Q: Solved: Excel: Run-time Error 1004 During Macro

I have attached a spreadsheet. The first tab (Setup) has buttons 1,2,3,4,5. The purpose of these buttons is to have different years investments. I have a tab (Sheet1) that is hidden in the spreadhseet. Each macro unhides, then changes about 3 formulas on the Investment Analysis Tool tab, simply changing the cell reference. Only it does't ' change. It errors out with the above error. I've never see anything like this happen! Thanks in advance for the help.

Preferred Solution: Solved: Excel: Run-time Error 1004 During Macro

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel: Run-time Error 1004 During Macro

Monty75, I am sorry but the spreadsheet that you posted does not have any buttons visible.
The Code is there and I have looked at and run it and the problem is that the Code is not on Sheets("Investment Analysis Tool").Select
This is a quirk of Excel, You need to put activesheet. in from of your "Range selection" like so
ActiveCell.FormulaR1C1 = "=Sheet1!R[31]C[-2]"

Read other 3 answers

Hi All,

I included the macro that was developed last week for this "AIR Log." However, the creator is on leave and I only know how to read and interpret...thus giving me an error that I cannot fix:
I would also like to do the following...

In Column O have a formula... =CONCATENATE(Lxx,Mxx,Nxx)
Hide columns L-N
Remove Table Style
Set font to Arial; size to 9; Make sure all text is Middle & Center Aligned.
Row 1 has a fill color: Blue, Accent 1, Darker 25%
Option Explicit

Sub oooFormatAIRLog()

Sheets(1).Name = "AIRLog"
Columns("P:Q").Delete Shift:=xlToLeft
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Risk/Issue/Action Item?]") _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Issue,Risk,Action Item", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Criticality]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, CustomOrder:="High,Medium,Low", _
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Due Date]&quo... Read more

A:Solved: Excel Macro: Run-time error '1004'

With the sheet and the charts it's a needle in a haystack.
The error messag is that you're adressing a range or sheet which is either misspelt or hidden or non existant.
You will have to go through the macro step by step.
Open the VBA editor, loop for the macro select it and press F8 so the code will be executed line by line and it will stop when the error occurs.
Check that line and see if what it addresses exists.
Trouble shooting takes time an patience.

Good luck

Read other 2 answers

I have a multisheet workbook in Excel 2000. On one of the sheets there are 2 drop down combo boxes which link to cells and produce a result of 0 or 1. Depending on the result 2 other cells are formatted. The code works fine until I try to save the workbook under a different name - then I get a message saying:
"Run-time error 1004 Select method of Range Class failed".
It then goes into debug and stops at the fourth line below. The only way out is to end the excel session. My workbook is not read only. Has anyone had a similar experience.

If Worksheets("Input").Range("TAXBASIS").Value = 1 And Worksheets("Input").Range("ACC_IND").Value = 0 Then
'sole trader retaining own accnt
Selection.Locked = False
Selection.FormulaHidden = False
Selection.Locked = False
Selection.FormulaHidden = False
Selection.Locked = True
Selection.FormulaHidden = False



A:Excel run-time error 1004

Try specifying the relative sheet name (or names) before the



lines. However, if that gets you any further, it'll still fall over without an End If


Read other 1 answers

Hi, can people help me with my VBA code? when i select some of the specialty from the drop down menu, it has an error messages says Run time error '1004': Application-defined or object-defined error. I cannot see anything wrong with my code, can you help?
I have attached my code.
Many thanks

Read other answers


I have a very simple excel spreadsheet that references data on another sheet in the same workbook. In order to view this data in a different order I recorded a very simple sort macro to highlight the correct columns and rows to sort. I then took the code from that macro and pasted it into a command button script. This works great on my machine. The problem I am having is when I send this to others. They are asked to enable macros and the do but when they try and click the command button they get a run-time error 1004. The debugger points to the line in error as "DataOption1 :=xlSortNormal".

I am running on XP with office 03. I have sent this to several other people including those with XP and 03 as well as other platforms.

Any advice for me would be greatly appreciated.



A:Excel run-time error 1004

Read other 7 answers

I've shared a protected Excel 2000 sheet with a friend by email. The sheet has a few macros, and several unlocked cells for data entry. When I run the sheet myself, it works fine. When my friend runs it, he gets a runtime 1004 error which says he's trying to change the contents of a protected cell.

If I give him a totally unprotected version of the worksheet, it then works fine for him too.

Could there be something in the way he has configured his Excel that would cause this to happen? We both have macro security set at LOW.


A:Excel Run Time 1004 Error - Sheet Protection

Read other 7 answers

I am trying to figure out the best way to copy a value from one cell, and paste it into another cell as a time value.

The cell to be copied can have values such as:


What I want to do is place a colon between the first two numbers and the last two numbers. I have code that does this but it fails when there is not a leading zero.

How would I do this be determining if there is a leading zero or not?

A:Solved: Excel Macro -> Time

Here is the solution I have come up with...

If Len(wsTest.Range("L6")) = 4 Then
wsTest1.Cells(x, "E") = Left(wsTest.Range("L6"), 2) _
& ":" & Right(wsTest.Range("L6"), 2)
wsTest1.Cells(x, "E") = Left(wsTest.Range("L6"), 1) _
& ":" & Right(wsTest.Range("L6"), 2)
End If

Read other 3 answers

Hi there

I have been slaving away for hours trying to figure out the code below:

If Range("C16") > "11:00:00 AM" Then

More code goes here.

It does not work!!

Can someone provide me with the correct VBA code??


A:Solved: Excel time VBA macro

If Range("C16") > TimeValue("11:00:00") Then
Msgbox "Have a good birthday, Mario!"
End If

Read other 2 answers

Sub Clearcells()
Range("B12", "B4000").Clearcontents
Range("C12", "C4000").Clearcontents
Range("E12", "E4000").Clearcontents
Range("G12", "G4000").Clearcontents
Range("I12", "I4000").Clearcontents
Range("N12", "N4000").Clearcontents
Range("O12", "O4000").Clearcontents
Range("P12", "P4000").Clearcontents
Range("Q12", "Q4000").Clearcontents
Range("R12", "R4000").Clearcontents
Range("T12", "T4000").Clearcontents
Range("U12", "U4000").Clearcontents
Range("V12", "V4000").Clearcontents
Range("W12", "W4000").Clearcontents
Range("X12", "X4000").Clearcontents
Range("Y12", "Y4000").Clearcontents
Range("Z12", "Z4000").Clearcontents
Range("AA12", "AA4000").Clearcontents
Range("AB12", "AB4000").Clearcontents
Range("T4", "T5").Clearcontents
Range("U4", "U5").Clearcontents
Range("V5", "V5").Clearcontents
End Sub

I added this in the excel developer for clear the cells but when I clicked on assistive button it clears all the data but when I protected the sheet it doesn't work it shows 1004 error. please help

Read other answers

Hello. Hope anyone could answer this question!
I have a problem when clicking cancel. I get a run time error '1004', where it says 'False.xls' could not be found...
(the file false.xls doesn't exist and are not supposed to exist either, but you are supposed to choose another file which the program will then open)

My code:

Dim myBook As Workbook
Workbooks.OpenText _
Filename:=Application.GetOpenFilename, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=False, _
Comma:=False, Other:=True, OtherChar:="*" 'the problem is here somewhere

Set myBook = ActiveWorkbook
ThisWorkbook.Worksheets("Database").Range("A6").PasteSpecial _
Application.DisplayAlerts = False
myBook.Close False
Application.DisplayAlerts = True

'Below here the rest of the programming code comes.

When clicked cancel, I also would like the macro to jump some lines further down in my program.

Hope anyone is able to help me.


A:run-time error '1004'

Try this it worked for me, nothing fancy but gets the job done
Private Sub CommandButton1_Click()
Dim myBook As Workbook
Dim Filename1 As Variant

Filename1 = Application.GetOpenFilename

' Check for the Cancel button.
If Filename1= False Then GoTo OverHere
Workbooks.OpenText _
Filename:=Filename1, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=False, _
Comma:=False, Other:=True, OtherChar:="*" 'the problem is here somewhere

Set myBook = ActiveWorkbook
ThisWorkbook.Worksheets("Database").Range("A6").PasteSpecial _

Application.DisplayAlerts = False
myBook.Close False
Application.DisplayAlerts = True
End Sub

Read other 1 answers


I encounter an error while opening an excel file, i could not view the file and it tell me to enable the macro. But after i enable the marco. The file still cannot be view, there is a error saying " RUN time error '1004' the specific dimension is not valid for the current chart type. i am using microsoft excel 2007. Please try to help. thank you.

A:Run time error '1004'

Two possible things I found. One is strictly for 2007. That is, according to MS, "Calculated members that are defined in Microsoft Online Analytical Processing (OLAP) cubes do not display by default in version 12 PivotTables. This does not apply to calculated measures (calculated members in the Measures dimension). These always appear. Calculated members appear by default in Office Excel 2003. However, this feature can be controlled in the object model (PivotTable.ViewCalculatedMembers) in both Office Excel 2003 and Office Excel 2007. This setting is also added to the Display tab in the PivotTable Options dialog box in Office Excel 2007 so that it is easy to make calculated members appear when they are needed."

Otherwise, for a 1004 error, according to MS, there are 3 possible causes, more info will be found by looking at the method. See:

Read other 3 answers

Can there be many causes to the runtime error 1004?

I have a workbook that has protected sheets. When I try to run a macro on the protected sheets, I get a runtime error 1004 message. When I unprotect the sheets, and re-run the macro, I get the runtime error 1004, but with a different message.

A:Excel 2007 and Runtime Error 1004

is the code highlighting in both cases the same line or different lines?
If you can post the file with the code would be easier to give you an answer!

Read other 2 answers

I have the following macro below which works great for when any value is entered in Column C, it time/date stamps the adjacent cell in Column D. However, I need it to do the same thing a few columns over (ie, when any value is entered in Column H, it will time/date stamp the adjacent cell in Column J)

For the life of me, and cannot get it to repeat . Any ideas? I'm a bit new at this, but thanks in advance for the help. Feel free to spell it out - I won't be offended.

Thanks! Here's the current macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C3:C9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


A:Solved: Excel Macro - Repeat Time/Date Stamp for Different Column?

Read other 6 answers

Hi there,
I've cobbled below code together (I'm game but probably not even a pedestrian in VBA, maybe more of a snail) to take some information from an Excel sheet and put it into an e-mail. I also want the active workbook attached to the e-mail and if possible another file with the same name but in pdf (haven't looked at that yet).

The ErrorCatch says it's an object or applications driven error.

I'm not sure about this line: .Attachments.Add ActiveWorkbook.FullName but really have no clue where the problem is.

Any help would be greatly appreciated. Thanks.


Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
End Function

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String

On Error GoTo Errorcatch
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi Leon" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"Attached invoice for " & Cells(C14) & ".Can you please approve for payment?" & vbNewLine & _
"Supplier: " & Cells(E43) & vbNewLine & _
"Invoice Number:&quo... Read more

A:Solved: Error 400 in Excel Macro

Read other 14 answers

This post is related to an earlier one, which has now been solved and closed:http://forums.techguy.org/business-applications/955009-excel-2007-lookup-formula.html. The macro outlined in this thread works fine as long as there are 4 values in each row the macro references. If there are less than 4 values the formula errors when trying to return the

value. Could somebody please edit the macro so that if it causes an error due to too few values, that it will then break out of the code instead of keep running? The ideal situation would be that it outputs any values it finds, and skips past and carries on if the function errors.

A:Solved: Excel Macro Error

No problem - all sorted now A bit of out of the box thinking was all that was needed!

Read other 2 answers

The following macro works perfectly on the Mac I have built it on. But when I transfer it to my server - or try to run it on a Windows machine - it returns a 400 error.

Does anybody know why?

Sub NextInvoice()
Range("K9").Value = Range("K9").Value + 1
Range("F7:F11").Value = "Name/Address"
Range("F29:K30").Value = "Personal message..."
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
NewFN = Range("B3").Value
ActiveWorkbook.SaveAs ActiveWorkbook.Path + "\" + NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close SaveChanges:=True
End Sub

A:Solved: Problem with an excel macro 400 error

Read other 16 answers


Every macro enabled file that I try to use on my computer gives me the same error: Runtime error 9 subscript out of range.

I get this error on files that work on other computers, including files I wrote on my computer, they don't work anymore and the all give me the same error.

Even writing a very simple macro I get this error, for example:

Sub MyInfo()
Workbooks("CropTool").Worksheets("RawData").Range("A1").Cells(2,4) = "hi"
End Sub

I have checked about 10 times to make sure that the workbook is indeed called CropTool, and the worksheet is called RawData.

This error has nothing to do with what I write in the module....

Please help.

A:Solved: excel 2007 macro error

Read other 7 answers

When I try to compile the Excel Macro, I get the following error message:

"Compile error: Procedure too large"

A:Solved: Excel Macro - Error Message

Read other 15 answers

I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010

A:Solved: Excel macro 2003 to 2010 error 438

Read other 16 answers

Hi Folks

I have an Excel 2007 .XLSM file containing the following macro that performs a Fourier analysis
Sub Macro1()
Application.Run "ATPVBAEN.XLAM!Fourier", ActiveSheet.Range("$C$1:$C$4"), ActiveSheet.Range("$E$1"), False, False
End Sub

The macro works fine when I launch the XLSM file manually. However, if I launch the file from a VB program (in Microsoft Access 2007), I get the following message:

'Cannot launch the macro ". The macro may not be available in this workbook or macros may be disabled'

The code I use to open the workbook and relevant add-ins:
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Open(MyPath & "Template1.xlsm")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISATPVBAEN.XLAM")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISANALYS32.XLL")
objXL.RegisterXLL "Analys32.xll"
I then go to the worksheet and try to manually execute macro1 but I get the above error message. Note that:
"enable all macros" is set in Developer/Macro Security
"trust Access to the VBA etc." is turned on. in in Developer/Macro Security
I tried placing the macro in my PERSONAL.XLSB file to no avail (not sure this was worth it, but I found a suggestion to that effect on another forum)
I tried executing macro1 from the VBA program as follows but got the same error:
Cod... Read more

A:Solved: Macro Error when launching Excel as OLE Automation Object

found the answer on:

I had omitted the auto open macro. Statement:

objXL.Workbooks("atpvbaen.xlam").RunAutoMacros 1

Read other 1 answers

I am continually getting the error "Run Time error 1004: Application defined or object defined error," each time a user clicks one of the cells within this excel file. It comes up as apparently a visual basic error. I've search other sites and unfortunately do not know enough about macros or excel formulas to know what to do next, so I thought I would post about it here. One user apparently has no trouble opening it while every other user has the same issue. Here is the debug code:

Private Sub Worksheet_Deactivate()

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = -4142
Target.EntireRow.Interior.ColorIndex = 8
Target.EntireColumn.Interior.ColorIndex = 8
End Sub

The "Cells.Interior.ColorIndex = -4142" portion is highlighted in yellow, so I assume that's where the error is. From there, I'm not sure what to do to change it. Please let me know as soon as possible what direction I should take.


A:run time error 1004 application defined or object defined error

To be honest...I would assume that the file is corrupt and try to replace it. All seemingly meaningful links on this error involve VBA coding and things that can go wrong with it...way overhead my head and out of my interest arena .

If it truly is a coding error (as seems implied), I would take it to the originator of that file, since it throws an error.

Very strange that e pluribus unum...manages to use it without problems .


Read other 1 answers

I am working in Excel 2003, within XP and have the following situation.

For approximately 18 months, I have been running the macro listed below without issue. Now, for no apparent reason, the macro fails to execute in its entirety. When executing the macro from within Excel, the execution starts and proceeds a few lines and then just stops. No error, no warning, nothing, the macro just stops.

If I open up the Visual Basic editor screen from within Excel and attempt to execute the macro with F5 or step through line by line with F8, execution will proceed to the end of the Selection.TextToColumns command line, and the insertion point goes back to the start of this same command once executed. If F8 is pressed a second time, the insertion point goes all the way back to the Sub line at line 1 of the macro and tries to run the macro again. The behaviour is consistent, and no error code or indication occurs. The code has not changed.

If I break up the macro into a series of smaller macros at each error point, and run them sequentially, I get the desired outcome.

I have tried renaming, copying and pasting the text into different macro files, exporting the macro and re-importing into different worksheets (this one is currently sitting in personal.xls which is still sitting in my /xlstart subfolder.

I have 8 similar macros that now all do the same thing - very puzzling.

Would welcome any suggestions you may have. Thanks all.

ps. for all you real VBA folks out there, no this isn&... Read more

A:Solved: Excel 2003 macro halts during execution - no error message

Read other 7 answers

Hello all

My problem is that I have put together a stop watch in which I want 2 bits of animated text to start at the same time as I click the button to start the stop watch. I currently can only get it to Call the animations and so it runs both animations in order and then starts the clock. Is it possible to get these working together at the same time?

My code so far is as follows;

Public StopSW As Boolean
Public ReSetSW As Boolean
Public SplitSW As Boolean
Public myTime

Sub stopwatch()

Call Animate_String1
Call Animate_String2

'Seconds and fractions of seconds Timer!
Dim Start, Finish, TotalTime
Dim myHi!, myH%, myMi!, myM%, mySi!, myS%, mySf!, SubTotalT, newTime
'Set timer.
Start = Timer
StopSW = False
ReSetSW = False
myTime = Sheets("Sheet1").Range("AA1").Value
'Yield to other processes.
'Calculate time.
Finish = Timer
TotalTime = Finish - Start
SubTotalT = myTime + TotalTime
myHi = Application.WorksheetFunction.RoundDown(SubTotalT / 3600, 0)
myH = myHi
myMi = Application.WorksheetFunction.RoundDown(((SubTotalT - (myH * 3600)) / 60), 0)
myM = myMi
mySi = Application.WorksheetFunction.RoundDown(SubTotalT - ((myH * 3600) + (myM * 60)), 0)
myS = mySi
mySf = SubTotalT - ((myH * 3600) + (myM * 60) + myS)
newTime = Format(myH, "0") & " :" & Format(myM, "0") & " :" & _
myS & " H:M:S"
'Sh... Read more

A:Running more than one macro at the same time in Excel

Read other 16 answers

I'm trying to write a macro that calculates the difference between two times. If the difference is greater that 8 hours then the diffence is set equal to 8 hours. My code currently looks like this but it doesn't work (cell A1 stores the difference between the two times).

If Range("A1").Value > 8 Then
Range("A1").Value = 8
End If

Any Suggestions?

A:Excel macro time comparison

Take a look at the datediff function - http://office.microsoft.com/en-us/excel-help/datediff-function-HA001228811.aspx


Read other 3 answers

When I recorded this macro it worked, however, when I later attempt to use the macro on the same data I get the above error. Why?

Selection.EntireRow.Delete (This is highlighted when attempting to Debug).
Selection.Delete Shift:=xlToLeft
Thank you in advance for any assistance.

A:Run-time error "1004": Cannot use that command on overlapping selections.

Your code works fine for me at the point where you state there's a problem, but for an apostrophie character to comment out your notes.

[COLOR=red][COLOR=Black]Selection.EntireRow.Delete[/COLOR] [COLOR=Lime]'[/COLOR][/COLOR][COLOR=blue][COLOR=Lime][put in apostrophie sysmbol to comment][/COLOR][COLOR=Black](This is highlighted when attempting to Debug).[/COLOR][/COLOR]

It may be that you do not have any blank cells in column A, or that the whole of column A is blank.

Can you explain more.

Welcome to the forum btw.

Read other 3 answers

I have the following code...

If ComboBox1 = "ETECHRYOBIAND-W" Then
ActiveCell.Formula = _
"=OR(D12=""1,1,1,2-Tetrachloroethane""," & _
"D12=""1,1,1-Trichloroethane"",D12=""1,1,2,2-Tetrachloroethane""," & _
"D12=""1,1,2-Trichloroethane"",D12=""1,1-Dichloroethane""," & _
"D12=""1,1-Dichloroethene"",D12=""1,2-Dichlorobenzene""," & _
"D12=""1,2-Dichloroethane"",D12=""1,2-Dichloropropane"",D12=""Benzene""," & _
"D12=""Bromochloromethane"",D12=""Bromodichloromethane"",D12=""Bromoform""," & _
"D12=""Bromomethane"",D12=""Bromomethane"",D12=""Carbon disulfide""," & _
"D12=""Carbon tetrachloride"",D12=""Chlorobenzene"",D12=""Chloroform""," & _
"D12=""Chloromethane"",D12=""cis-1,2-Dichloroethene""," & _
"D12=""cis-1,3-Dichloropropene"",D12=""Ethylbenzene"&... Read more

A:Solved: Runtime Error '1004'

Read other 10 answers

I need an excel macro to refer to a different column each time I run the same macro. Does anyone know what the syntax is for this?

A:How do you get an excel macro to refer to a different column each time its run?

Read other 14 answers

I'm receiving the following error when trying to run a recorded macro:
"Run-time error '1004': Cannot use that command on overlapping selections."

It was successful when I recorded it, but when I tried to run it on a new sheet, I received the error. My code:

Sub Sub_and_delete_blanks()
Sheets("Innovation Mapping Project - Re").Range("A1:MX29").AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range( _
"A5"), Unique:=False
End Sub

A:Excel 2016 Error "1004"

Which line is highlighted it you click Debug after receiving the error?

Read other 1 answers

Hello All,

I am trying to put a macro together that will be triggered when a value is entered into a cell. The macro would then place the current time/date in a different cell along with placing the username used in another cell. I have figured out how to have the cell event to be triggered using the below code but I am lost after that. Any help you can provide would be great.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, MyCell As Integer
Set VRange = Range("D")
If Union(Target, VRange).Address = VRange.Address Then

ActiveCell.Offset(0, 5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -5).Range("A1").Select
Application.CutCopyMode = False
End If
ActiveCell.Offset(0, 6).Range("A1").Select
Dim sUserName As String
sUserName = GetNTLoginName
End Sub

A:Excel Macro to Enter Username and Current Time


I couldn't quite get what the macro was trying to do. The best I can figure is that for any change in Column D you want to place in the corresponding row / columns I and H - the date/time and the user name respectively.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 And Target.Row > 1 Then ' restricts changes to col D from row 2 onwards
For i = 1 To Target.Count
If Target(i) <> "" Then ' if you double click in a cell but do not add data the date and user name will not be added
Target.Offset(0, 5) = Date + Time 'puts the date and time in Col I
Target.Offset(0, 6) = GetNTLoginName 'this didn't work for me in my environment
' Target.Offset(0, 6) = Environ("username") 'this is what I use. Try it if the previous row didn't work
End If
End If
End Sub

See if this works for you. If it doesn't produce the results you want post back if you need help.

Read other 1 answers

I have created a macro in Excel 2003. Once I tested the macro several times, I placed the macro in a blank workbook, and saved it as "Personal.xls". I saved the "Personal.xls" workbook in this file path...

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

I opened another blank Excel workbook, and hide the Personal.xls window.

When I try running the macro from a button on the toolbar, I get the following Run-time error....

Run-time error '9'
Subscript out of range

This is the line of code that gets highlighted when I debug the error..

Set ws = ThisWorkbook.Sheets (.Name)

Can anyone help me to resolve this error?

A:Solved: Excel 2003 and Run-time Error

Read other 13 answers

Hey guys,
I have one worksheet called Consumption with a list of clients. I need to filter the data by building type and copy the data from this worksheet to a worksheet for each building type. The building types are in column E and the entries start at line 12. The different types possible are listed in column E above the entries. For this particular example I need the first one in E1. Here is the code I have in VBA

Sub PasteToCustSheet(OfficeBuildings)
Dim C As Range
For Each C In Range(Range("E12"), Range("A65536").End(xlUp))
If C.Value = Range("E1").Value Then
ActiveSheet.Paste ThisWorkbook.Sheets(C.Value).Range("a65536").End(xlUp).Offset(1, 0)
End If
Application.CutCopyMode = False
End Sub

It keeps giving me a runtime error 9 and highlighting the first line but OfficeBuildings is the name of one of the sheets I need the information to go to. How do I fix this?

A:Solved: Excel 2003 VBA - Run time error 9

Read other 8 answers

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers

Iím using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False

Does anyone have a better solution?

Read other 1 answers

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:


There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

A:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again

Read other 1 answers

I have created a macro that goes through my workbook and is supposed to clear the interior color from cells A1:I900 on each page. When I try to run the macro I get a pop up that just says "400" in it with a red circle that has a white "X" through it. Here is the code:

Sub Clearcolors()
Dim ws As Worksheet
Dim RngH As Range
Dim RngHD As Range
For Each ws In ThisWorkbook.Worksheets
Set RngH = ws.Range("A1:I" & Range("I900").End(xlUp).Row)
For Each RngHD In RngH
RngHD.Interior.ColorIndex = xlNone
Next RngHD
Next ws
End Sub

What can I do to fix this problem? Any suggestions?

A:Solved: VBA error "400" running an Excel macro.

Read other 15 answers

Hi Guys:

I have created a macro( 2 line macro actually) that goes through my workbook and it supposed to clear the value from cells b1:b68 on main page. When I try to run the macro with the sheet protected I get a pop up that just says "400" in it with a red circle that has a white "X" through it, whit the sheet unprotected works perfectly !. Because it is a survey I need to protect teh content, to be manipulated by many. Can somebody give a clue? Here is the code:

Public Sub Clear()
End Sub
Windows 7
MExcel 2010

Many thanks


A:Solved: error "400" running an Excel macro

Read other 8 answers

Hi there,

I would really appreciate some help with a problem I am having in Excel 2010.

I have a workbook which is used to track trainings within our organization. It is a file which inludes filter macros which are liked to buttons at the top of the screen (for instance a button to show all completed trainings and one to show all failed trainings etc.). There is also a show all button used to clear filters and each button first runs a command to clear all filters before sorting.

This file has worked very well for more than a year, but is suddenly showing an error: when the data is filtered within a view, especially if it is filtered by more than one criteria (for instance filtered by country, and by name), instead of clearing the filters and applying a new filter I receive this error:

Run-time error '91': Object variable or With block variable not set

Debugging shows the code in bold highlighted. I have pasted the entire code:

Sub WL_Heidelberg()
' WL_Heidelberg Macro
'clear any filters
On Error Resume Next
On Error GoTo 0
ActiveSheet.Range("Training_Requests").AutoFilter Field:=16, Criteria1:= _
"WL Heidelberg"
ActiveWorkbook.Worksheets("Training Requests").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Training Requests").AutoFilter.Sort.SortFields.Add _
Key:=Range("H2:H1842"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNorma... Read more

A:Solved: Excel 2010: Run-time error '91' on clearing auto filters

Read other 12 answers

I created a macro for one of my co-wokers that:

Refreshes data using MS Query to pull data from an Access database.
It then formats fonts and headers and stuff correctly on that worksheet.
It then creates a pivot table on a new sheet (using the wizard) based on this refreshed info (it's actually based on a cell range 8 columns wide and 10,000 rows long excluding the header, just in case we get a huge number of claims on one ship).
It then formats the pivot table correctly and renames the worksheet.
Finally, it moves the worksheet to where it should be (still in same workbook).

This worked the first time, and still works now, except for the last part, where it moves the sheet. I now receive an error:

Run-time error '1004':
Application-defined or object-defined error.

I hit Debug to see where the problem was (I know very little VB) and this is what was there. The Bold part is what was highlighted in yellow in the VB window:

Sub Refresh_Pivot()
' Refresh_Pivot Macro
' Macro recorded 04/09/01 by Liz Clark
Selection.QueryTable.Refresh BackgroundQuery:=False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 11
With Selection.Font
.Name = "Times New Roman"
.Size... Read more

A:Excel 97 macro error

Read other 6 answers


I have a peice of code that filters a column for zero values and returns the rowcount. I tried looping over different columns.this macro works well with small input.
But I have an excel sheet with 160106 rows. I want to run my macro on this . I am getting a run 1004 error.I found the following link which kind of explains the problem

But I am not able to resolve it. Could anyone please help me. I am pasting my macro below

my sample file is in
it is a 96mb file


Option Explicit
Sub findrcn()
Dim wsStart As Worksheet
Dim sWord As String
Dim RowCount As Integer
Dim i As Long
Dim j As Long
Dim l As Long
Dim k As String
Dim Final As Integer
Dim lastrow As Integer
Dim rng As Range
Dim oBook As Workbook

Set wsStart = ActiveSheet
'this loop is to check if a sheet exists
For j = 1 To Worksheets.Count
k = Worksheets(j).Name
If UCase(k) = UCase("Analysis") Then
lastrow = ((Sheets("Analysis").Range("A" & Rows.Count).End(xlUp).Row) + 1)
lastrow = 0
End If

Next j
MsgBox "finished checking the sheets"
i = 1
For Each rng In Range("A1:B1").Columns
sWord = Replace(rng.Address(RowAbsolute:=False), "$", "") ''Now I am trying to loop over all the columns

If lastrow = 0 Then
... Read more

A:Macro run time error


Can't solve your problem as it is now. However, I suspect there's an other approach that could help you reach the goal. So what is it you actually want to achieve? I saw a lot of data in the sample workbook. You want to count the zeros in each and every column?


Read other 1 answers

=IF(ISERROR(100),"NA",100) appearing in Excel 2007 shortcut key macro?

First, my disclaimer is that I am not familiar with Excel or VB!​

That being said, one of my users is getting an error when running a macro in Excel 2007 from his personal.xlsm.​
The macro is a simple color change which we have re-recorded several times but when he attempts to run the macro with a shortcut key the field populates with =IF(ISERROR(100),"NA",100) where 100 was the test value of the original cell. If you run the macro from the menu it works correctly. ​
Macro is:​
Sub Macro1()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+Shift+E
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
End Sub​
Any suggestions are greatly appreciated. ​


Read other answers

I can run this macro on my own machine but when I transfer it to another computer, I get the following error: Run Time error 1004 Cannot open PivotTable source file 'headcount by area' - any ideas what's wrong??!

A:Error running a macro in Excel

Read other 6 answers

Hi there,

I'm trying to do a simple macro that opens an Excel template. Copies fields from a row in an open Excel sheet to the newly opened template. Prints the template. Puts a date into that row. Stop.

I got an error at r = ActiveCell.Row until I deleted Option Explicit. Also got an error at the copies code that I turned into text. Now I get a Compile Error: Named argument not found at IgnorePrintAreas:=False

I tried to delete that line but then I get an error for the entire print code.

Does anybody know where the error sits? I'm trying to print to the default printer.

Thanks, Christine

Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'ActiveWorkbook.SaveAs FileName:=BkSrc.Cells(r, 3).Value
'Copied from Zack Baresse, I think I will need this
'Call ToggleEvents(False)
'Set ws = ThisWorkbook.Sheets("CodingTemplate")
'If WbOpen(BkDest) = True Then
'Set wkb = Workbooks(FileName)
'blnOpened = False
'If Right(FilePath, 1) <> App... Read more

A:Compile Error in Excel VBA macro

I have worked a bit more on this and above problems seem to be resolved. But, big but, I now get a run-time error '9'. Subscript out of range.

I looked it up and it says it means one of the workbooks isn't open but I'm working with 2 books, one which I have open to start with and the other one I do open.

Any ideas? Thanks, Christine

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Function WbOpen(wbName As String) As Boolean
'Originally found written by Jake Marx
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function
Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'Copied from Zack Baresse, I think I will need this
Call ToggleEvents(False)
Set ws = ThisWorkbook.Sheets("CodingTemplate")
... Read more

Read other 1 answers

I have a spreadsheet with macros that I use for work which works flawlessly on Win XP, but when you try to run the macros on a Vista/7 machine it gives this error:

"Compile error: Can't find project or library"

It seems to be hanging up at "Trim" on this line:

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?


Read other answers

I was using this Word Macro below which I got from this forum. It's great for reducing all the images in a Word document automatically, but when using it in Word documents with lots of pages, it fails each time I try to use it with "Run-time error 4198". Can anyone tell me why? My documents have several thousand pages, with lots of images, and it will be so helpful not to have to adjust their image sizes one by one. Thanks in advance.

Sub ResizePhotos()
DesiredHeight = InputBox("Enter height in points (72pts/in, 28pts/cm):", , 180)
For Each ishape In ActiveDocument.InlineShapes
With ishape
ImageWidth = .Width
ImageHeight = .Height
NewHeight = DesiredHeight
NewWidth = Round(NewHeight * ImageWidth / ImageHeight)
If NewWidth > 432 Then 'limit width to six inches
NewWidth = 432
NewHeight = Round(NewWidth * ImageHeight / ImageWidth)
End If
.Height = NewHeight
.Width = NewWidth
End With
End Sub

A:Word Macro keeps getting Run-Time error - please help!

Read other 10 answers

I have a new Dell laptop with Windows ME & Office XP. When I go to record a new macro, I can enter the description and other info, but when I click 'OK' to start recording the Macro, I get an "Unable to record" error message. The error message has no help button or details button, just an 'OK' button. I have everything for Office installed.....Please help, thanks

A:Unable to record macro error in EXCEL >:-(

Read other 7 answers