Variable In Range.Formula Causes 1004 Error
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
ADVERTISEMENT
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
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 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
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
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 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
View Related
Nov 21, 2012
Application-defined or object-defined error 1004
when I register the formula in the macro they work perfectly, when I run I receive this message, really do not know why I thought maybe I should substitute something but really is too complex for me to go inside that.
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(IF(AND(R[10]C[-2]0.01*R[10]C[-1]),INDEX(R[46]C[2]:R[49]C[2],RANDBETWEEN(1,4))&TEXT((1-R[10]C[-2]/R[10]C[-1]),"" #%""&"", ""),IF(R[10]C[-2]5,INDEX(R[55]C[7]:R[56]C[7],RANDBETWEEN(1" & _
" "",IF(R[27]C[5]>7,INDEX(R[55]C[8]:R[56]C[8],RANDBETWEEN(1,2))&"", "","""")))"
Range("D5").Select
ActiveCell.FormulaR1C1 = _
[code]....
View 1 Replies
View Related
Jul 23, 2007
Wrote this snippet to clean a sheet but if Chr(34) or Chr(60) are in sequence or before the others it errors out (Runtime 1004 "The formula contains an error"). However, it they are last as in the example code, it runs fine, finds them and replaces them.
BTW: Chr(34) is " and Chr(60) is <
Special characters ?, ~ and * are handled as proscribed by Excel help, ie: ~?, ~~. and ~*
So I also tried Chr(34) ,"~"" and """", Chr(60), "~<" and "<"
All other chars seem fine. Just curious...
Option Explicit
Sub MrKlean()
Dim r As Long
Dim ws As Worksheet
Set ws = ActiveSheet
With ws. Cells
'keep <sp> Chr(32)
'Chr(33)......................
View 4 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
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
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
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
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
Aug 21, 2014
I manage to find a macro online, which aims to insert a desired formula into desired cell range. However, when I execute the macro, i get a "run-time error, 1004".
View 1 Replies
View Related
Oct 20, 2008
I lost 2 days trying to discover the bug in my program, but I coudn't find the right answer.
How can I activate the AutoFilter to show me the rows selected with "YES" ?
This is the code... The compiler stops always at the command Selection.AutoFilter.
View 14 Replies
View Related
Feb 10, 2009
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.
View 11 Replies
View Related
Mar 25, 2009
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.
View 6 Replies
View Related
Mar 27, 2009
I am encountering this error when I try to run code I have written on my own machine. I am working with all unprotected sheets, and I am running Excel 2007. All of the sheet exist.
Here is my ....
View 10 Replies
View Related
