Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Vb runtime error 1004 Results

Hi I have a problem with adding 2 charts to an existing worksheet in VBA via worksheet.chartobjects.add routine
first one is fine and does what I want. The problem starts when I add the second chart, an error message appears: something like the HasTitle-property can not be set, runtime error '1004'

Can anyone help, please?

the code goes like that


	VB:
	
 
Dim wsheet As worksheet 
Dim wchart1 As chartobject 
Dim wchart2 As chartobject 
 
 
Set Wsheet = ActiveWorkbook.Sheets.Add(after:=Worksheets  Worksheets.Count)) 
Wsheet.Name = "DATA" 
Wsheet.Activate 
 
 
Set WChart1 = .Wsheet.ChartObjects.Add(700, 30, 900, 400) 
WChart1.Activate 
WChart1.Chart.HasTitle = True 
 
WChart1.Chart.ChartTitle.text = "Chart 1" 
WChart1.Chart.ChartType = xlXYScatterLines 
 
Set WChart2 = Wsheet.ChartObjects.Add(700, 30, 900, 400) 
WChart2.Activate 
 
WChart2.Chart.HasTitle = True 
WChart2.Chart.ChartTitle.text = "Chart 2" 
WChart2.Chart.ChartType = xlXYScatterLines 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have an Excel application that I developed in Excel 2003. I bought a seperate PC to test the application under Excel 97. I have loaded Office 97 onto that 2nd PC. The code goes to debug in one area. So, I created a new blank workbook in Excel 97 with just the code that causes the runtime error 1004. The code is simply:

	VB:
	
 Test() 
    Application.calculation.xlcalculationmanual 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This simple code succesfully runs when I execute it from Macro Run. However, if I have another procedure in the same module call Test, it goes to debug with a 1004 error. Also, if I create a simple command button that calls Test, it goes to debug 1004 error.

Does anyone know if this is an Excel 97 VBA known problem and is there any work around? Is there any published files list of Office 97 VBA files? The 2nd PC came with Office 2003 trial and MS Works but I uninstalled those programs before installing 97 and wonder if 97 is accessing some later version VBA files that are incompatible.
Thanks in advance to anyone that can help.
Mike B.

I'm writing a code that takes several pivot tables and create a new one with some specific information. At the end, I add some rows for totalling and format the new table.
What is happening is that, while I was writing the code and running it using the visual basic editor, it run correctly.
However, if I run it from the pressing the command button, I receive an error:
"Runtime error 1004: Select Method of Range class failed".

When I press debug, it goes to the following code:
# Worksheets("report").Cells(nRow, nW).Select
#

After the program fails, it cannot be run from visula basic editor.. However, if I close Excel and open it again and go directly to the VB editor and run the code, it runs without problem.

I have several codes that are not running correctly (or having the same error message). They are:
# For nW = 1 To 2
# Worksheets("report").Cells(nRow, nW).Select
# With Selection.Interior
# .ColorIndex = 36
# .Pattern = xlSolid
# .PatternColorIndex = xlAutomatic
# End With
# Next

and also:

#cRange = "C5:" & "H" & Mid(Str(nRow + 1), 2)
#Worksheets("report").Range(cRange).Select
#Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
#With Selection.Font
# .Name = "Arial"
# .Size = 8
# .Strikethrough = False
# .Superscript = False
# .Subscript = False
# .OutlineFont = False
# .Shadow = False
# .Underline = xlUnderlineStyleNone
# .ColorIndex = xlAutomatic
#End With

I really don't know why there is an error there. To validate the code I also recorded a macro and reviewed the generated code...

Any idea?

Thank you for your help

Hello Team,

I've been trying to used the macro below to filter my pivot table based on the cell value from another sheet (code below) but I'm receiving the runtime error 1004 ("Unable to set the CurrentPage property of the PivotField class").


	VB:
	
 
Sub macro8() 
     
    Dim PT As PivotTable 
     
    Workbooks("Script Productivity Clarify.xls").Worksheets("Pivot Customer").Activate 
    Set PT = Sheets("Pivot Customer").PivotTables("PivotTable4") 
    PT.PivotFields("User").CurrentPage = Sheets("Pivot Names").Range("A9").Value 
    Set PT = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've checked that I've correctly named the sheets and pivot in the code and all is fined.

Could you please check? Do I have to add a refence in VB for this to work?

Thanks and Regards,

Jeremie.

Hello,
I'm trying to import data from delimited text files without Excel saving the query or data connection. I used the Macro Recorder during the manual import, and unchecked the default "Save query definition" and selected "overwrite..." from the Import Data --Properties--External Data Range Properties dialog box:
Capture.jpg

	VB:
	
 ImportDataWithUI() 
     
     ' ##############   BEGIN DATA IMPORT ##################
     ' Import overwrites cell range without inserting new columns
     'Get the file name with a UI from
     'Walkenbach's Sub GetImportFileName() page 410
     
    Dim Filt As String 
    Dim FilterIndex As Integer 
    Dim Title As String 
    Dim FileName As Variant 
     
     '   Set up list of file filters
    Filt = "Text Files (*.txt),*.txt,(*.csv),*.csv,(*.dat),*.dat," & _ 
    "All Files (*.*),*.*" 
     
     '   Display *.* by default
    FilterIndex = 1 
     
     '   Set the dialog box caption
    Title = "Select a File to Import" 
     
     '   Get the file name
    FileName = Application.GetOpenFilename _ 
    (FileFilter:=Filt, _ 
    FilterIndex:=FilterIndex, _ 
    Title:=Title) 
     
     '   Exit if dialog box canceled
    If FileName = False Then 
        MsgBox "No file was selected." 
        Exit Sub 
    End If 
     
     '   Display full path and name of the file
     '    MsgBox "You selected " & FileName
     '
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;" & FileName _ 
        , Destination:=Range("$A$1")) 
        .Name = FileName 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlOverwriteCells 
        .SavePassword = False 
        .SaveData = False 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 437 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = False 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
         
         
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Unfortunately, the code recorded fails to remove the query definition. So I searched around an found two subs that should do the trick, but don't:


	VB:
	
 
Dim Sh As Worksheet, xNazwa As Object 
Dim xConect As Object 
 
For Each xConect In ActiveWorkbook.Connections 
    If UCase(xConect.Name) Like "*" Then xConect.delete 
Next xConect 
 
For Each Sh In ActiveWorkbook.Worksheets 
    For Each xNazwa In Sh.Names 
        xNazwa.delete 
    Next xNazwa 
Next Sh 
 
 'From http://www.ozgrid.com/forum/showthread.php?t=63309
Dim ws As Worksheet 
Dim qt As QueryTable 
For Each ws In ThisWorkbook.Worksheets 
    For Each qt In ws.QueryTables 
        qt.delete 
    Next qt 
Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
You see, I need to make a table from this data (bogus example file is attached) and if there is a data connection or a query, Excel throws a runtime error 1004 (table cannot intersect query). Once I get the data in, I run the following code to get the size of the data range (where the header row is, where the data rows start and end, and the last column). This code works until I turn it into a table, where things have ground to a halt. Here's the last part of the code:

	VB:
	
 Range 
Dim DataHeaderRow As Integer 
Dim DataRowStart As Integer 'for starting data calculations
Dim DataRowEnd As Long 
Dim DataColEnd As Integer 'this is the last column of imported data
 
Application.GoTo Reference:="R1C1" 
Selection.End(xlDown).Select 
Selection.End(xlDown).Select 
DataHeaderRow = Selection.Row 
Selection.End(xlDown).Select 
DataRowEnd = Selection.Row 
Selection.End(xlToRight).Select 
DataColEnd = Selection.Column 
 
DataRowStart = DataHeaderRow + 1 
 
Range("E1") = "Data Header Row" 
Range("E2") = DataHeaderRow 
Range("E3") = "Last Data Row" 
Range("E4") = DataRowEnd 
Range("E5") = "Last Data Column" 
Range("E6") = DataColEnd 
 
 'Automatic sizing of column widths, selecting like ctrl+shift+*
ActiveCell.CurrentRegion.Select 
 
 'Auto-size columns
Selection.Columns.AutoFit 
 
 'create a table from the imported data and give it a name
 
ActiveSheet.ListObjects.Add(xlSrcRange, , xlYes).Name = "ImportedData" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
There was only one post in ozgrid that I found with the same problem but there was no solution:
http://www.ozgrid.com/forum/showthre...ht=import+text

I'll be grateful for any suggestions. I hope the explanation and breakdown is clear.

Hello,

I am new to the site and I was hoping someone might understand an error I am getting. I know it has to do with the limits of specialcells in my search, but I'm not sure of the best way to fix it.

Here is the code:


	VB:
	
 Sheets("Scrap") 
    For h = 0 To Form_Fields.SDate_Combo.ListCount - 1 
        SRDate = Replace(Form_Fields.SDate_Combo.List(h), ".", "/") 
        ChkDate = Form_Fields.SDate_Combo.List(h) 
        If CDate(SRDate) >= CDate(SDate) And CDate(SRDate)

Hi,

I am somewhat new to coding with VBA and I bet there is a very simple solution to this but I couldn't find it.

I am writing a macro that needs to scan my table and depending on some conditions copy a row (make a second copy of this row below it).

My data is formatted as a table (using the 'format as table' button).

I am running my ActiveCell using a loop and checking my condition to determine when I need to do the copying. This works fine, my active cell is moving and I am entering my "if" statement at the proper row.

When my condition is met the problem starts. Here is what I am trying to do:


	VB:
	
ActiveCell.EntireRow.Select 
 
 'copy the row
Selection.Copy 
 
 'insert a new row below
ActiveCell.Offset(1).EntireRow.Insert 
 
 'move the active cell one row lower to the newly inserted row:
Range(ActiveCell.Offset(1, 0)).Activate 
 
 'Paste the copied row
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I get to the stage when I need to insert a row (the third statement)

	VB:
	
ActiveCell.Offset(1).EntireRow.Insert 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get 'runtime error 1004', telling me the operation is not allowed as I am attempting to shift cells in a table in the worksheet.

Well, this is exactly what I want to do! I want to shift the table one row lower and insert a new row, but I am not allowed.

Any ideas how to do it?

cheers,

Yoav

Hi, I am new to Excel VBA programming. I need to have multiple (up to 6) pivot tables on the spreadsheet with shared external data source. As a data source i have a csv file.
Also, i need to be able to load data file on demand. I have created a button on the sheet that would load File dialog and let user select whatever csv data file they need. Then the idea was to update pivot cache connection and command properties using VBA with new file name.

Here is the code :

	VB:
	
 PivotCache 
Set pc = ActiveWorkbook.PivotCaches(1) 
 
Dim ODBC_CONNECT_STRING As String 
 
 ' FullFilename is a new absolute path to selected csv file
 
ODBC_CONNECT_STRING = "ODBC;DBQ=" & FullFilename & ";" & _ 
"DefaultDir=" & FullFilename & ";" & _ 
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _ 
"DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;" & _ 
"SafeTransactions=0;Threads=3;UserCommitSync=Yes;" 
 
 'set new connection string
pc.Connection = ODBC_CONNECT_STRING 
 
Dim SQL_COMMAND As String 
Dim existingSQLCommand 
existingSQLCommand = pc.CommandText 
 ' here i parse existingSQLCommand  according to new file name
SQL_COMMAND = existingSQLCommand 
pc.CommandText = SQL_COMMAND 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now, this code works fine when i have only 1 pivot table. As soon as I added 2nd pivot table with data source shared from 1st pivot table this code gives me an error:
Runtime error '1004'.

Also, the code works fine when I have separate cache for each table, but that makes my Excel document huge.

Is what I am trying to accomplish even possible with shared cache?

Thanks for your responses.

Hello All,
I have read the rules and i appologize if the title is not appropriate. I don't know how to say this other than, this code works on my PC, but not on my co-workers!

I have my macro broken out into several subs, here is the section of one of them that i am having an issue with. I'll tried posting the whole think but it produced a blank post.

It gets hung up during the first Autofilter, in this section:


	VB:
	
Rows("1:1").Copy 
Sheets("Atlanta").Activate 
Rows("1:1").Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
 
Sheets("Oxnard").Activate 
Dim CopyRange3 As Range 
Range("A:K").AutoFilter Field:=3, Criteria1:="75" 
 
With ActiveSheet.AutoFilter.Range 
    Set CopyRange3 = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) 
End With 
 
CopyRange3.EntireRow.Copy Sheets("Atlanta").Range("A2") 
CopyRange3.EntireRow.Delete 
ActiveSheet.AutoFilterMode = False 
Application.Calculate ' Calculate!

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
right on this line I get a runtime error 1004, application defined or object defined error"


	VB:
	
 CopyRange3 = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and, when i set my cursor over "CopyRange3" it says that it equals Nothing.

Again, this works fine on my PC and it's stopping on a co-workers. The other PC has had this method before in a previous version of this same macro.

I would greatly appreciate any help or advice on trouble shooting this.

Thank you!

Hi,

I am writing an excel macro to create a timesheet. In every new sheet, I want to have for each day a first dropdown list in colomn D (done without any problem) and another one in column E which content changes depending on the choice made for the first list. I've read many forum, tried several solutions, but it's not working.

NB : if I use the formula "=INDIRECT(SUBSTITUTE(D20, " ", "")) in excel (i.e. without coding in vba, everything works), but when I try to do that in VBA i get a "runtime error '1004' : application-defined or object-defined error" and the debugger points the line


	VB:
	
 btnModifInfosRes_Click() 
    FInfosRes.Show vbModeless 
    FInfosRes.NomRes.text = Worksheets("Informations").Range("B2").Value 
    FInfosRes.PrenomRes.text = Worksheets("Informations").Range("B3").Value 
     
     
End Sub 
 
Private Sub btnOK_Click() 
    Dim chosenDate As Date, wstitre As String, ws As Worksheets, counter As Integer, nbj As Integer 
     
    chosenDate = Calendrier.Value 
    wstitre = year(chosenDate) & "-" & Month(chosenDate) 
    nbj = joursmois(chosenDate) 
    If bWorksheetExists(wstitre) = False Then 
        Sheets("FdtModele").Copy After:=Worksheets("InfosPerso") 
        ActiveSheet.Name = wstitre 
        ActiveSheet.Unprotect Password:="iptel2000" 
        Sheets(wstitre).Range("B1").Value = DateSerial(year(chosenDate), Month(chosenDate), 1) 
        Sheets(wstitre).Range("B2").Value = Worksheets("InfosPerso").Range("B2").Value 
        Sheets(wstitre).Range("B3").Value = Worksheets("InfosPerso").Range("B3").Value 
        Sheets(wstitre).Range("B4").Value = Worksheets("InfosPerso").Range("B4").Value 
        Sheets(wstitre).Range("B5").Value = Worksheets("InfosPerso").Range("B5").Value 
         
        Sheets(wstitre).Range("A1:A5").Interior.Color = RGB(179, 216, 139) 
         
        Sheets(wstitre).Range("A20").Value = DateSerial(year(chosenDate), Month(chosenDate), 1) 
        Range(Cells(20, 1), Cells(20 + nbj - 1, 1)).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlDay, Step:=1

         
        For counter = 20 To (nbj - 1 + 20) 
            Cells(counter, 3) = typejour(Cells(counter, 1)) 
            Cells(counter, 2) = joursemaine(Cells(counter, 1)) 
             
            txt = "D" & Cells(counter + 20, 4).row 
             
            With Cells(counter, "D").Validation 
                .Delete 
                .Add Type:=xlValidateList, Formula1:="=categories" 
                .IgnoreBlank = True 
                .ShowInput = True 
                .InputTitle = "" 
                .InputMessage = "choisir une catégorie" 
                .ShowError = True 
                .ErrorTitle = "Erreur:" 
                .ErrorMessage = "Cette entrée ne fait pas partie des options disponibles" 
            End With 
             
            With Cells(counter, "E").Validation 
                .Delete 
                .Add Type:=xlValidateList, Formula1:="=INDIRECT(SUBSTITUTE(RC[-1],"" "",""""))" 
                .IgnoreBlank = True 
                .ShowInput = True 
                .InputTitle = "" 
                .InputMessage = "choisir une catégorie" 
                .ShowError = True 
                .ErrorTitle = "Erreur:" 
                .ErrorMessage = "Cette entrée ne fait pas partie des options disponibles" 
            End With 
             
            Select Case Cells(counter, "C").Value 
            Case Is = "Fin de semaine" 
                Cells(counter, 4).Value = "Fin de semaine" 
                Cells(counter, 5).Value = "Fin de semaine" 
                Cells(counter, 6).Value = 0 
                Cells(counter, 7).Value = "N/A" 
                Cells(counter, 8).Value = "N/A" 
            Case Is = "Ouvrable" 
                Cells(counter, 4).Value = "Choisir..." 
            Case Else 
                Cells(counter, 4).Value = "Absence" 
                Cells(counter, 5).Value = "Férié" 
                Cells(counter, 6).Value = 7.5 
                Cells(counter, 7).Value = "N/A" 
                Cells(counter, 8).Value = "N/A" 
            End Select 
        Next 
         
        Cells(20, 6).Activate 
        ActiveSheet.Visible = True 
        ActiveSheet.Protect Password:="iptel2000" 
    Else 
        MsgBox "Cette feuille a déjà été créée, vous ne pouvez pas en ajouter une feuille pour cette semaine. Pour corriger
des informations dans cette feuille, consulter l'onglet correspondant", , "ERREUR" 
         
    End If 
    Unload FAjoutFdT 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your help.

Hi Ozgrid gurus,

I've a Macro running from a button on Worskeet ("FYI")
Problem is I keep running into Runtime error 1004 when i try to run it.
I've included the code below including comments on how it's supposed to work.
Will appreciate any help, it really has me stumped, I've tried repeated debugging and it seems fine to me but it just won't run. Thanks in advance.


	VB:
	
 RemoveDuplicates() 
     '
     ' RemoveDuplicates Macro
     ' Filter out duplicates and paste to 'FilteredList'
     '
     
     'Clear previous "Destinations Info" destinations column
    ThisWorkbook.Sheets("Destination Info").Range("B2", Range("B2").End(xlDown)).Clear 
     
     'Paste Destinations from DataInput as values into Temp Sheet
    ThisWorkbook.Sheets("DataInput").Range("D2", Range("D2").End(xlDown)).Copy 
     
    ThisWorkbook.Sheets("Temp").Range("A2").PasteSpecial _ 
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
     'Filter for duplicates
    Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True 
     
     'Copy filtered data from Temp
    ThisWorkbook.Sheets("Temp").Range("A2", Range("A2").End(xlDown)).Copy 
     
     'Paste into Destination Info
    ThisWorkbook.Sheets("Destination Info").Range("B2").Paste 
    Application.CutCopyMode = False 
     
     'Clear Data in Temp and mark as Filtered
    ThisWorkbook.Sheets("Temp").ShowAllData 
    ThisWorkbook.Sheets("Temp").Range("A2", Range("A2").End(xlDown)).Clear 
    ThisWorkbook.Sheets("Temp").Range("A1").Value = "Filtered" 
    Sheets("Destination Info").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I'm new to this and hope one of you will be able to help me it what seems to be a quite simple thing to do.

I have a spreadsheet with 2 macros in it, one for copying cells and deleting contents/comments etc.


	VB:
	
 '
Selection.Copy 
Selection.Interior.ColorIndex = xlNone 
Selection.ClearContents 
Selection.ClearComments 
Application.CutCopyMode = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and another to Paste Special (all except borders)


	VB:
	
 '
Selection.Select 
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, _ 
Operation:=xlNone, SkipBlanks:=False, Transpose:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The first macro works fine but the second macro comes up with a runtime error '1004' Paste Special method of range class failed.

This is probably a very simple thing that I'm doing wrong and hope that someone will be able to help me out.

Cheers
BigNige

Ok, I have a macro that is designed to use an inputbox to ask for a number string that represents an entry on the current page, then go to that entry on the current page, and then select various info from that row and then use a portion of that string to move to another sheet which has the name equal to that portion of the string to perform more actions. My current problem is that when I try to set a variable based on a portion of the text input into the inputbox entry it gives me: runtime error 1004: method range of object _gloabal failed. And the line it errors on is the variable definition which is based on a portion of the inputbox entry.


	VB:
	
 
Dim ChooseDate As String 
Dim Hours As String 
Dim SR As Integer 
Dim ER As Integer 
Dim SC As Integer 
Dim EC As Integer 
Dim RowVar As Integer 
Dim ColVar As Integer 
Dim found As Boolean 
MyInput = InputBox("Date-Time Number?") 
If MyInput = vbNullString Then Exit Sub 
ChooseDate = (Mid(Range(MyInput), 1, 4)) 
SR = 1 
ER = 1000 
SC = 1 
EC = 12 
found = False 
For RowVar = SR To ER 
    For ColVar = SC To EC 
        If Not found Then 
            If Cells(RowVar, ColVar).Formula = MyInput Then 
                Cells(RowVar, ColVar).Select 
                found = True 
            End If 
        End If 
    Next 
Next 
ActiveCell.Offset(0, 8).Select 
Hours = ActiveCell.Text 
Sheets(ChooseDate).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The "choosedate=" line is where it errors. It's suppose to take, as a string, the first four characters of the text input into the inputbox. Then it searches the current sheet for the entire entry, selects it, uses that row to collect more info and then moves to the sheet in the workbook that has the name equal to the first four digits of the inputbox text to do more actions. I'm sure it's a syntax error but my knowledge is at it's limits. Any assistance would be greatly appreciated.

Below is the piece of code that I'm having issues with. I've used this exact code for the same data and had no problems. I'm trying to automate some analysis and placed this code in another file and I'm now receiving a runtime error 1004 application-defined or object-defined error.


	VB:
	
rws2 = ActiveSheet.UsedRange.Rows.Count 
Redim DataArraySplit(1 To rws2, 1 To 2) 
Set StudyTimeSplit = ActiveSheet.Range(Cells(1, 1), Cells(rws2, 2)) 
For x = 1 To rws2 
    DataArraySplit(x, 1) = ((DataRange(x, 4) - SequenceStart) * 2.31481481481481E-04) + SequenceTime 
    If Application.WorksheetFunction.Round(((DataArraySplit(x, 1) - StartTime) / ExcelMin), 0) < 0 Then 
        DataArraySplit(x, 2) = Application.WorksheetFunction.RoundUp(((DataArraySplit(x, 1) - StartTime) / ExcelMin), 0) +
TimeOffset 
    Else 
        DataArraySplit(x, 2) = Application.WorksheetFunction.RoundDown(((DataArraySplit(x, 1) - StartTime) / ExcelMin), 0) +
TimeOffset 
    End If 
Next 
StudyTimeSplit.value = DataArraySplit 'this is the line that brings the error

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So far I have made certain that the ranges/arrays are not defined elsewhere, that StudyTimeSplit range is the exact size as DataArraySplit, and that data does exist in DataArraySplit.

I'm baffled. It worked before, why not now? Any help would be much appreciated.

Hi

within a large section of code I am trying to count the number of columns within a range of data. The start point I am basing on a 'the cell after the first empty cell in a row' however the code produces a Runtime error 1004: Method range of object -global failed. The snippet of code is...


	VB:
	
rangelimit2 = Range("Cells(1, rangelimit + 2)").End(xlToRight).Column 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If however I hard reference it as below the code works


	VB:
	
rangelimit2 = Range("i1").End(xlToRight).Column 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However I would prefer not to do this to make make the macro more useable elsewhere.

Any help much appreciated.

Cheers
Sam

The complete code is...


	VB:
	
 charts2() 
     
    Dim rngLabels As Range 
    Dim rngData As Range 
    Dim lngIndex As Long 
    Dim sngLeft As Single 
    Dim sngTop As Single 
     
    Dim rangelimit As Integer 
    Dim rangeuse As Integer 
     
    If ActiveSheet.UsedRange.Count < 2 Then 
         'MsgBox 1
    Else 
        rangelimit = Range("A1").End(xlToRight).Column 
        rangeuse = rangelimit - 1 
    End If 
     
    sngLeft = 100 
    sngTop = 75 
     
     
    Set rngLabels = Range("a1", Range("A1").End(xlDown)) 
    For lngIndex = 1 To rangeuse 
        Set rngData = Union(rngLabels, Range(Cells(1, lngIndex + 1), Cells(1, lngIndex + 1).End(xlDown))) 
        With ActiveSheet.ChartObjects.Add(Left:=sngLeft, Width:=327, Top:=sngTop, Height:=229) 
            .Chart.SetSourceData Source:=rngData 
            .Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="geoperformance" 
        End With 
        sngLeft = sngLeft + 100 
        sngTop = sngTop + 100 
    Next 
     
     
     
    Dim rngLabels2 As Range 
    Dim rngData2 As Range 
    Dim lngIndex2 As Long 
    Dim sngLeft2 As Single 
    Dim sngTop2 As Single 
     
    Dim rangelimit2 As Integer 
    Dim rangeuse2 As Integer 
     
    ActiveSheet.Activate 
     
    If ActiveSheet.UsedRange.Count < 2 Then 
         'MsgBox 1
    Else 
        rangelimit2 = Range("Cells(1, rangelimit + 2)").End(xlToRight).Column 
        rangeuse2 = (rangelimit2 - 1) 
    End If 
     
     
     
    sngLeft2 = 500 
    sngTop2 = 75 
    Set rngLabels2 = Range("i1", Range("i1").End(xlDown)) 
    For lngIndex2 = (rangelimit + 2) To rangeuse2 
        Set rngData2 = Union(rngLabels2, Range(Cells(1, lngIndex2 + 1), Cells(1, lngIndex2 + 1).End(xlDown))) 
        With ActiveSheet.ChartObjects.Add(Left:=sngLeft2, Width:=327, Top:=sngTop2, Height:=229) 
            .Chart.SetSourceData Source:=rngData2 
            .Chart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="geoperformance" 
        End With 
        sngLeft2 = sngLeft2 + 100 
        sngTop2 = sngTop2 + 100 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello my friends gotta tini question

I wanna shift a particular column (lets say column A) to the left, so I do the following:

	VB:
	
hojaFAC.Columns("A:A").Select 
Selection.Insert Shift:=xlToLeft 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
hojaFAC is a worksheet

BUT I get the next error when I execute my macro :
RUNTIME ERROR 1004
Select method of range class failed

Does anyone know why?
Why do I get runtime error nº 1004 all the time also for other reasons. I think it´s the only runtime error I ever jumped into. But If I ever get a runtime error It's allways number 1004.
Can anyone explain the nature of its meaning?

I get a runtime error "1004 - Select method of Range class failed" when my macro starts as follows:


	VB:
	
Sheets("7100release").Select 
Range("A2:D51").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It chokes on the Range. Seems to work fine as long as I do Range on the master sheet, not changing sheets. What gives? Thanks!

I have an Excel VBA routine which opens another Excel file as a part of the routing. For example:


	VB:
	
 
 ' Beginning of the code.
 
Workbooks.OpenText Filename:=Range("log_file").Value, Origin:=xlWindows 
 
 ' Rest of the code.

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem I run into is when the user does not have access (via Windows Active Directory) to the file located in the "log_file" range. They usually will get the old runtime error 1004. I can easily trap that error but err 1004 is used so many other times, I don't want to mistakenly trap it as something else.

Does anyone know of a way to verify and/or check if the user has read/write access to a file (or folder) before opening it?

Thx,
Mike

Hello,

I am trying to use the TREND worksheet function on a vba array. I keep getting Runtime error 1004: Unable to get the TREND property of the WorksheetFunction Class.

I can bypass the error (like it were a search with no result), but I get no result of any kind from the function.

My search of other threads yields nothing concrete, but causes a guess that it may have to be used on a worksheet range not a vba array. But that's not actually stated in any of the threads, and I find no documentation to that effect.

Is this the issue or is it something else altogether?

	VB:
	
 test() 
    Dim knowny As Variant 
    Dim knownx As Variant 
    Dim newx As Integer 
    Dim myval As Integer 
     
    knowny = Array(2, 4, 6, 8, 10) 
    knownx = Array(1, 2, 3, 4, 5) 
    newx = 6 
     'On Error Resume Next
    myval = Application.WorksheetFunction.Trend(knowny, knownx, newx) 
     '     'next error, err as normal.
     '    On Error GoTo 0
     '    Err.Clear
    Debug.Print myval 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help is appreciated.

hi all,
i want to create dynamic validation list in excel. The logic shd be like
this.
i m fetching data from SAP on the click of execute button in sheet2.
at this point of time i want to create dynamic validation list in sheet2 in
row "E". and data to be filled in the list will be fetched from sheet1.
i have added new named range in sheet1 called"mbe"(in sheet1!A)

when user will execute excel i will feel data in sheet1 cloumn A.
and i have written this code in the click of execute button :

	VB:
	
 ActiveSheet.Range("myrange") 
    .Validation.Delete 
    .Validation.Add xlValidAlertStop, , "=mbe" 
     
    .Validation.InCellDropdown = True 
    .Validation.IgnoreBlank = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

its giving runtime error "1004" (object defined error)
please help me to solve this problem

your help will be appreciated.

thanks in advance,
jigs


No luck finding an answer? You could always try Google.