Setting Range To Value Fails With 1004 Error
May 9, 2007
I'm trying to find the last row on a sheet and then set the next cell, in column A to a certain value. It fails with Method 'Range' of object '_Worksheet' failed.
'activate the male page
sMalePicks.Activate
'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'get next row
lastRow = lastRow + 1
sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))
View 5 Replies
ADVERTISEMENT
Feb 1, 2010
I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my
View 2 Replies
View Related
May 1, 2012
I am using Excel 2010. Why I do struggle with setting the range below
Code:
Set rng = Sheets("Data").Range(Cells(4, firstcol), Cells(lastrow, lastcol))
I get run time error 1004
Tried simple code from msdn and it return same error
Code:
Range(Cells(2, 3), Cells(10, 4)).Select
View 2 Replies
View Related
May 6, 2009
Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?
View 5 Replies
View Related
Aug 23, 2012
I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:
1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works)
2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works)
3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)
The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."
I'm guessing there is a setting somewhere in Excel that has changed as part of an update.
I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.
For what it's worth, here is the macro code.
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Run "PullInSheet1"
Dim oldbook As String
[Code] .......
In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.
Code:
Sub PullInSheet1()
Dim AreaAddress As String
'''''' Sheet11.UsedRange.Clear
Dim ClRange As String
ClRange = "= 'L:ADMINEMPLOY SERVICES" _
[Code] ........
There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...
1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.
2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.
The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.
I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem).
View 4 Replies
View Related
Aug 8, 2006
i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.
Option Explicit
Dim rng As Range
Const FirstRow As Long = 2
Dim r As Long
Dim ans As Variant
Dim MyArray(100, 4)
Public MyData As Range, c, d As Range
Private Sub cmbAmend_Click()
Application. ScreenUpdating = False
Set c = Worksheets("ENTRY SHEET").Range("b4").End(xlUp).Offset(3, 0)
c.Value = Me.DTPicker1.Value
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value ..........
View 9 Replies
View Related
Oct 7, 2009
Trying to write a macro to transcribe data from columns in Sheet1 to Rows in Sheet2. Assuming there is a blank between each record. I get an error on the "Set Rng1....." line.
View 4 Replies
View Related
Mar 11, 2014
I can't figure out whats wrong with this code:
[Code] ......
Its give me error 1004 on this line:
[Code] ........
Its copying the data just fine, but its having issues with pasting it. The data should be pasted into the same corresponding columns (so also starting in A Column), in the next available row (in Column C from row 2 on).
View 3 Replies
View Related
Feb 2, 2009
I am using the following macro to format a sheet - I recently added a auto-fill part to it which essentially moves a number from one column to another and autofills it in all the cells until it encounters another number of the same kind in the column it moved the previous number from.
When I try to run this macro it does everything upto the auto-fill part and then gives me error 1004 [the set cell = cell.offset(1,0) ] is highlighted in yellow.
View 4 Replies
View Related
Dec 29, 2013
The following five lines of code are inside a loop in which i goes from 1 to 600. When i was 594 a condition allowed these lines to be executed. (The last was in my original code and the others were just added to try to figure out why the last had a problem.) The first three work and the last two trigger error 1004 "Application-defined or object-defined error." All I am trying to do is to fill some cells with a dummy value. It doesn't matter whether I try to fill with 1 or with "1".
Worksheets("Volumep").Cells(5, i+3) = 1
Worksheets("Volumep").Range("vv5:vv104").FillDown
Worksheets("Volumep").Range("vv5:vv104").Value = 1
Worksheets("Volumep").Range(Cells(5, i+3), Cells(104, i+3)).FillDown
Worksheets("Volumep").Range(Cells(5, i+3), Cells(104, i+3)).Value = 1
Whether or not there is a different or better technique for filling cells, the range specification is the real issue I am trying to solve. The referencing issue produces the same error later in the module where more complicated work is being done.
Bonus question: is there a general prohibition forbidding the mixing of range("a1") and cell(1,1) styles of addressing in the same line of code? (Not a factor in the immediate problem but related to previous problems I have encountered)
View 2 Replies
View Related
Dec 26, 2008
Range(Cells(1, 1), Cells(257, 257)).Select
This gives the Run-time error '1004'
I searched these forums for any old posts to work around this but did not find any threads. If anybody knows a good thread about this, I would be greatful.
Basically I run some code to generate Startcolumn, StartRow, EndColumn, EndRow. If there is a limitation of 256, the code is basically useless. I don't know of a way to use the A1 range method whilst using the Range("A1:....")
View 9 Replies
View Related
Jul 11, 2014
I'm trying to do the following
Dim wkb As Workbook
Dim wkb2 As Workbook
Set wkb = ActiveWorkbook
Set wkb2 = Workbooks("F:SuppliersBT Monthly Invoice2014BT Macros.xlsm")
but get a subscript out of range error relating to setting wkb2
The file BT Macros is open - this is where the Macro is stored. What have I done wrong when trying to reference it?
View 5 Replies
View Related
Sep 13, 2006
Subcript Out Of Range Error Coming Now For The Code Which Works For Me Before
Sub WHideRows()
Dim rRange As Range, rCell As Range
Dim strVal As String
Set rRange = Worksheets("WIED PROBLEM WELLS").Range("A11:A110")
For Each rCell In rRange
strVal = rCell(1, 3) & rCell(1, 4) & rCell(1, 5) & rCell(1, 6) & rCell(1, 7) & rCell(1, 9)
rCell.EntireRow.Hidden = strVal = vbNullString
Next rCell
End Sub
i am using the code above to hide the rows which doesn't have any values in all the following Cells 3,4,5,6,7 & 9 or Unhide the rows if there is value in any 1 of the following cells 3,4,5,6,7 & 9 from row number A11 to A110.
The same code works for me before. But now the code is not working. It says below the error message
Run-time error '9':..................
View 9 Replies
View Related
Aug 22, 2007
code is getting an error on the bold line below. My error message is run time error 424 - object required.....
View 7 Replies
View Related
Apr 18, 2009
I keep getting the following error : Run time error'1004'; Application-defined or object-defined error when using the following
View 8 Replies
View Related
Apr 20, 2012
Trying to find the last cell in a range. The Error msg I get is
Method 'Range' ob object '_Global' Failed
Here is my code
Code:
Sub Fleetcopy1()
Dim wbk As Workbook
Dim EndColumn As Integer
Dim EndRow As Long
Dim EndCell As range
Set wbk = ThisWorkbook
EndColumn = Cells(1, Columns.Count).End(xlToLeft).Column
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
EndCell = range(EndRow, EndColumn).Address
Bolded is where the error comes up. Not sure what this means.
View 4 Replies
View Related
Jan 30, 2013
It is designed to submit data specific cells from a userform. It works fine when I select and day, 1,2,3, etc. until I get to day 27-31. For some reason I get a Range of Object Failed error 1004 every time. I don't understand what's changing to cause the issue. The red text near the bottom is the one that it tells me is the problem.
Code:
Private Sub Submit_Click()
Dim ws As Worksheet
Set ws = activesheet
[Code]....
View 9 Replies
View Related
Feb 15, 2013
I know you can use the syntax range(cells(x,y),cells(z,w)) but for some reason unknown to be the following isn't working:
Set CurrSuppRange = TT.Range(Cells(1, CurrSuppCol), Cells(LastRow, CurrSuppCol))
where
CurrSuppCol = cboxCurrSupp.ListIndex
OurSuppCol = cboxOurSupp.ListIndex
LastRow = Workbooks(lbWkBkName.Caption).Worksheets(cboxWorksheet.Value).Cells(Rows.Count, "A").End(xlUp).Row
(each return integer values)
Set TT = Workbooks(lbWkBkName.Caption).Worksheets(cboxWorksheet.Value)
(tested and works fine)
View 2 Replies
View Related
Aug 29, 2007
I have code that opens a workbook then selects a range on a specific sheet. All the code works fine (the workbook opens, then the sheet is selected) up until it gets to:
Range("BM9").Select
Then I get a run time error 1004
I tried Cells(9,65) it does the same thing,
View 9 Replies
View Related
Aug 3, 2006
The problem lies in the following line
Range(Cells(1, a), Cells(b, a)).Formula = _
"=(rc[-1]-" & min & ")/(" & max & "-" & min & ") "
When it is executed i get the 1004 error ("application-defined or object-defined error)...
min and max are defined (as worksheetfunction.min and max of a selection), and their values are correct. The problem seems to lie in the max variable, namely if i simplify the formula to just
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & min & ")"
it works ok, but if it is
Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & max & ")"
I get the error.
I am completely baffled considering both variables are defined in the same way i.e.
min = Application.WorksheetFunction.min(Selection)
max = Application.WorksheetFunction.max(Selection)
View 9 Replies
View Related
Jan 12, 2007
I have a workbook that I am getting the error select method of range class failed. I am not sure what I have done wrong? I have a worksheet called Unit Tools that I would like to import the information in column B from another worksheet called Tool Catlog. This is the code that I used and I am getting a run time error 1004 with the above message. I am not really great with VBA but I am learning from the examples and questions that people have put on here.
Private Sub CommandButton4_Click()
Sheets("Tool Catalog").Select
Range("B3:B173").Select
Selection.Copy
Sheets("Unit Tools").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
View 5 Replies
View Related
May 11, 2007
I'm getting a Run-time Error 1004 when using a Range(rangestring).Select.
It only seems to occur when I'm trying to select more than 35 cells. Up until 35 it works just fine.
My code is designed to select individual cells in a worksheet based on user selections from a list, and as I said, works fine unless they select more than 35 items.
Is there a limit to how many cells can be selected using Range...Select? If so, I can't find it documented anywhere.
View 14 Replies
View Related
Jan 10, 2007
Run-Time error '1004':
Method 'Range' of object'_worksheet' failed
The Function basically takes any cell in range with a formula and has it blue. If the formula is overwritten by the user it turns the cells colour Red.
Private Sub Worksheet_Change(ByVal Target As Range)
'change Colour of cells from blue if formula based to red if data typed by user
If Not Intersect(Target, Range("AS63,BA5:BP66,BT7:CI55,BU60:BU64,BX60:BX64,CA60:CA64,CD60:CD64,BT55:CI66,BT59:CI59,CF7:CF55,CF65:CF66,DJ19:DJ21,DJ24,DL5:DM36,DJ41,DJ45,DJ48,DL41:DM48,DH50:DH51,DJ50:DJ51,DL50:DM53,DH63,DJ63,DL55:DM58,DL60:DM66,DU5:DV33,DU37:DV58,DZ8:EB8,ED5:EE27,ED31:EE66,EM5:EN12,EM16:EN29,EM33:EN38,DH63,AL5:AM26,AL30:AM49,AL53:AM66,AV5:AW16,AV20:AW29,AV33:AW53,AV55:AW63,CO5:CO66,CQ5:CR66,CY5:CY66,DA5:DB66,DJ5:DJ7,DJ14:DJ15,DJ17")) Is Nothing Then
If Target.HasFormula Then
Target.Font.ColorIndex = 11
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub
View 4 Replies
View Related
Feb 9, 2012
I'm attempting to write an OutLook 2007 macro that extracts excel spreadsheets from incoming email and saves them as a .csv file. Before saving the file I need to delete the header row. I am getting a "Error 1004 - Delete method of range class failed" error when running this code.
Here is the code: (the bolded line is where I'm getting the error)
Public Sub ConvertToCsv2()
Dim xls As Excel.Application
Dim oWB As Excel.Workbook
Dim tmp As String
Dim ws As Excel.Worksheet
[code]....
View 7 Replies
View Related
Apr 6, 2012
My code is :
Public Const GRAPH_PutData_COL1 = "A"
Public Const GRAPH_PutData_COL2 = "B"
Range(GRAPH_PutData_COL1 & "1").Sort Key1:=Range(GRAPH_PutData_COL2 & "1"), _
Order1:=xlAscending, _
DataOption1:=xlSortTextAsNumbers
it is showing error, "sort method of range class failed error 1004"
Above error only showing when first record is empty...
View 2 Replies
View Related
Aug 22, 2012
When I try to run following code, I get error
Run-time error '1004': Select method of Range class failed
Workbooks("Book1").Worsheets("Sheet1").Range("A1").select
however if I split the code like below it works perfectly
Workbooks("Book1").Worsheets("Sheet1").Select
Range("A1").select
View 3 Replies
View Related
Feb 16, 2007
When I run this code I get a run time error 1004, copy method of range class failed, I have an autofilter on and it is showing 5 rows, the 5 cells in column A of sheet2 gets copied to sheet1 OK but I get the error?
What do I need to do to fix this?
View 9 Replies
View Related
May 9, 2007
i added a "Microsoft office spreadsheet 11.0 " object using additional control and used it in my form of Excel Macro. then i added some data from the combo box to the embedded excel object dispalyed. the form displays correctly and am even able to add data to cells.
but am not able to sort a column. gives me the error message "Sort method of range class failed"
have tried to make sure the sheet is active using the ".Activate" with the object name. this is the only solution available on the existing forums.
View 9 Replies
View Related
Mar 16, 2009
Method range of object global failed
When i run this ....
View 9 Replies
View Related
Jun 28, 2006
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:
Private Sub TBEnterUp_Click()
iLast = Range("WBDate_DayLast").Value '<<<<<<<
iItem = TBEnter.Value
If iItem = iLast Then
TBEnterUp.Visible = False
Exit Sub
End If
TBEnter.Value = iItem + 1
If iItem > 0 Then TBEnterDown.Visible = True
End Sub
It is a simple number advancer. It is working in the UserForm I took it from. I have included a sample file of the problem. Feel free to look at it and borrow anything in it that you might like.
View 2 Replies
View Related
