Visual Basic :: Activesheet.Unprotect Causes Runtime 1004 Error!?

Runtime Error 1004
I'm using the code below:

Can anyone explain what it is trying to do? I copied it & now i need to know how to change my selection. When i use this code i get runtime error 1004 Application defined or object defined error. It stops at the line below.

If i can understand what this code below is trying to do i may be able to fix it!!
thanks
Basically i need to copy a range of cells from one excel sheet to another. The set My Range part doesnt make sense to me - what does the +10, 9 do?

VB Code:
Sub CopyCells()Dim MyRange As ObjectSet MyRange = objExcel.Worksheets("Sheet1").Range(objExcel.Cells(NotShippedRow, 1), objExcel.Cells(NotShippedRow + 10, 9))

Runtime Error 1004?
I get the following error,

RunTime error '1004'
Addins Method of Addins Class Failed.

It directs me to this line of code.
AddIns.Add FileName:= _
"C:Program FilesMicrosoft OfficeOfficeLibraryAnalysisANALYS32.XLL"

What is wrong? This code works perfectly as a macro why won't it work in VB6.0?

Thanks
NEvin

Runtime Error 1004
I made a project in VB 6.0 and i created the exe from it. I send it to someone and he got the following error:
runtime error 1004
method ~ of object ~ failed

Please give me some idea.

Thank you,
Kenguru

VBA Runtime Error 1004
Hi,
I am working on a form processing project which uses a product called Formware. In this we are using VBA which comes along with the product. Our project is in production right now. Long back we had released one patch which is running fine in production. 15days back we released one more patch with some minimal changes. But after this patch installation we are getting "VBA Runtime Error 1004".
The problem we are facing is that with the new patch, this error occurs only after some period of time. So user needs to log off and after logging in again the application runs fine.

Can this be because of the changes done in the code? or is it related to Data? We tried to reproduce this error in our Development environment as well as in the UAT environment but all in vain.

Could you help us resolve this problem?

Thanks in Advance
Rekha

Runtime Error 1004: How Can I Use Rows.
I want to do a search in the "all" sheet with value I type in the TextBoxItem1 in "sheet1" . And I can find a block(with the rowInQuotationsheetBegin and rowInQuotationsheetEnd )in the "all" sheet, go copy/paste to the sheet "Quotationsheet".

my codeExcel 2003)

Private Sub CommandButtonItem1_Click()
Dim item As Long
Dim rowInQuotationsheetBegin As Long
Dim rowInQuotationsheetEnd As Long
Dim tmp As String

item = Worksheets("Sheet1").TextBoxItem1.Value

For i = 8 To 4000

If item = Worksheets("all").Cells(i, 6).Value Then
rowInQuotationsheetBegin = i
For j = i + 1 To i + 20

If Worksheets("all").Cells(j, 5).Value = "ITEM NO." Then
rowInQuotationsheetEnd = j - 1
Sheets("all").Select
ActiveSheet.Unprotect
Application.CutCopyMode = False
Rows("8:23").Select
'Rows(rowInQuotationsheetBegin : rowInQuotationsheetEnd).Select
Selection.Copy
Sheets("QUOTATIONSHEET").Select
ActiveSheet.Paste
End If
Next j

End If

Next i

End Sub

at first I want to use

'Rows([rowInQuotationsheetBegin : rowInQuotationsheetEnd]).Select

to get the rows i need, but I dont know how to use Rows with variables. Then I try to test with

Rows("8:23").Select

But I still got runtime error 1004. some code I modified direct from Macro, maybe it is not so professional. So how can I fix this problem?

Thx.

New To Excel And Getting A Runtime Error 1004
Hi I'm new to excel and am trying to create a maintenance "database" that will update the dates that maintenance is to be done by the date today. Some of the things will be done weekly/monthly/yearly. I keep getting a runtime 1004 error when I try and use:

Code:

Sub weekly_monthly_yearly()
'
' weekly_monthly_yearly Macro
' Macro recorded 10/06/2004 by Shaw Resources
'
' Keyboard Shortcut: Ctrl+d
'change dates at the start of a new week
If (Sheet1.Cells(5, E).Values Like "Weekly" And DateDiff("d", Now, _
Sheet1.Cells(5, H).Values) >= 1) Then
'if its monday
If (Weekday(Now(), vbMonday)) Then
Sheet1.Cells(E, 5).Values = Sheet1.Cells(5, E).Values + 7 'add 7 days to schedule
'if its tuesday
ElseIf (Weekday(Now(), vbTuesday)) Then
H = Now() + 1
'if its wednesday
ElseIf (Weekday(Now(), vbWednesday)) Then
H = Now()
'if its thursday
ElseIf (Weekday(Now(), vbThursday)) Then
H = Now() + 6
'if its friday
ElseIf (Weekday(Now(), vbFriday)) Then
H = Now() + 5
End If
'if its monthly maintenance
ElseIf (E = "Monthly" And Month(Now()) > Month(H)) Then
'add a month to date
H = Month(H) + 1
'if its yearly maintenance
ElseIf (E = "Yearly" And Year(Now()) > Year(H)) Then
H = H + 365
End If
End Sub

I can't seem to get by the error. I know there's a lot more I have to do like change all the E's and H's to actual data but I can;t figure out how to call on the data without the error popping up. Any help would be appreciated.

Exporting Chart - Runtime Error 1004
Hello to all!

I've this code running on two diferent windows enviroments: a winXP_sp2 and a W2KServer_sp4, both with Office2003_sp1 installed, but in the second one (w2kserver_sp2) i receive a 1004 friendly message!!

"Runtime error 1004
[method name]
Application-defined or Object-defined error"

If xlchartObj.Chart.ChartTitle.Text = designacao Then
' i've confirmed this path with sucess!
chartCompleteName := ....

xlchartObj.Chart.Export filename:=chartCompleteName, FilterName:="GIF"

' other code...
End If

What can i search for in the w2kServer machine that difers in the winXP?!?!

If i can give you more info about my enviroment, tell me, please!!

Extreme VB Thankz!

Public Variable Runtime Error 1004
I have a public variable:
Code:
Public RefZone1Rng As Range

That I set in a Sub:
Code:
Sub CheckZones()
Set RefZone1Rng = Refwrksht.Range(Refwrksht.Cells(2, 1), Refwrksht.Cells(RefZone1_Bot, 1))

That I would like to be available to a Function that I pass some variables to within the first sub:
Code:
ColorNum = Search_ref_zone(Choice, BZone)
End Sub

The following is the function that I go to. I would like it to recognize that RefZone1Rng has been declaired in the procedure above without having to pass it the variables explicitely.
Code:
Function Search_ref_zone(RefZone As Integer, BZone As String)
Dim SelRange As Range
Select Case RefZone
Case 1
SelRange = RefZone1Rng
End Select
End Function

Is this possible? Thank you!

Edited by - AgentH on 8/24/2007 7:51:26 AM

Runtime Error 1004 For Excel Chart Creation On Second Run
Hi All,
I have an error cause by my create of chart. its runtime error 1004: Method ' Charts' of Object '_Global' failed

any clue on what its talking about ?

my code is as follows and works fine the first time my application goes through this section of the code. But the error occurs during the second time if i run this code ( ie i dun close the application; and it works during the first run)

' Add Chart
Charts.Add '' ERROR POINTS TO THIS LINE
ActiveChart.ChartType = xl3DColumnStacked
ActiveChart.SetSourceData Source:=Sheets("Chart").Range("A1:B51"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Chart!R1C1:R51C1"
ActiveChart.SeriesCollection(1).Values = "=Chart!R1C2:R51C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
ActiveChart.SeriesCollection(2).Delete

' Set Parameters of the Chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Thrust Plate Ava"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
.Elevation = 15
.Perspective = 30
.Rotation = 20
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
.HasLegend = False
End With

' Change the Background color of the Chart Area
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 50
End With

' Control the size of the Chart
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.4, msoFalse, _
msoScaleFromBottomRight

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.0000"

osheet3.Range("A1:B51").Font.ColorIndex = 2 ' set font color to white

Urgent: Runtime Error 1004 -Range Method
Hello,

I'm getting:

RunTime error 1004
Method Range of object _Global failed

Code:
ThisWorkbook.Worksheets("Data").Select
'Error Occurs on line BELOW:
Set rnArea = ThisWorkbook.ActiveSheet.Range(Excel.Range(range1), Excel.Range(range2).End(xlUp))
The code works superbly on my computer, but when posted online, I get the error.

Thanks,
LA

Runtime Error 1004 With Range(Cells(... Reference
Hi,
In the following code, the second msgbox line returns Run-time error 1004 if the referenced sheet is not active:

Sub RangeCellProblem()

add1 = Sheets("PrintInfo").Cells(11, 7).Address
add2 = Sheets("PrintInfo").Cells(110, 7).Address
MsgBox Application.WorksheetFunction.CountA(Application.ActiveWorkbook.Sheets ("PrintInfo").Range(add1 & ":" & add2)) 'No error

MsgBox Application.WorksheetFunction.CountA(Application.ActiveWorkbook.Sheets ("PrintInfo").Range(Cells(11, 7), Cells(110, 7)))
'Returns error when sheets("PrintInfo") is not active.

End Sub

I use the "Range(Cells..." reference a lot. I'd rather not create variables or select the sheet beforehand every time. And I need to refer to rows and columns by number.

Is there any minor tweak I can make to prevent the error?

Thanks,
Michael

RESOLVED: Runtime Error 1004 When Exporting To Excel
Hi, my application is getting the followed error

Runtime error 1004: Application defined or Object defined error

and this is the offending piece of code.

myBook.Worksheets(1).Range(bCol).Value = vDate

vDate is a string, and I have checked bCol as well to make sure that they have correct variables.

Any help much appreciated...

ScriptControl; Language = Javascript; Runtime Error 1004
Yo yo yo,

I'm working with the undocumented scriptcontrol and I have a "runtime error 1004 Expected: ;". I dont know where the error is because the JS does have all the ; in all the right places. Heres the code

frmMain
Code:
Dim mMain As MSScriptControlCtl.Module
Dim SharedThings As CScripts

Private Sub Form_Load()
    Set SharedThings = New CScripts
    scrMain.AddObject "Cls", SharedThings, True

    Set mMain = scrMain.Modules.Add("mMain")

    Open App.Path + "" + "default.js" For Input As #1
        Dim code As String

        Do Until EOF(1)
            Line Input #1, temp

            code = code + vbCrLf + temp
        Loop

        'MsgBox code
    Close #1

    scrMain.Modules.Item("mMain").ExecuteStatement (code) ' This is the line causing troubble

    scrMain.Modules.Item("mMain").Run ("main")
End Sub

CScript
Code:
Sub PrintOut(ByVal str As String)
    frmMain.txtOutput.Text = frmMain.txtOutput.Text & vbrlf & str
End Sub

And the file default.js
Quote:
main()
{
    Cls.PrintOut("Hello, World!");
}

My biggest problem is that I dont know where the error is, in my code (probably not) or in the JS. Its too bad MS didnt build a better Lexer (I think that its the Lexer anyways) with better error handeling

Edited by - Cerf on 8/10/2004 11:29:46 AM

Excel Runtime Error 1004 License Unavailable
THis is a weird one. I have been running fine on my laptop using Excel thru VB6, when all of a sudden I get this error...

"Run-time error 1004, This command is unavailable because the license to use this application has expired"
WHat the Frig is that about. On debug it lands on line
Set WkbNewbook = XlAPp.Workbooks.add

?????

Runtime Error 1004 Unable To Set The Name Property Of The Series Class
hi

i get a runtime error when i update a chart's names

my code is as follows

ActiveSheet.ChartObjects("Chart 7").Select ' select the chart
ActiveChart.SeriesCollection(1).XValues = "=Data!R21C2:R21C13" 'Primary X Axis Labels
ActiveChart.SeriesCollection(1).Name = "=Data!R42C1" 'Critical Name
ActiveChart.SeriesCollection(2).Name = "=Data!R43C1" 'High Name
ActiveChart.SeriesCollection(3).Name = "=Data!R44C1" 'Slow Name
ActiveChart.SeriesCollection(1).Values = "=Data!R42C2:R42C13" 'Critical values
ActiveChart.SeriesCollection(2).Values = "=Data!R43C2:R43C13" 'High Values
ActiveChart.SeriesCollection(3).Values = "=Data!R44C2:R44C13" 'Slow Values

The problem is when the High name is being set.

This code has worked many times before, and the values on the Data worksheet in rows 42-44 have not changed.

Does anyone know why this error is occuring now? Can anyone help me?

thanks
mags

Runtime Error 1004 Method Range Of Object_global Failed
I get this error when attempting to open a second form to do updates.
UPDATE: I changed around some things now the error happens in another part of the code:

on the main menu there's a cmd button Guests; code below

Code:
Private Sub cmdGuests_Click()
frmMainMenu.Hide
frmGuests.Show

End Sub

here is the guest form code:

Code:
Private Sub cmdAdd_Click()
UpdateData

End Sub

Private Sub txtRow_AfterUpdate()
'go to the right row

If txtRow.Text < 5 Then
'can't go above the top of the range
    txtRow.Text = 5
ElseIf txtRow.Text > Range("Guests").Rows.Count Then
'can't go more than 1 row below the end of the range
    txtRow.Text = Range("Guests").Rows.Count
End If
UpdateData
txtFtName.SetFocus

End Sub
Private Sub UserForm_Initialize()

UpdateData

End Sub
Private Sub UserForm_Activate()
With cboState
     .AddItem "AL"
     .AddItem "AR"
     .AddItem "PA"
     .AddItem "OK"
     .AddItem "CO"
     'get list from VB project

End With

Call populatecombobox(cboRSVP)
Call populatecombobox(cboThankYou)
Call populatecombobox(cboInvite)


End Sub
Private Sub UpdateData()

'get fresh values for all textboxes from the spreadsheet
txtFName.Text = Range("FirstName")(txtRow.Text).Value '<----error happens here
txtLName.Text = Range("LastName")(txtRow.Text).Value
txtCity.Text = Range("City")(txtRow.Text).Value

End Sub
Private Function populatecombobox(cbo As ComboBox)
    cbo.Clear
    cbo.AddItem "Yes"
    cbo.AddItem "No"
End Function

Private Sub txtFName_AfterUpdate()
'store the value of the textbox in the spreadsheet

Range("FirstName")(txtRow.Text).Value = txtFName.Value

End Sub

Private Sub txtLname_AfterUpdate()
'store the value of the textbox in the spreadsheet

Range("LastName")(txtRow.Text).Value = txtLName.Value

End Sub
Private Sub txtAddr1_afterupdate()

  'store address one in spreadsheet
  Range("Address1")(txtRow.Text).Value = txtAddr1.Value

End Sub

Private Sub cmdPrevious_Click()
'previous row

'previous row

If txtRow.Text > 5 Then txtRow.Value = txtRow.Value - 1
UpdateData

End Sub

Private Sub cmdNext_Click()
'next row

If txtRow.Text < Range("Guests").Rows.Count Then txtRow.Value = txtRow.Value + 1
UpdateData

End Sub

the field names are in row 5 of the spreadsheet as I put a command button above that.
I renamed the sheet to Guests.

What am I missing? The main Menu button is fine. However when attempting to populate the form from the spreadsheet I get the failure notice. Do I have to declare something? In playing around a bit I noticed that if I put something like "F5" for the state cell (and the others with "A5" etc) it processes but it puts in the value of the cells. (IE if F6 is hard coded for "State", NC was entered but I could not move forward to add a new record) What I WANT is the records and the ablity to move up & down and to view the records!!!

I got some of the code from a tutorial I found on the net...I wonder if the version of Excel matters? Mine is XP and I have no idea what version the tutorial was created in.

Edited by - FuzzDuckie on 1/17/2004 2:30:26 PM

RunTime Error 1004:Select Method Range Of Class Failed
Hi

I have an Excel Sheet in Which employee details(empname,empno) are stored.when I try to delete an employee it is giving the Select Method Range Of Class Failed Runtime Error. Iam using the following code to delete an employee ..
Resource-Details is the Sheet Name wher employee details are stored.
'rid' is the employee id selected to delete from drop down.....

Do While Sheets.Item("Resource-Details").Range("A" & lngcount) <> ""
If Sheets.Item("Resource-Details").Range("A" & lngcount) = rid Then
Sheets.Item("Resource-Details").Rows(lngcount).Select
Sheets.Item("Resource-Details").Rows(lngcount).Delete
Count = Count + 1
'Exit Sub
End If
lngcount = lngcount + 1
Loop

Can anyone help me?????////

And I want to close my previous posts in this Forum....How to close my previous posts????

Runtime Error 1004: Application-defined Or Object-defined Error
Hi All

I wrote a program a while back that was working fine until yesterday when it started throwing the above error. For the life of me I cannot figure out what the problem is. If you could please help, I would appreciate it very much.

I'm using a sql query in vb6 to get data from a sql db and dumping the data into Excel 97. To be sure I have set all my references and the program was working fine till yesterday.

Thanx for all your help

Kind Regards

VBA Excel: Runtime 1004 Error With "Refresh BackgroundQuery" In Web Query
Anyone seen this one before? I get the notirious runtime 1004 error at the following point in an Excel macro that retrieves stock data from Yahoo:

strurl = "http://finance.yahoo.com/q?s=" & Symbol & "&d=v2"
With Worksheets("Temp").QueryTables.Add( _
       Connection:="URL;" & strurl, Destination:=Worksheets("Temp").Cells(1, 1))
           .BackgroundQuery = True
           .TablesOnlyFromHTML = False
           .Refresh BackgroundQuery:=False ' <- Bombs at this line with
           .SaveData = True ' Run-time Error '1004':
   End With ' Application-defined or object-defined error

The weird part is, it only seems to happen on my machine (XP Home, Office 2000 SR-1 SP3 as noted above), and not on a variety of others running various
flavors of Excel, from 97 to XP, on environments ranging from Win2K to Mac SoftWindows!

I put the bombing code in a blank ss with nothing else. It still bombs on the Yahoo URL (I substituted the "symbol" var with hard-coded symbol e.g. QQQ for a complete URL string). It does NOT bomb on a URL where I know the data is small and static (the Yahoo page updates dynamically before being gotten, obviously).

When I try a similar web query hand entered in a blank Excel ss using the Yahoo URL (Data -> Get External Data -> New Web Query), 4 times out of 5 I get a dialog saying:

"Unable to open <URL>. The Internet site reports that a connection was established but the data is not available."

(where <URL> is the actual Yahoo URL in the VBA at top of this thread)

I wonder if this 1004 error in the VBA macro is being caused by the query returning empty when the connection returns no data? If so, why am I getting it and no one else?? Is this a known issue?

Get Runtime Error 1004, "Cannot Access 'xxxx.xls'".
I am using a DAO data control object to open an existing multi-sheet Excel spreadsheet as a recordset, update one of the worksheets and then close it using the xxxxx.recordset.close method. This works fine, but when I try to reopen it in the same sub using the following code:

Set AppExcel = CreateObject("Excel.Application")
AppExcel.Application.Workbooks.Open "C:xxxxx.xls"

.... the Open statement fails with runtime error '1004', Cannot access 'xxxxx.xls'.

I have checked the list of active processes and there are no Excel programs running. Is it possible that, even though the recordset has been closed, DAO still has control over the application and is preventing the AppExcel object open statement from executing?

ActiveSheet.Paste Error
hye all,

I'v problem with this code :

Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="BEMLG"
Selection.AutoFilter Field:=6, Criteria1:="MVOU"
Cells.Select
Selection.Copy
Sheets.Add.Name = "Master"
Sheets("Master").Select
ActiveSheet.Paste
Range("A:B,D:G,I:BN").Select
Range("I1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
Range("C2").Select

The problem is, there is an error at ActiveSheet.Paste
This error occur since I've upgrade to Office 2003
This coding use in my MS Excel project...
The error popup was like this "run-time error '1004'. Paste method of Worksheet class failed"....
I'm really need your help!!

Range Of Cells; Runtime 1004
Hi all!

im VBA-blind for moment, i guess; i'm trying to use some integers as row/column indentifiers,(see code) but it crashes on "Sheets("2005").Range(Cells(Row, col2), Cells(Row, col3)) = Array("A", _....."

Returns Runtime error '1004' Application defined or object defined error

when i use "Sheets("2005").Range("A1:B1") = Array("A", _....." it works fine.
what going wrong?
thanx in advance, i'm really stuck; any help would be appreciated!!!!!

Code:
Sub Yadda()
Dim RW As Long
Dim cnt As Integer
Dim cnt2 As Integer
Dim col As Integer
Dim col2 As Integer
Dim col3 As Integer

cnt = 15
cnt2 = 1
col = 1
Row = 1
col2 = 1
col3 = 2

With Sheets("new")
While cnt2 <= 15
If Len(.Cells(cnt, col).Value) > 0 Then
Sheets("2005").Range(Cells(Row, col2), Cells(Row, col3)) = Array("A", _
.Cells(cnt, col).Value)
col2 = col2 + 1
col3 = col3 + 1
End If
cnt = cnt + 1
cnt2 = cnt2 + 1
Wend

col = col + 1

End With
End Sub

Error 1004: Application-defines Or Object-defined Error
Hello there,

while trying to write a script I get this error.
Here is minimal script which reproduces the error (working with Win XP and Office 2003 Sp3).

Code:
Function foo()
On Error GoTo Hell
Dim i As Integer
For i = 1 To 4
Dim r As Integer
r = Application.Caller.Row
Dim c As Integer
c = Application.Caller.Column
Dim s As Integer
s = Application.Caller.Worksheet.Index
MsgBox ("Sheets(" & s & ").Cells(" & r & ", " & c & ")")
Sheets(s).Cells(r + i, c + i) = "bar" ' this line produces the error
'Sheets(1).Cells(1,1) = "bar" produces the same error :(
Next i
foo = i
Exit Function
Hell:
MsgBox "Error-No.: " & Err.Number & vbNewLine & vbNewLine _
& "Description: " & Err.Description _
, vbCritical, "Fehler"
End Function
I tried to ask google, but I could not find any working results.
I really hope anybody here can point me to a solution.

Regards

Run Time Error '1004' General ODBC Error.
I customised a downloadable Macro in Excel sheet that is meant to connect to my Oracle database to do some charting.

Here is an excerpt of the code where the runtime error occurs in line ".Refresh" (second end line).

As I am not a programmer, this really gives me a challenge. Anyone mind to help? Thanks in advance.

strSQl = "select timestamp,mb_used,mb_free " & _
                     "from v_ora_db_space " & _
                     "where db_status='" & strDbStatus & "' order by 1"


     With ActiveSheet.QueryTables.Add(Connection:=strDbConnection, _
                Destination:=Range("A1"), Sql:=strSQl)
                ActiveSheet.QueryTables(1).BackgroundQuery = False
               .Refresh ' run the query
            End With

Stupid Error Message (Error 1004)
OK.

I am running a macro, and I keep coming across the same error. Here is the sub that is causing the problems:

Code:
Sub FindRowsSysType()

If Application.Range("SvcLvl").Value = 4 Then
If Application.Range("SystemValue").Value = 5 Or _
Application.Range("SystemValue").Value = 6 Or _
Application.Range("SytemValue").Value = 10 Or _
Application.Range("SystemValue").Value = 12 Then
FindRowsATU
Else: PickSvc = MsgBox("The system type you have specified" & Chr(13) _
& "is not compatible with the service level you" & Chr(13) _
& "have chosen. Please choose a different service level." _
, vbOKOnly, "Please Pick A Different Service Level")
Application.Goto Reference:="PickSvcBox"
Exit Sub
End If
Else
If Application.Range("SystemValue").Value = 1 Then
strDetailType = "NoDetail"
ElseIf Application.Range("SystemValue").Value > 1 _
And Application.Range("SystemValue").Value < 6 _
Or Application.Range("SystemValue").Value > 7 _
And Application.Range("SystemValue").Value < 12 Then
strDetailType = "StanDetail"
ElseIf Application.Range("SystemValue").Value = 6 Then
strDetailType = "SprayDetail"
ElseIf Application.Range("SystemValue").Value = 12 Then
strDetailType = "DischDetail"
Else: strDetailType = "NoDetail"
End If
End If

FindRowsDetail

ReplaceSchedule

End Sub

The problem is, VBA runs through the first "If" statement just fine, but when it gets to the nested "IF" statement, it gives me this error:

Quote:

Run-time error '1004':

Method 'Range' of object '_Application' failed

How is that possible? It had just finished running through an "Application.Range" statement, so how is it getting hung up on the same type of statement? Please help.

Error 1004 Object Defined Error
VBA is the worst programming language I've ever seen ! I'm writing this macro for Exell and it keeps giving me this stupid error.
The error occures in the inner while loop, or at least that is where the debugger is reffering me to. I can't see what can be wrong. It is just a simple macro and I've written much more complecated things in C++.
Can someone help ??
Thanks in advance

Code:

Sub FindDups()
Dim current As Integer
Dim NextZip As Integer

current = NextZip = 2
NextZip = NextZip + 1

While ((current < 20717))
While (ActiveSheet.Cells([current], 3).Value = ActiveSheet.Cells([NextZip], 3).Value)
ActiveSheet.Cells([NextZip], 7).Value = 1
NextZip = NextZip + 1
Wend
current = NextZip
NextZip = NextZip + 1
Wend
End Sub

Error 1004
hi!
this is my problem: When I try to open a excel chart twice via vb the error 1004 is shown. When i open one chart it works, but i have to restart the program.

I found the source-code how to open a excel chart in a book, but it doesn't work:
(starts with an command button)
'-------------------------------------------------------------------

dim sh as worksheet
dim ex as excel.application
set wo = excel.application.workbooks.add
set sh = wo.activesheet

with sh
..."input data for the chart"
end with

ex.charts.add <- here the error is shown in the debug mode

ex.activechart.setsourcedata _
source:= ex.sheet("Tabelle1").range("A4:B7") plotby:=xcolumns

with ex.activechart
..."set the title for the chart"
end with

ex.visible=true
ex.activewindow.activate

'----------------------------------------------------------------------

i hope you can help me
benno344

Error 1004
Hi everyone. I'm getting a very annoying error message saying 'Cannot change part of a merged cell'.

Can anyone tell me what is wrong with my code?

Code:
Public strNewFileName As String
Option Explicit

Sub OldfileToNewfile()

Dim NewWB As Workbook
Dim WSName As String
Dim RngAddress As String
Dim CopyRange As Range
Dim PasteRange As Range
Dim sFilePath As String

sFilePath = "C:cassonsfamily budget calculatormy budgetsmy budget.xls"

Set NewWB = Workbooks.Open(sFilePath)

'Copy data from the 'Housing' sheet
WSName = "Housing"
Set CopyRange = NewWB.Worksheets(WSName).Range("A1:A23")
Set PasteRange = ThisWorkbook.Worksheets(WSName).Range("A1:A23")
PasteRange.Value = CopyRange.Value
Call CopyRange.Copy(Destination:=PasteRange)<<<<< bugs out here
Exit Sub

Thanks

Error Msg 1004 Help !!!!!!!!!!!!!!
I am running some large code and i am getting the following error message - 1004 - method "Range of objuect_" Global failed

the code prior to falling down is below;

xl.Range("A2").Select
xl.ActiveCell.FormulaR1C1 = "=TRIM('ORIGINAL venmast'!RC)"

this is the culprit below...........

xl.Selection.AutoFill Destination:=Range("A2:CE2"), Type:=xlFillDefault

Please help if you can, i greatly appreciate it. With this fix i should complete the application.

Error 1004 In VBE
I am developing an application in Excel using VBE where I want to call a module everytime I want to show a different WorkSheet. For example:

In a Private Sub I have set newName = "MenuSheet", where newName has been declared as Global in a module I call modGlobal. Then I call a module named chngSheet where I am attempting to set Sheets(newName).Visible = True. But I get the following 1004 Error:

Code:
Method 'Sheets' of object '_Global' failed.

When I Print newName in the Immediate window, it returns MenuSheet.

Any ideas as to what the problem is?

Cheers

Stan

Run-time Error '1004'
Help!

I keep having nightmares with VBA for Excel, in particular with the above error message. It's always to do with defining my objects but I can never work out what I'm doing wrong. The hair pulled out of my head is now covering my keyboard and I am about to do serious violence to the next person in my office who coughs.

The following code is for a button embedded on a worksheet and therefore not operating through a module, (this seems to be critical though I've no idea why):

Private Sub refresh_Click()
whtMatchKey.Activate
Application.Calculation = xlCalculationManual

Dim lonLastrow As Long
Dim intLastcol As Integer

lonLastrow = whtpastedata.Range("A65000").End(xlUp).Row
intLastcol = whtpastedata.Range("AZ1").End(xlToLeft).Column

Selection.Range(Cells(2, 1), Cells(2, intLastcol)).Copy
Selection.Range(Cells(3, 1), Cells(lonLastrow, intLastcol)).PasteSpecial Paste:=xlPasteAll
Selection.Range(Cells(3, 1), Cells(lonLastrow, intLastcol)).FillDown

The second I get to the copy statement I get the runtime error. I've tried re-defining it as ThisWorkbook....etc, activating the relevant sheet earlier, activating the workbook....

Out of ideas and soon, out of hair. Any help gratefully received.

cheers

1004 Error 'Global'
Code:
Private Sub mnuAlphabetically_Click()
Dim MyXLApp As New Excel.Application
Dim strSheetName As String

MyXLApp.Workbooks.Open FileName:=App.Path & "components.xls"
MyXLApp.Worksheets("ingredients").Activate

MyXLApp.Range("A1").Select ' Code that alphabetizes the list
MyXLApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

MyXLApp.DisplayAlerts = False
MyXLApp.ActiveWorkbook.Save
MyXLApp.Workbooks.Close ' Close Excel
MyXLApp.DisplayAlerts = True
MyXLApp.Quit
Set MyXLApp = Nothing
End Sub

I get the error the second time I run this procedure. The first time it runs perfectly then the second time it has the error at the line:

Code:
MyXLApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Any ideas on what is wrong and how to fix it.

Thanks for the help.

Run-time Error '1004':
Hi

I am trying to create a macro that will look for duplicate values in two different worksheets. I have come to a point now, when I don't understand anything of what is happening.

My code now looks like this:

Public Sub RunWash()
'Definitions

Dim FileNum As Integer
Dim HridWash As String
Dim LookupCell As Variant
Dim ColInxNum As Integer
Dim RowInx As Long
Dim CellID As Long

'BaseLine
Dim BL_HRID As String
Dim BL_LastName As String
Dim BL_FirstName As String
Dim BL_OrgCode As String
Dim BL_AllRange As Range
Dim BL_HRIDRange As Range
Dim BL_LNRange As Range
Dim BL_FNRange As Range
Dim BL_OCRange As Range
Dim BL_Result As Range

'PS8
Dim PS_HRID As String
Dim PS_LastName As String
Dim PS_FirstName As String
Dim PS_OrgCode As String
Dim PS8_AllRange As Range
Dim PS8_HRIDRange As Range
Dim PS8_LNRange As Range
Dim PS8_FNRange As Range
Dim PS8_OCRange As Range

Application.ScreenUpdating = False
'On Error GoTo EndMacro:
'FileNum = FreeFile

ColInxNum = 5
RowNdx = 2
LookupCell = Worksheets("Baseline").Cells(RowNdx, 1)
CellID = Worksheets("Baseline").Cells(RowNdx, 5)

'Baseline
BL_HRID = Worksheets("Baseline").Cells(RowNdx, 1).Value
BL_LastName = Worksheets("Baseline").Cells(RowNdx, 2).Value
BL_FirstName = Worksheets("Baseline").Cells(RowNdx, 3).Value
BL_OrgCode = Worksheets("Baseline").Cells(RowNdx, 4).Value

Set BL_AllRange = Range(Worksheets("Baseline").Cells(2, 1), Cells(2355, 4))
Set BL_HRIDRange = Range(Worksheets("Baseline").Cells(2, 1), Cells(2355, 1))
Set BL_LNRange = Range(Worksheets("Baseline").Cells(2, 2), Cells(2355, 2))
Set BL_FNRange = Range(Worksheets("Baseline").Cells(2, 3), Cells(2355, 3))
Set BL_OCRange = Range(Worksheets("Baseline").Cells(2, 4), Cells(2355, 4))
Set BL_Result = Range(Worksheets("Baseline").Cells(2, 5), Cells(2355, 5))

'PS8
PS_HRID = Worksheets("PS8").Cells(RowNdx, 1).Value
PS_LastName = Worksheets("PS8").Cells(RowNdx, 2).Value
PS_FirstName = Worksheets("PS8").Cells(RowNdx, 3).Value
PS_OrgCode = Worksheets("PS8").Cells(RowNdx, 4).Value

Set PS8_AllRange = Range(Worksheets("PS8").Cells(2, 1), Cells(1907, 5))
Set PS8_HRIDRange = Range(Worksheets("PS8").Cells(2, 1), Cells(1907, 1))
Set PS8_LNRange = Range(Worksheets("PS8").Cells(2, 2), Cells(1907, 2))
Set PS8_FNRange = Range(Worksheets("PS8").Cells(2, 3), Cells(1907, 3))
Set PS8_OCRange = Range(Worksheets("PS8").Cells(2, 4), Cells(1907, 4))

Do While Worksheets("Baseline").Cells(RowNdx, 1) <> "#"

HridWash = Application.WorksheetFunction.VLookup(LookupCell, PS8_AllRange, ColInxNum, False)
MsgBox HridWash

RowNdx = RowNdx + 1
CellID = CellID + 1
Loop

'EndMacro:
'On Error GoTo 0
'Application.ScreenUpdating = True
'Close #FileNum

End Sub

The debugger is stopping on the line:

Set PS8_AllRange = Range(Worksheets("PS8").Cells(2,1), Cells(1907, 5))

And I get the following error message:

"Run-time error '1004':
Method 'Range' of object '_Global' failed"

Why do I get this message, and why don't I get the same message on the same type of lines that is above this one?

Does it have anything to do with Globals?

I hope someone will be able to help me with this.

PS. The code is not finished, and I guess it won't be working as it is, so any other comments on my code would be greatly appreciated.

PPS. Thanks for all help.

Run Time Error 1004
Ok, no idea why this is happening... Here is the code...

Code:
col = (TestNum * 2) + 1
row = TestNum + 25

Sheets("Charts").Activate
'Print out close point and pass fail on chart sheet here...
Cells(row, col).Value = Status & ClosePoint

Now, I can print out the values by using this..
Range("A1").value = Status & ClosePoint

..however, any time I use Cells(row,col).value I get the RTE 1004, Method 'Cells' of object '_Global' failed... anyone have any clues? Thanks...

Run-time Error'1004'
Hi All,

I am getting error "Run-time Error'1004' Method 'Cells' of Object'_Global' failed" if i am running the same file second time.
I am getting the Error in code in line "If WorksheetFunction.CountA(Cells) > 0 Then"

Here is the code:-
Set ExcelApp1 = New Excel.Application
Set ExcelWorkbook = ExcelApp1.Workbooks.Open(strFileName)

'Set excelsheet = ExcelApp1.Sheets(1)

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If

Please help me in finding the error.
Thanks & Regards,
Deepti

Run-time Error 1004
I copy data to the clipboard and then run this Macro:
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub
End up with the error and PasteSpecial Method of Class Range failed. I want to skip the movements of Edit, PasteSpecial, Transpose, OK. The ranges are always different.

tjn

Run Time Error 1004-why?
Range("Calc!B5:B12,B19:B21,B24,B28:B31,C2831,C19:F21").ClearContents

Gives me the run time error 1004, "Method 'Range' of object '_Global' failed"

What's wrong? It worked for a while, and then started giving me this error, while I changed nothing in those cels or the code related to the function.

Thanks,

JG

Another Autofill 1004 Error
I have to take the formula in cell E5 and copy it down column E as many rows as there are in column D. For example if the data in column D was as follows:
D5 = 8610
D6 = 8612
D7 = 8614
D8 = 8616

then i would need to copy the fomula from cell E5 to E8.

So far the code i have is :

Code:
Range("E5").AutoFill Destination:=Range("E5:E" & Range("D5").End(xlDown).Row)

when i run the code i get the error 'Autofill method of range class failed' (runtime error 1004)

I looked at some other threads but they didnt seem to help me with my problem. I would really appreciate any input or advice anyone has. Thanks.

SumIF Error 1004
Hi,
I am having a horrible time at tracking this down, but basically SumIf is not working within VB6.

I get Run-time error 1004
Unable to get the SumIF property of the WorksheetFunction Class

Code:
If sID = "050" Then
sTemp = "O" & iTop & ":O" & iBottom
dTemp = excel.WorksheetFunction.SumIf(wDataSource.Range(sTemp), ">0") ' Line that causes Runtime error 1004
.Cells(iCurrentRow, CI_StartColumnTotal).Value = dTemp 'WorksheetFunction.SumIf(wDataSource.Range(sTemp), ">0")
Else
.Cells(iCurrentRow, CI_StartColumnTotal).Value = WorksheetFunction.Sum(wDataSource.Range("O" & iTop & ":O" & iBottom))
'Sum works fine
End If
I am using VB6, and Excel 2K, and this is writing code inside a COM add-in for Excel.
Any help would be appreciated.

K

Run Time Error 1004
I get a run time error 1004. I am attaching the macro, template and source file. Can anyone help????thanks.

1004 Run Time Error
Hello, well the frustration lead me to seek help again. Im writing a VBA macro to copy data from one excel file and move it to another. Im getting an error when i try to select a range of data. All i want to do is move an known range of data from an unknown location in a spreadsheet and paste it toanother file. I have been working on this for about in total 3 weeks with shall we say mixed results. If anyone could offer alittle help it would be much aprechiated.

This the section the error occurs

Code:
ERROR OCCURES HERE
Range(Cells(int_top, 3), Cells(int_bot, 118)).Select
Selection.Copy

The whole macro looks like so

Code:
Private Sub CommandButton1_Click()
Dim int_row As Integer
Dim int_x As Integer
Dim int_bot As Integer
Dim int_top As Integer

Application.DisplayAlerts = False
Workbooks.Open Filename:="C: empTEST.XLS"
ActiveWorkbook.SaveAs Filename:="C: empDataHolder.XLS", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Windows("DataHolder.xls").Activate
Sheets("Test").Select

int_x = 0
int_row = 1

While int_x <> 10

If ActiveSheet.Cells(int_row, 5).Value = "" Then
int_bot = int_row - 1
int_top = int_row - 98
int_x = 10
Else
int_row = int_row + 1
End If
Wend
int_bot = int_bot - 1

'ERROR OCCURES HERE
Range(Cells(int_top, 3), Cells(int_bot, 118)).Select
Selection.Copy
Windows("Test Check.xls").Activate
Sheets("RawData").Select
ActiveSheet.Range("C113").PasteSpecial
Windows("DataHolder.xls").Close
Sheets("Results").Select

End Sub

Ducky
Thermal Testing Engineer

Run-time Error ‘1004’
Hi

I'm having trouble assigning a 'Macro' to 'Data>>>Form'.

I have a set of data in the spreadsheet which i have named. However, when i record the macro i cannot click stop where i would like the macro to stop at, which would be where the form appears etc...the code that im using at the moment is shown below:

Sub mcrAddANewCustomer()
'
' mcrAddANewCustomer Macro
' Macro recorded <date> by <name>
'

'
Sheets("Customer Details").Select
Range("A6").Select
ActiveSheet.ShowDataForm
Range("A35").Select

End Sub

--------------------------------------------------------------------
The line which 'Flags' an error in the VBA code is the following:

"ActiveSheet.ShowDataForm"

The error which i receive from that is:

[Run-time error ‘1004’:
ShowDataForm method of Worksheet class failed]

Thxs...any help would be appreciated

Run-time Error 1004 , Help Please
Hello,
please help my problem, VB and Excel automation, run-time errors 1004 method Save As.

Source code:

Private Sub Command1_Click()
Dim myexcel As Object, myWb As Object
Set myexcel = CreateObject("excel.Application")
Set myWb = myexcel.Workbooks.Open("c:jfxpristroje.xls")
myexcel.Visible = True
myexcel.DisplayAlerts = False
myWb.Worksheets("sheet1").Delete
myexcel.DisplayAlerts = True
myWb.SaveAs filename:="C:jfxPRISTROJE1.xls", fileformat:=xlCSV
myexcel.Quit
End Sub

VB version 6.0
Excel version (include XP office)

Thanks JFX

Run Time Error '1004'
Hi there,

When running a Excel based invoice, when i some to copy the infomation from one cell into another on a different sheet i come across the following error;

Run-Time Error '1004'

Application-Defined or Object-Defined Error

The code that works up untill the error is;
Public Sub bus()

Workbooks("Assingment u10 1-2").Activate
Worksheets("sheet1").Select
Range("c9").Select

invoiceno = InputBox("Invoice Number:", "Automated Invoice")
ActiveCell.Formula = invoiceno
ActiveCell.Offset(1, 0).Select

Workbooks("Assingment u10 1-2").Activate
Worksheets("sheet1").Select
Range("c11").Select

soldby = InputBox("Sold By:", "Automated Invoice")
ActiveCell.Formula = soldby
ActiveCell.Offset(1, 0).Select

Workbooks("Assingment u10 1-2").Activate
Worksheets("sheet1").Select
Range("c10").Select

soldto = InputBox("Sold to:", "Automated Invoice")
ActiveCell.Formula = soldto
ActiveCell.Offset(1, 0).Select

*
Worksheets("sheet3").Select
Range("f9").Select

down:

ActiveCell.Offset(1, 0).Select

If (ActiveCell.Formula = soldto) Then
ActiveCell.Offset(0, 1).Select
ActiveCell = ActiveCell + 1

ElseIf (ActiveCell.Formula = "") Then
ActiveCell = soldto
ActiveCell.Offset(0, 1).Select
ActiveCell = ActiveCell + 1

Else
GoTo down
End If

*

I know the error is somwhere between the two *'s but where or what it i don't know.

Any Help would be greatly appreciated

Regards

Womble

Run-time Error 1004
Hi,
I m using vb6 and linking it with excel, when the variables Rows and Cols are used outside the loop it works fine, but when used inside it gives error 1004
please help
Public x1 As Excel.Application
Set x1 = New Excel.Application
x1.Workbooks.Open ("F:aheenVacantABCVacancies.xls")
Do While Not rsQpostCreation.EOF
Rows = FindRow(rsQpostCreation.Fields(4))
Cols = FinCol(rsQpostCreation.Fields(5))
x1.Sheets("Classified (with Pool) for pri").Cells(Rows, Cols).Value = rsQpostCreation.Fields(6)
rsQpostCreation.MoveNext
Loop

Run-time Error 1004
Hi, I have been having problems running a macro I wrote to automate the analysis of input into a form in Excel. The program only ran once and since then it had been showing "Run-time Error 1004: Application or object defined error!"
The error line is:

Worksheets(x).Cells(nu, al).Value = nves

and my entire code is:

Code:
Option Explicit
Dim Cnt1 As Byte, Cnt2 As Byte, al As Byte, nu As Byte, al1 As Byte
Dim al2 As Byte, al3 As Byte, x As Byte
Dim TermName As Variant, TermValue As Variant, NamValue As String, MonthEntrd As Variant
Dim MonthComp As Variant, NatValue As String
Dim NrtValue As Variant, GrtValue As Variant, DestExport As String, TnnValue As Variant
Dim BrrlValue As Variant, Month As Variant, nves As Byte

Private Sub Form_Load()
tb_terminal.Text = " "
tb_nameVessel.Text = " "
tb_monthEntered.Text = " "
tb_monthCleared.Text = " "
tb_nationality.Text = " "
tb_nrt.Text = " "
tb_grt.Text = " "
tb_destExport = " "
tb_tnngTonnes.Text = " "
tb_tnngBarrels.Text = " "
End Sub

Private Sub cb_enter_Click()
TermValue = Array("QUA-IBOE", "FORCADOS", "ESCRAVOS", "BRASS", "PENNINGTON", _
"BONNY OFFSHORE", "ANTAN", "ODUDU", "IMA", "UKPOKITI", "EA OFFSHORE", _
"ABO", "YOHO", "OKONO", "OBE", "OKWORI", "BONGA", "ERHA")
Month = Array("JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", _
"AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER")
TermName = tb_terminal.Text
NamValue = tb_nameVessel.Text
MonthEntrd = tb_monthEntered.Text
MonthComp = tb_monthCleared.Text
NatValue = tb_nationality.Text
NrtValue = tb_nrt.Text
GrtValue = tb_grt.Text
DestExport = tb_destExport.Text
TnnValue = tb_tnngTonnes.Text
BrrlValue = tb_tnngBarrels.Text

If tb_tnngTonnes.Text = " " Then 'Converts Barrel Value to Tonnes in Marine Mutual
Converter 'Returns
End If
For Cnt1 = 0 To 17 'Iterates Through the Terminal Names
If Cnt1 >= 0 And Cnt1 <= 3 Then 'Picks the appropriate worksheet
x = 1
ElseIf Cnt1 >= 4 And Cnt1 <= 7 Then
x = 2
ElseIf Cnt1 >= 8 And Cnt1 <= 11 Then
x = 3
ElseIf Cnt1 >= 12 And Cnt1 <= 15 Then
x = 4
ElseIf Cnt1 >= 16 And Cnt1 <= 17 Then
x = 5
End If
If TermName = TermValue(Cnt1) Then
al = 2
ElseIf Cnt1 = Cnt1 + 1 Then
al = al + 4
End If
For Cnt2 = 0 To 11 'Checks through the 12 months
If MonthComp = Month(Cnt2) Then
nu = 4
ElseIf Cnt2 = Cnt2 + 1 Then
nu = nu + 1
ElseIf nu = 7 Then 'These controls jumps the
nu = 8 'Quarterly rows
ElseIf nu = 11 Then
nu = 12
ElseIf nu = 15 Then
nu = 16
ElseIf nu = 18 Then
al = al + 1
End If
nves = 1
al1 = al + 1
al2 = al + 2
al3 = al + 3
Worksheets(x).Cells(nu, al).Value = nves
Worksheets(x).Cells(nu, al1).Value = NrtValue
Worksheets(x).Cells(nu, al2).Value = GrtValue
Worksheets(x).Cells(nu, al3).Value = TnnValue
Next Cnt2
Next Cnt1
End Sub

Private Sub Converter()
TnnValue = BrrlValue / 7.2
End Sub

I also suspect that the next three lines after the problematic line would return the same error. help me out please.

Run-time Error 1004
Hi everyone,

I have a program (exe file) that works on one computer (comp1).
But it doesn't work on another computer (comp2).

When I run the program (on comp2) I get the following error...
"Run-time error 1004 application defined or object defined error."

So, I tried the *.vbp version (on comp2).
I got the same error and it identified the error at the last line of code (see below).

When I run the *.vbp program (on comp2) the value of "daze" is -24759.
On comp1 the value is 324 (which is correct).

So, there seems to be a problem with "daze".
In other words, when comp2 tries to work out the difference between the two dates.

But I can't work out why.

Anyone got any ideas?

Thanks

Code:
Dim daze, dayz As Integer
daze = DateDiff("d", Now, "31/12/07")
lblone.Caption = daze
Dim dx, yy
yy = 57 'Start at row 57 (i.e. row 57 = 6th Feb 2007 on the spreadsheet).
For dx = 328 To 1 Step -1 '328 because this code was written on the 06/02/07.
If daze = dx Then
dayz = yy
End If
yy = yy + 1
Next dx

lblinfo(15).Caption = Format(oXLSheet.Cells(dayz, 16), "hh:mm AMPM") 'The problem is identified here.

Run-time Error '1004'
Hello. Hope anyone could help me with 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
Range("A1:CL1").Select
Selection.Copy
ThisWorkbook.Worksheets("Database").Range("A6").PasteSpecial _
Paste:=xlPasteValues
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.

Bjorn

Error Code 1004
Why gives the blue line error code 1004.The message says something like this: "by the application or object defined error" (I don't know the exact message because I don't have the English version).
The commandbutton below which is situated on UserForm2
executes the macro 'Return_event_period2' and uses the values xend and yend (textbox values)
and rijnummerbegin,rijnummereind,kolomref in the following statement:

ActiveCell.Formula = "=AVERAGE(return_stock!" & kolomref & rijnummerbegin - 1 + x & ":" & rijnummereind + 1 - y & ")"

What is wrong with the above statement.
I added the whole code below.You don't have to look at the whole code. I made the statement which gives an error in the text a BLUE color.

Public xend As Integer
Public yend As Integer
Public rijnummerbegin as Integer
Public rijnummereind as Integer
Public kolomref as String

Private Sub CommandButton2_Click()
Dim c As Integer
Dim r As Integer
Dim lastcell As Integer
Dim lastcelladdr As String
Dim firstcelladdr As String
Dim myrange As Range
Dim SelRange1 As Range
Dim SelRange2 As Range
Dim Addr1 As String
Dim Addr2 As String
Dim Addr3 As String
Dim lengte1 As Integer
Dim lengte2 As Integer
xend = UserForm2.TextBox1.Value
yend = UserForm2.TextBox2.Value
'Get the address, or reference, from the RefEdit control.
Addr1 = RefEdit1.Value
Addr2 = RefEdit2.Value
Set SelRange1 = Range(Addr1)
Set SelRange2 = Range(Addr2)

SelRange1.Copy
Worksheets("return_stock").Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
SelRange2.Copy
Worksheets("return_market_index").Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Addr3 = RefEdit3.Value
Set myrange = Range(Addr3)
c = myrange.Cells.Count
r = myrange.Rows.Count
lastcell = (c / r) * (r - 1) + 1
lastcelladdr = myrange.Cells(lastcell).Address(rowabsolute:=False, columnabsolute:=False)
firstcelladdr = myrange.Cells(1).Address(rowabsolute:=False, columnabsolute:=False)
lengte1 = Len(lastcelladdr)
lengte2 = Len(firstcelladdr)
rijnummerbegin = Right(firstcelladdr, (lengte2 - 1))
rijnummereind = Right(lastcelladdr, (lengte1 - 1))
kolomref = Left(firstcelladdr, 1)

Call Return_event_period2
Unload Me
End Sub

Sub Return_event_period2()
Dim d As Integer
Sheets("return_event").Select
Range("B8").Select

For y = 1 To UserForm2.yend
For x = 1 To -UserForm2.xend
Cells((7 + x + (-1 * UserForm2.xend) * (y - 1)), 2).Select
ActiveCell.Formula = "=AVERAGE(return_stock!" & kolomref & rijnummerbegin - 1 + x & ":" & rijnummereind + 1 - y & ")"
Next x
Next y

d = Worksheets("niet_voor_gebruiker").Range("A1").Value
Range(Cells(8, 2), Cells(7 - UserForm2.xend * UserForm2.yend, 2)).Select

Selection.AutoFill Destination:=Range(Cells(8, 2), Cells(7 - UserForm2.xend * UserForm2.yend, d)), Type:=xlFillDefault
End Sub