Free Microsoft Excel 2013 Quick Reference

getting vba Error: 1004: Method 'Worksheets' of object '_Global' failed for one user.

Hi All,

I have a program i just released to 10 co-workers. We all have the same hardware/software (XP/Excel 2007), etc.

My program works flawlessly for all user but one.

The code in question is here:

	VB:
	
Call Log_Event("WorkBook", "Open", "User: " & Environ("USERNAME")) 
Call Log_Event("WorkBook", "Open", "UserDomain: " & Environ("USERDOMAIN")) 
Call Log_Event("WorkBook", "Open", "Path: " & ThisWorkbook.FullName) 
Call Log_Event("WorkBook", "Open", "Calling Security Check") 
Call Globals.SecurityCheck 
 'initial nav button state:
NavButtonState = "Disable" 
 'set/disable excel features
Call Log_Event("WorkBook", "Open", "Setting Application Vars") 
On Error Resume Next 
Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 
Application.ErrorCheckingOptions.NumberAsText = False 
Application.ErrorCheckingOptions.BackgroundChecking = False 
Application.WindowState = xlMaximized 
ActiveWindow.WindowState = xlMaximized 
On Error Goto errhandler 
 'initialize:
Call Log_Event("WorkBook", "Open", "Calling Dict Init") 
Call FCdictionaryManager.Initialize 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The last line fails with the 1004!

FCdictionaryManager is just another Mod (not in a worksheet). It gets called one time only, when the workbook is opened.

Any one have an idea as to why one user would get a Worksheet error when calling a Mod Sub?

Thanks in advance!


Related Results

Hi I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.

Thanks.

Sub formatcolumns()

    Columns("G:G").Insert Shift:=xlToRight
    Range("H1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("N1").FormulaR1C1 = "=(RC[-1])"
    Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"
    Columns("N:N").Interior.ColorIndex = 36
    Columns("R:R").Insert Shift:=xlToRight
    Range("R1").FormulaR1C1 = "=(RC[-1])"
    Range("R2:R").FormulaR1C1 = "=(RC[-4]/RC[-2])"
    Columns("R:R").Interior.ColorIndex = 36
    Columns("U:U").Insert Shift:=xlToRight
    Range("U1").FormulaR1C1 = "=(RC[-1])"
    Range("U2:U").FormulaR1C1 = "=(RC[-14]*RC[-2])"
    Columns("U:U").Interior.ColorIndex = 36
    Columns("AC:AC").Insert Shift:=xlToRight
    Range("AC1").FormulaR1C1 = "=(RC[-1])"
    Range("AC2:AC").FormulaR1C1 = "=(RC[-5]+RC[-4]+RC[5]+RC[-22])/(RC[1]+RC[2])"
    Columns("AC:AC").Interior.ColorIndex = 15
    Columns("AJ:AJ").Interior.ColorIndex = 35
    Columns("AN:AN").Insert Shift:=xlToRight
    Range("AN1").FormulaR1C1 = "=(RC[-1])"
    Range("AN2:AN").FormulaR1C1 = "=(RC[-4]-RC[-33])"
    Columns("AN:AN").Interior.ColorIndex = 35
    Columns("AR:AR").Insert Shift:=xlToRight
    Range("AR1").FormulaR1C1 = "=(RC[-1])"
    Range("AR2:AR").FormulaR1C1 = "=(RC[-4]/RC[-3])"
    Columns("AR:AR").Interior.ColorIndex = 35
    Columns("BB:BD").Select
    Selection.Cut
    Columns("L:L").Insert Shift:=xlToRight
    Columns("M:N").Select
    Selection.Cut
    Columns("R:R").Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("D:D").Insert Shift:=xlToRight
    Range("N2").Select
    Selection.AutoFilter
    ActiveWindow.FreezePanes = True
    Columns("AG:AG").Select
    Selection.Interior.ColorIndex = 34
    Columns("AH:AH").Select
    Selection.Interior.ColorIndex = 33
End Sub


I have the following code that keeps giving me the error message " Run-time error '1004': Method 'Range' of object'_Gloabl' Failed"

Can someone please help me.

Code:

Sub errorhandling()
Worksheets("Sheet1").Select
On Error GoTo E6:
Range("SACC6").Cells(Range("SACC6").Rows.Count, 1).Offset(1, 0).Activate
E6:
On Error GoTo Err8:
Range("SACC7").Cells(Range("SACC7").Rows.Count, 1).Activate
Err8:
End Sub

There are a lot of posts regarding run time error 1004, Method 'SaveAs' of object '_Workbook' failed. Having ready through 20 - 30 of them, none of them seem to solve my problem.

I have some VBA that worked fine in Excel 2003 that produces the above error in Excel 2007. I am trying to save a file and then save it a web addreess as follows:


	VB:
	
 Publish() 
    webaddress = "[URL]http://www..../forecasting/files/[/URL]" 
    FileDir = "[URL="file://Denasvf2b..."]Denasvf2b...[/URL] 
    Myfilename = "judy.xls" 
     
    ActiveWorkbook.SaveAs Filename:=FileDir & Myfilename, FileFormat:=1 
    ActiveWorkbook.SaveAs Filename:=webaddress & Myfilename, FileFormat:=1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Whatever fileformat I put it, the first saveas line works and the second does not. Note webaddress and filedir are full paths in my code and yes they do exist.

If I manually use save as command from the Office button and paste in the web address, whatever file name I type it says it already exists.

Greetings,

After looking in the archives, this particular problem didn't appear to be in there.

I have a cet of CommandButtons on sheet 1. The code for these buttons is in the code section of the sheet. A named range is referenced in this code. This named range is on sheet 2. Every time I try to reference this or any named range (from any other sheet), I get this "Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

The last time I had this error I was able to fix it by moving the code to Module1. I tried that here, but it did not work (I can't figure out how to call it from the sheet). CommandButton.

Here is the code from the sheet for one of the buttons:

	VB:
	
 TBEnterUp_Click() 
     
    iLast = Range("WBDate_DayLast").Value '

Workbooks.Open with CorruptLoad parameter set to xlRepairFile fails on Excel
5.0/95 file due to Chart, with Error 1004 Method 'Open' of object
'Workbooks' failed.

We have a sample Excel 5.0 (Excel 95) file that is being opened through a
Visual Basic 6.0 function on Excel 2003 COM call. The Workbooks.Open method
succeededs if CorruptLoad is set to xlNormalLoad, but it fails with Error
1004 if Corrupt Load is set to xlRepairFile.

The Excel 5.0 document has a single sheet with a Chart on it. If the file
is modified such that only the chart is removed, the Open with xlRepairFile
works. If everything else is removed from the file and the Chart remains,
the xlRepairFile Open still fails.

Any ideas on why this might be failing? Is there a limitation of Excel 2003
opening Excel 5.0 file with Charts?
Thanks for any input on this topic.

Function WorkbooksFailSafeOpen(oWorkbooks As Workbooks, strLocalInputFile As
String) As Workbook

Dim lngErrNumber As Long
Dim strErrorDescription As String

On Error Resume Next

Set WorkbooksFailSafeOpen = oWorkbooks.Open(strLocalInputFile, _
UpdateLinks:=2, _
ReadOnly:=True, _
Password:="", _
IgnoreReadOnlyRecommended:=True, _
CorruptLoad:=xlRepairFile)

lngErrNumber = Err.Number

On Error GoTo 0

' If the first open failed with Error 1004
' Method 'Open' of object Workbooks' failed
' Try the alternate Open, without xlRepairFile setting

' Note we can't seem to get the description
' "Method 'Open' of object 'Workbooks' failed"
' out of the Err.Description variable, comes out as
' "Application-defined or object-defined error"
' even though pop-up dialog displays Method 'Open'... message
' so just check against generic error number 1004

If lngErrNumber = 1004 Then
Set WorkbooksFailSafeOpen = oWorkbooks.Open(strLocalInputFile, _
UpdateLinks:=2, _
ReadOnly:=True, _
Password:="", _
IgnoreReadOnlyRecommended:=True, _
CorruptLoad:=xlNormalLoad)
End If

End Function

Hi,
I am trying to draw a graph using a Macro and I am getting "Run-time
error '1004'" Method 'Range' of object '_global' failed. Could any body
help me out in fixing this problem.

line
Range("A1:A340").Select
is giving the error.

..................Code.................

Sub swapmemory()
'
' swapmemory Macro
' Macro recorded 10/20/2003 by ibm user
'
' Keyboard Shortcut: Ctrl+q
'
Dim sDate As String
Dim sfile As String

sDate = Format(Date, "mmddyy")
sfile = "swapmemory_results." & sDate
ChDir "C:new ApplicationsTower-G"
Workbooks.OpenText FileName:= _
"C:new ApplicationsTower-G" & sfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Range("A1:A340").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(sfile).Range( _
"A1:A45"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
sfile
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Swap Memory Results -- GLITR"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"time(each unit = 5min)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kb"
End With
End Sub

Thanks in advance.

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I have an Excel spreadsheet that gives me an Error 1004 Method 'Range' of object '_Global' failed at the following line.

startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2, 1).Address

Can anyone help?

Thanks,
Bernie

When I run my macro in XL2003, I get the following Run-time error
message 'Run-time error '1004'; Method 'Export' of object'_Chart'
failed' everytime the macro runs the following line.

ActiveChart.Export Filename:=LCase(SaveName), _
FilterName:="GIF"

Anyone have any suggestions? What is the error really trying to tell
me?

Thanks,

Norm

I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to ToolsProtection is does
not give the unprotect option.

THanks

I have this macro that I have modified. I get this error

Run time error : '1004'
Method 'Range' of 'object' Global Failed

Here is the code and I have already tried changing the range name and the Variable.

Set rng = Range("Database")

I don't understand what the error wants to change.

I have a button on my spreadsheet which activates some code, it has worked perfectly for over a year but now for some reason I cannot get it to work.
When I click on the button now I get the message: Run-time error '1004' Method 'Range' of object '_Global' failed.
Apologies if this is a simply query but i do not understand VBA at all, the file is far too large to attach here but here is the relevant code:


	VB:
	
 UpDateResults() 
    Dim s As Range, i As Variant, t As Range, w As Range, _ 
    cc As Range, c As Range, w2 As Range, j As Integer, Val As Integer 
    Val = Sheet3.Range("AG1").Value 
    Set s = Range("Scores") 
    Set t = Range("Table") 
    Set w = t.Cells(1, 1).End(xlToRight).Offset(0, 1) 'first empty week
    Set w2 = t.Cells(1, 1).Offset(0, 31).End(xlToRight).Offset(0, Val) 
     
    Application.Calculation = xlManual 'turn off calculations
    If Sheet4.Range("IV1").Value = 1 Then 
        For Each cc In s 
            For j = -1 To 4 Step 5 
                Set c = cc.Offset(0, j) 
                i = Application.Match(c.Offset(0, -3), t, 0) 'get player position
                If IsError(i) Then 
                    MsgBox "Cannot locate " & c.Offset(0, -3) & " in the table." 
                Else 
                    w2.Cells(i, 1).Value = c 
                End If 
            Next j 
        Next cc 
        w2.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _ 
        SearchOrder:=xlByRows, MatchCase:=False 
         
    Else 
        For Each cc In s 
            For j = -1 To 4 Step 5 
                Set c = cc.Offset(0, j) 
                i = Application.Match(c.Offset(0, -3), t, 0) 'get player position
                If IsError(i) Then 
                    MsgBox "Cannot locate " & c.Offset(0, -3) & " in the table." 
                Else 
                    w.Cells(i, 1).Value = c 
                End If 
            Next j 
        Next cc 
        w.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _ 
        SearchOrder:=xlByRows, MatchCase:=False 
         
    End If 
     
     'w.Range("A1:A" & t.Rows.Count).Replace What:="", Replacement:="DNP", LookAt:=xlWhole, _
     'SearchOrder:=xlByRows, MatchCase:=False
    Application.Calculation = xlAutomatic 'turn on calculations
    Sheet4.Range("IV1").Value = 0 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As usual any help would be greatly appreciated
regards
Billy B

I am getting a runtime error '1004'
Method 'Range' of object 'Global' failed

Private Sub OptionButton2_Click()
Sheets("Formulas").Visible = True
Range("Formlas!B2") = "480" <-----
Sheets("Formulas").Visible = False
Sheets("Panel").Select
End Sub

when I originally wrote this code it worked, however now I get the run-time error. Does anyone know what would cause this and how I get the active object to be the Worksheet?

Thanks,
Jason

Hello

I posted a problem on here yesterday and received very useful help (here:http://www.ozgrid.com/forum/showthread.php?t=156937) I now have a new problem with the same macro but I think it is sufficiently different to need a new thread.

What I need to do is to make a chart on the current worksheet, using data from the current worksheet, without specifying which worksheet that will be. The code below worked fine until I moved the macro to the personal.xlsb thing so that it could be used in different workbooks.


	VB:
	
 ActiveSheet.ChartObjects.Add _ 
    (Left:=100, Width:=700, Top:=200, Height:=500) 
    .Chart.ChartType = xlBubble 
    .Chart.SetSourceData Source:=Range( _ 
    strDateRange & "," & _ 
    strPwinRange & "," & _ 
    strEstValRange) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(in the current test strDataRange = !$B:$B strPwinRange = !$C:$C and strEstValRange = !$D:$D)
Now, this code gives an error 1004: Method 'Range' of object '_Global' failed. To correct this I have had to do the following (adding sheet1):

	VB:
	
 ActiveSheet.ChartObjects.Add _ 
    (Left:=100, Width:=700, Top:=200, Height:=500) 
    .Chart.ChartType = xlBubble 
    .Chart.SetSourceData Source:=Range( _ 
    "'Sheet1'" & strDateRange & "," & _ 
    "'Sheet1'" & strPwinRange & "," & _ 
    "'Sheet1'" & strEstValRange) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I don't want the worksheet to have to have a specific name for the macro to run, and I thought having the ActiveWorksheet part should make that unnecessary. The original code still works in the original workbook, even when run from personal.xslb, so I don't really know what's going on.

Any help would be greatly appreciated.

We have an application written in Excel 2002 VBA that deals with
user-selected cells. The user does not select the cells in any specific
order, nor does he select them from a single column as in this case. However,
the cells must be processed in exact row/column order. We have therefor
sorted the selected cells
in that order and are now about to combine them to a Range using the
following code:

Dim rngSel As Range
Dim strRange As String
Set rngSel = Worksheets("Lines").Range(strRange)

' strRange contains the following list of cells, which are selected by the
user. In this case some of them are
' in a contiguous range:
strRange =
"U4,U5,U6,U7,U8,U9,U10,U11,U12,U13,U14,U15,U16,U17 ,U18,U19,U20,U21,U22,U23,U24,U25,U26,U27,U28,U29,U 30,U31,U32,U33,U34,U35,U36,U37,U38,U39,U40,U41,

U42,U43,U44,U45,U46,U47,U48,U49,U50,U51,U52,U53,U5 4,U55,U56,U57,U58,U59,U60,U61,U62,U63,U64,U66,U66, U67,U68,U69,U70,U71,U72,U73,U74,U77,U76,U77,U78,U7 9,U80"

The "Set Range" statement fails with message: "1004 - Method 'Range' of
object '_Global' failed".

However, if "strRange" only holds up to 65 single cell-addreses, the code
executes correct. But as soon there are 66
or more, it fails with the above error message.
(In this particular case we could set the "strRange" to "U4:U80", but that
would not help, as the user could instead
select cells such as: "B2,C3,D4,E7,D8,B9,F10.....etc.").

We have not been able to find any documentation on a limitation in the
number of individual cells you can specify in order to form a Range.

I hope someone can help ?

Thanks,
Thomas

I am using the following VBA code for a workbook that saves the user's file to a network folder that all users have access to - I have confirmed with each user. This code works perfectly on my computer but the other users are getting the Method 'saveas' of object' _workbook' failed Run Time Error.

Also, all users have Excel 2010 and Excel 2007. I have scoured the internet and forums trying to find a solution that works to no avail. Thanks in advance for your help!

Sub SaveIt()

Dim strDSO As String, strTM As String

ThisWorkbook.Save
Application.DisplayAlerts = False
'*****************
With Worksheets(1)
strDSO = .Range("C9")
strTM = .Range("D9")
End With

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="stlusregnt1RegionalEast RegionRNDAP Report" & " RNDAP Report - " & strDSO & " - " & strTM & ".xlsm", FileFormat:=52

End Sub

Hi Guys

I am getting an error runtime error 1004 method rows of object _failed in the line IR=

Please have a look at the following code.
HTML Code: 
Dim iR As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("Sheet4")
'find first empty row in database
For i = 1 To TextBox1.Value

iR = ws1.Cells(Rows.Count, 1) _       ERROR
  .End(xlUp).Offset(1, 0).Row
  'iR = 4
ws1.Cells(iR, 1).Value = Format(ComboBox1.Value & "/" & ComboBox2.Value & "/" &
ComboBox3.Value, "dd/mm/yyyy") & "_" & Time & "_" &
Environ$("computername")
Regards
Aman

We have an application written in Excel 2002 VBA that deals with
user-selected cells. The user does not select the cells in any specific
order, nor does he select them from a single column as in this case. However,
the cells must be processed in exact row/column order. We have therefor
sorted the selected cells
in that order and are now about to combine them to a Range using the
following code:

Dim rngSel As Range
Dim strRange As String
Set rngSel = Worksheets("Lines").Range(strRange)

' strRange contains the following list of cells, which are selected by the
user. In this case some of them are
' in a contiguous range:
strRange =
"U4,U5,U6,U7,U8,U9,U10,U11,U12,U13,U14,U15,U16,U17,U18,U19,U20,U21,U22,U23,U24,U25,U26,U27,U28,U29,U 30,U31,U32,U33,U34,U35,U36,U37,U38,U39,U40,U41,

U42,U43,U44,U45,U46,U47,U48,U49,U50,U51,U52,U53,U54,U55,U56,U57,U58,U59,U60,U61,U62,U63,U64,U66,U66, U67,U68,U69,U70,U71,U72,U73,U74,U77,U76,U77,U78,U79,U80"

The "Set Range" statement fails with message: "1004 - Method 'Range' of
object '_Global' failed".

However, if "strRange" only holds up to 65 single cell-addreses, the code
executes correct. But as soon there are 66
or more, it fails with the above error message.
(In this particular case we could set the "strRange" to "U4:U80", but that
would not help, as the user could instead
select cells such as: "B2,C3,D4,E7,D8,B9,F10.....etc.").

We have not been able to find any documentation on a limitation in the
number of individual cells you can specify in order to form a Range.

I hope someone can help ?

Thanks,
Thomas

Hello all,

I am trying to write a macro to do the following, I have two very large lists (10-20k) rows each which I need to sort through a match up to each other based on a reference number. My though process so far has been to make a four sheet workbook with two of the sheets containing one of these lists I have each. I the pull the first number from the first list and then compare it to every value in the second list. When it finds a match it copys the row from each sheet and pastes them in a third sheet side by side. When I initially tackled this problem I used multiple steps to accomplish this copy paste action. The code which I wrote for this I have left in the code below however it has been commented out. This multi step process did work however because of the volumn of data I have I am trying to make this macro run as fast as possible so I am now trying to do the copy paste in one command.

The line which I am receving the runtime error message from is:


When I mouse over this line during debugging it reads:

matchWS.Range(tCount, 4).Value = <Method 'Range' of object '_Worksheet' failed>

Which leads me to believe that the error is happening in the latter part of that line. What I cannot seem to figure out is why, when the exact same thing seems to me to be working fine in the first part.

I thought maybe something in the second part was returning a value which I did not expect but when I mouse over the different aspects of the second half of that statement nothing seems to be return anything odd.

deductionWS.Cells(xCount, 1) = 6132010
xCount = 2

deductionWS.Cells(xCount, 1) = 9900004
xCount = 2

Those values all match the second line of the the worksheet I am calling.

I've tried to be as specific as possible with calling these methods and making sure that I reference the worksheet which I want the method to be called for but I cannot seem to make any head way into why I keep getting this error. I'm just plain old stumped haha.

Below is the full code for my macro so far however I will admit right now it is not complete, and yes I know I need better variable names

Once I am able to figure out a better way to do the copy paste or able to fix my current method I will have to add the code to copy the data from the second list as well.

Sub myMatch()

'Variables
Dim x As Range, y As Range, list1 As Range, list2 As Range
Dim xCount As Long, yCount As Long, tCount As Long
Dim deductionWS As Worksheet, creditWS As Worksheet, matchWS As Worksheet, masterWS As Worksheet
Dim wb As Workbook
    
    Application.ScreenUpdating = False
    
    'Define workbook
    Set wb = ActiveWorkbook
    
    'Define my worksheet variables
    Set masterWS = wb.Sheets("Deductions and Credits Matched")
    Set matchWS = wb.Sheets("Claim # Matchup")
    Set deductionWS = wb.Sheets("Open Deductions")
    Set creditWS = wb.Sheets("Open Credits")
    
    'Starting Time stamp
    t = Time()
    d = Date
    masterWS.Range("M1").Value = "Started " + Str(d) + " at " + Str(t)
        
    'Populate the lists to compare
    Set list1 = deductionWS.Range("D2:D20000")
    Set list2 = creditWS.Range("E2:E20000")
              
    'Set intial values for counters
    tCount = 0
    xCount = 1
                                           
    'Matching loop every x value in list1 checked against every y value in list2 for a match
    For Each x In list1
                     
        'Reset y counter
        yCount = 1
                
        'Increment x counter
        xCount = xCount + 1
                 
        'Cycles through each y value to be compared
        For Each y In list2
                                              
            'Increment y counter
            yCount = yCount + 1
           
            'Comparison of claim numbers
            If (x.Value + 0) = (y.Value + 0) Then
            
                'Increment total number of matches counter
                tCount = tCount + 1
           
                matchWS.Range(tCount, 4).Value = deductionWS.Range(deductionWS.Cells(xCount, 1), _
                    deductionWS.Cells(xCount, 4)).Value
                
                '**********************Multi-step copy paste*********************
                
                'Selects the deduction and credit which match and copy pastes both to the matching worksheet
                'Sheets("Open Deductions").Select
                'Range(Cells(xCount, 1), Cells(xCount, 4)).Select
                'Selection.Copy
        
                'Sheets("Claim # Matchup").Select
                'ActiveCell.Select
                'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                'False, Transpose:=False
                
                'Sheets("Open Credits").Select
                'Range(Cells(yCount, 1), Cells(yCount, 6)).Select
                'Selection.Copy
                
                'Sheets("Claim # Matchup").Select
                'ActiveCell.Offset(0, 4).Select
                'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                'False, Transpose:=False
                
                'ActiveCell.Offset(1, -4).Select
                               
                '******************************************************************
                
            End If
        
        Next y
    
    Next x
    
    'Select all matched deductions and credits
    matchWS.Range(Cells(1, 1), Cells(tCount, 9)).Select
    Selection.Copy
    
    'Paste to main worksheet
    masterWS.Select
    Cells(2, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    
    'Ending time stamp
    masterWS.Range("M2").Value = "Ended " + Str(d) + " at " + Str(t)
    
    Application.ScreenUpdating = True
                     
End Sub


I have an VBA application that I've built within an .xlsm file. The following code is giving me an ongoing headache in that after it's been invoke a couple of time (or a dozen times--there's no predicting), I get an error message: "method add of object listrows failed". If I debug, the problem line is the ListRows.Add line. If I continue executing the code, I get a 1004 error message related to the same line and then Excel freezes and crashes and then restarts. I'm at a loss as to what the problem could be. I have code to flush the clipboard and the FreeIt function you see below unprotects the sheet where the ListObject resides. Also, I sometimes get the error when invoking another sub the deletes lines from the ListObject. And when I've tried to workaround the Add method by simply resizing the ListObject.Range by a single row, sooner or later I get the same error message, only it's "method resize of object range failed".

I've been up and down the forums out there and can't find a satisfactory answer.

Private Sub InsertRow()
    Dim RowNr As Integer
    Dim b As Boolean
        
    b = FreeIt()
 
    If line <> -1 Then
        RowNr = line
    Else
        RowNr = Target.Rows.Count
    End If

    ClearClipboard
    
    ThisWorkbook.Worksheets("Configurator").ListObjects("Products").ListRows.Add

    RevertIt (b)
End Sub


Error message in Excel-VBA: "1004 - Method 'Range' of object '_Global' failed"

We have an application written in Excel 2002 VBA that deals with user-selected cells. The user does not select the cells in any specific order, nor does he select them from a single column as in this case.
However, the cells must be processed in exact row/column order. We have therefor sorted the selected cells in that order and are now about to combine them to a Range using the following code:

Dim rngSel As Range
Dim strRange As String
Set rngSel = Worksheets("Lines").Range(strRange)

' strRange contains the following list of cells, which are selected by the user. In this case some of them are
' in a contiguous range:
strRange = "U4,U5,U6,U7,U8,U9,U10,U11,U12,U13,U14,U15,U16,U17,U18,U19,U20,U21,U22,U23,U24,U25,U26,U27,U28,U29,U 30,U31,U32,U33,U34,U35,U36,U37,U38,U39,U40,U41,U42,U43,U44,U45,U46,U47,U48,U49,U50,U51,U52,U53,U54,U 55,U56,U57,U58,U59,U60,U61,U62,U63,U64,U66,U66,U67,U68,U69,U70,U71,U72,U73,U74,U77,U76,U77,U78,U79,U 80"

The "Set Range" statement fails with message: "1004 - Method 'Range' of object '_Global' failed".
However, if "strRange" only holds up to 65 single cell-addreses, the code executes correct. But as soon there are 66 or more, it fails with the above error message.
(In this particular case we could set the "strRange" to "U4:U80", but that would not help, as the user could instead select cells such as: "B2,C3,D4,E7,D8,B9,F10.....etc.").

I have not been able to find any documentation on a limitation in the number of individual cells you can specify in order to form a Range.

Any help would be appreciated.

Greetings,

I keep getting this "Run-time error '1004', Method 'Range' of object '_Global' failed"

Here is the code that has the problem:

	VB:
	
 
Dim i As Long 
Dim j As Long 
Dim lDup As Long 
Dim lRow As Long 
Dim NoDupes As Collection 
Dim rRng As Range 
Dim Rng1 As Range 
Dim Rng2 As Range 
Dim Rng3 As Range 
Dim Swap1 As Variant 
Dim Swap2 As Variant 
Dim wks As Worksheet 
 
Private Sub UserForm_Initialize() 
    Call DefaultSet 
    For lDup = 1 To 3 
        Call NonDuplicatesList(lDup) 
    Next lDup 
End Sub 
 
Sub DefaultSet() 
    Set wks = ThisWorkbook.Sheets("Data") 
    Set Rng1 = wks.Range("NamedRange1") 
    Set Rng2 = wks.Range("NamedRange2") 
    Set Rng3 = wks.Range("NamedRange3") 
End Sub 
 
Sub NonDuplicatesList(lCBNum As Long) 
    Set NoDupes = New Collection 
    Set rRng = Range("Rng" & lCBNum) ' NoDupes(j) Then 
                Swap1 = NoDupes(i) 
                Swap2 = NoDupes(j) 
                NoDupes.Add Swap1, before:=j 
                NoDupes.Add Swap2, before:=i 
                NoDupes.Remove i + 1 
                NoDupes.Remove j + 1 
            End If 
        Next j 
    Next i 
     ' Load ComboBox Rowsource...
    Me.Controls("TB" & lCBNum).RowSource = NoDupes 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It does not even loop once though the original UserForm_Initialize For/Next loop.

Does anyone have any idea as to what is wrong with this code?

Any help is most welcome.

-Minitman

I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.
Private Sub Worksheet_Activate()

    Dim gotorow As Long
    
    Sheet2.Range("B3:I99").sort key1:=Sheet2.Range("D3"), order1:=xlAscending, key2:=Sheet2.Range( _
        "E3"), order2:=xlAscending, key3:=Sheet2.Range("B3"), order3:=xlAscending, _
        Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, dataoption1:=xlSortNormal, dataoption2:=xlSortNormal, _
        dataoption3:=xlSortNormal
        
    If Sheet2.Range("B3") = "" Then
        gotorow = 3
    Else
        gotorow = Sheet2.Range("B3").End(xlDown).Row + 1
    End If
    Sheet2.Activate
    Sheet2.Range("B" & gotorow).Select ' LINE WITH ERROR
    
End Sub
I appreciate any help on this. Long time searcher/first time poster.

Hi all, I am trying to write a macro that checks an entire column, find all cells that contain a certain keyword type in in a cell, and paste all of them in another sheet.
In order to identify the cells that contain a keyword I am using the Instr Function, but I am getting an error message "method range of object _global failed" and I don't know how to fix it.
Here below the entire code!

thanks all,


	VB:
	
 Button9_Click() 
     
     
     
     
    Dim i As Integer 
    Dim j As Integer 
    Dim Keyword1 As String 
     
     
    If ActiveSheet.Shapes("Check Box 6").ControlFormat.Value = -4146 Then 
        MsgBox "Audience not ticked" 
        Keyword1 = Range("D5") 
    Else 
         
        Sheets("All segments").Select 
        j = 17 
        i = 1 
        Do 
            If Cells(1, i).Text = "" Then 
                Sheets("Finder").Select 
                Range("A1").Select 
            Else 
                i = i + 1 
                 
                If InStr(1, Range(1, i).Value, Keyword1, Comparemethod.binary) > 0 Then 
                    Cells(A, i).Select 
                    Selection.Copy 
                    Sheets("Finder").Select 
                    Cells(D, j).Select 
                    Selection.PasteSpecial Paste:=xlPasteValues 
                    j = j + 1 
                    Sheets("All segments").Select 
                     
                     
                End If 
            End If 
        Loop Until Cells(A, i).Text = "" 
    End If 
     
     
End Sub 
 
[COLOR=#3E3E3E][B][/B][/COLOR] 

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


Hi everyone,

I'm getting the "method 'range' of object 'global' failed" error on the line FileName = Range("W" & n).Value. I'm not sure what it is, as I'm using this code quite succesfully on other macros. Does anyone have any ideas please?

Code:
 Sub FileCheck()
    Dim n As Long
    Dim FileName As String
        
    FileName = Range("W" & n).Value
     
    For n = 3 To 20
    If Dir(FileName) = "" Then
       MsgBox "The file does not exist"
    Else
       MsgBox "The file exists."
    End If
Next n
End Sub