Object Defined Error At Else Of If Then Else
Aug 3, 2007
I am trying to name a dynamic range. The below works when I have selected the relevant sheet. However, if the sheet is not selected then I get a Runtime Error 1004 at the Else line. I had planned on naming 3 ranges like this on each sheet. I have 15 sheets, so end result will be 45 ranges.
Sub CIT_SLA()
'Name and size the range CIT_SLA so the Word file (EOM Report) can pick only the relevant cells
If Sheets("CIT Results").Range("A2").Value = "" Then
Sheets("CIT Results").Range("A2:G2").Name = "CIT_SLA"
Else: Sheets("CIT Results").Range("A2", Range("G65536").End(xlUp)).Name = "CIT_SLA"
End If
End Sub
View 6 Replies
ADVERTISEMENT
May 13, 2009
I am having difficulty getting a form to work the way that I would like it to work. I have a form that is used to display questions that my students will be answering. The form also is used to put the answers into a worksheet. I have 2 sheets. Sheet2 has the questions, student answers, and correct answers. Sheet1 is used to indicate correct answers and to keep track of percentage correct. I am fairly new to VB. I have 2 pieces of code that I am going to post. The first one works and the second one doesn't.
This is in the "This Workbook" section and it works.
Option Explicit
Public intNoQ As Integer
Public strNoQ As String
Public NumberofQuestions As Integer
Dim StudentName As String
Dim InputBoxAnswer As String
Public Sub Workbook_Open()
Application.Visible = False 'Hide Excel
Load Questions
Load NumberCorrect
NumberofQuestions = Worksheets("Sheet1").Range("K3").Value
For intNoQ = 1 To NumberofQuestions
strNoQ = VBA.CStr(intNoQ)
If intNoQ = 1 Then
Questions.Controls("QuestionNumberBox").Value = "Question#" & strNoQ
Questions.Controls("QuestionBox").Value = Worksheets("Sheet2").Range("B1").Value
End If
Questions.Controls("CorrectBox" & strNoQ).Visible = True
Questions.Controls("CorrectLabel" & strNoQ).Visible = True
Next intNoQ...............
View 9 Replies
View Related
Mar 5, 2013
I have a relatively complex report that I work with and a worksheet is no longer required. I have deleted the worksheet and reference to it hwoever when running the macro to pull all the data, it gets to the summary of all the data and i get the Run Time Error 1004 Application-defined or object-defined error pop up. ON reviewing it, it is on this line ActiveCell.Offset(0, 0).Range("a1:a" & Range_Height).Select of the below code...
VB:
Sub GetRangeName()
Sheets("TOTAL").Select
[Code].....
use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.
View 1 Replies
View Related
Apr 26, 2009
I have a simple function below to put in different forumlas in different cells to get stock quotes. When I run this I get runtime error 1004 application-defined or object-defined error. The first formula goes through but vba chokes on the next formula: ActiveCell.Offset(I - 1, 4).Formula = username
View 10 Replies
View Related
Aug 12, 2009
Run-time error '1004' Application-defined or object-defined error. I am trying to use this
View 2 Replies
View Related
Oct 4, 2009
I was trying to use the below code
View 4 Replies
View Related
Dec 4, 2009
I keep getting a Run Time Error 1004 (Application Defined or Object Defined Error) when my sub reaches this line:
ActiveCell.Formula = "=SUM(D222,D224,D226,D227,D229,...)"
In the actual line of code the "..." above is another 20-30 or so cells in column "D". Probably no more than 150-170 characters in the line.
If I remove half of the cell range names it works, but I need all of the cell ranges for the equation.
View 9 Replies
View Related
Jul 24, 2007
I am trying to copy the info from one workbook to another workbook.
I keep getting the above referenced error...
Private Sub CommandButton1_Click()
Dim filepath As String
filepath = Range("A100")
MsgBox ("File Appended")
Workbooks.Open (filepath)
Windows("CorrespondenceMaster.xls").Activate
Sheets("Sheet2").Select
Range("DesNo", "LocationPath").Select
Selection.Copy
Windows(filepath).Activate
Range("A2").Select
ActiveSheet.Paste
End Sub
View 3 Replies
View Related
Jan 12, 2010
I haven't figured out who hates who more, me hating VBA or VBA hating me.. we just do not get along very well.
Anyways, I am having difficult understanding why I am getting the following error:
Error 1004: application defined or object-defined error.
I am using code that I just copied over from a worksheet where it was working fine. This is part of it.
View 6 Replies
View Related
Dec 1, 2003
I have a userform setup to enter data into a worksheet
Whee the user hits the button to add the data, I get the error 1004
Application defined or Object defined error
It is on the line Set LastRow
Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(x1Up)
....end sub
Now I am sure this has always worked in the past, so Is it me or am I really missing a plank or two?
View 9 Replies
View Related
Oct 1, 2009
I keep running into this error when I try to create a pivot table.
The debugger highlights the following line:
View 4 Replies
View Related
Aug 25, 2006
'Code1
Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new").Activate
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
'Name
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
'city
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)
the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.
View 5 Replies
View Related
Oct 4, 2006
Trying to add a named range at run-time
Here's what I have so far ...
View 9 Replies
View Related
Nov 10, 2006
I have problems with setting Validation input title and message. When in column "C" I type product code, data validation of corresponding cell in column "D" changes to list of diameters allowed for that product. That works fine. But i also want to set validation message to show allowed diameters.I set it in VBA using named ranges.
But for every second line (13, 15, etc on attached file) I get an application-defined or object-defined error. The rest (14, 16 etc) works ok.
View 4 Replies
View Related
Nov 22, 2008
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round?
In free-run mode from excel i just get a #VALUE!
View 13 Replies
View Related
Jan 13, 2009
I had to change sheet one but everytime I open this workbook, it keep saying "Application-defined or object-defined error"
but I cannot find what is causing the bug. I have checked the name of sheet and it seem correct for me in the codes.
View 7 Replies
View Related
Jun 9, 2009
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):
View 6 Replies
View Related
Aug 12, 2009
I'm trying to enter a series of formulas into a worksheet using vba. However, this code is giving me Run time error '1004', along with the description in the title. This is the first formula (they're all relatively similar).
View 2 Replies
View Related
May 23, 2007
I have a sub that takes the value of a cell and get the hour by using
mhours = Hour(KRT)
then using DateAdd I need to subtract the mhours from etd
and Im getting this error
Application-Defined or Object-Defined Error
Worksheets(b).Range("G" & q).value = DateAdd("h", mhours, etd)
Note: KRT and ETD are dim'd as Date
Note2: DateAdd returns the same error wether mhours is postive or negitive
View 9 Replies
View Related
Feb 26, 2009
this is the line it gives the error on.
Range("A1").End(xlDown).Offset(1,0).Select
View 9 Replies
View Related
Aug 29, 2006
I get a run time error 1004 when I run this sort.
ActiveSheet. Range("A1:AC277").Sort Key1:=ActiveSheet.Range("G2"), Order1:=xlAscending, Key2:= _
ActiveSheet.Range("E2"), Order2:=xlAscending, header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
View 9 Replies
View Related
Nov 14, 2006
Im trying to copy all the unique values from one column to another and the easiest way seems to be:
Set SourceRange = Worksheets(page2). Range("C1:C17365")
SourceRange. AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Worksheets(sida3).Range("B"), Unique:=True
However, I get the error- message: Run-time error '1004': Application-defined or object-defined error
View 2 Replies
View Related
Jun 1, 2009
I get runtime error 1004 "Application Defined or Object Defined Error" when I try to do the following:
View 4 Replies
View Related
Jun 5, 2009
So I'm correcting macro at work and keep running into this error:
"Application-defined or object-defined error"
View 5 Replies
View Related
Jul 31, 2009
I'm trying to figure out why I can run a bit of code on my machine but on another machine I get an: Application-defined or object-defined error. The code is in an excel file on a network drive and it pulls up a form, once the form is submitted it runs some code that looks like so
View 2 Replies
View Related
Jul 1, 2008
When opening a blank excel workbook I receive the error "1004 Application-defined or object-defined error". I press OK. And the message appears 2 more times before I am able to work in Excel. Doesn't matter what excel file I am opening, does the same thing. I do not know how to lookup programming code for any macros I am running. I would like to get rid of this error! It is a nusance.
View 9 Replies
View Related
Apr 26, 2006
I am getting an 'application defined or object defined' error at the code coloured red. I'm trying to sort a list of names starting at c5
'Update Names on Sheets
If Sheets("Daily Visits May").Range("e1") = "y" Then Goto 10
If Sheets("Daily Visits May").Range("d1") > 38837 Then Else Goto 10
Sheets("Daily Visits Apr").Select
Range("b5:c660").Select
Selection.Copy
Sheets("Daily Visits May").Select
Range("b5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
View 3 Replies
View Related
Jul 6, 2006
I get "Application-defined or Object-defined error" , run-time error 1004.
Option Explicit
Sub Initialize()
Dim rList As String
Dim CountRows, CurrCol As Integer
rList = "myTemplate"
Range("A2").Select
CountRows = Range(Selection, Selection.End(xlDown)).Rows.Count
CurrCol = 7
With Range("G : G" & CountRows + 1)
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
.ErrorTitle = "JC Message"
.InputMessage = "Select requested template"
.ErrorMessage = "You must enter a template from a list only"
.IgnoreBlank = False
End With
End With
End Sub
View 6 Replies
View Related
Dec 23, 2009
when I run the macro, I receive an error: "Run-Time Error '1004': Application-Defined or Object-Defined Error" See attached macro. If I click "Debug", the line "With r.Resize(,1)....... becomes highlighted in yellow.
View 9 Replies
View Related
Sep 18, 2009
In one worksheet i have created the following code, which adds date to
specific column when data is entered in colum A.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Target.Offset(0, 10).Value = Now()
End If
End Sub
The problem is that when trying to delete a whole row, error message pops-up.
Run-time error '1004' Application-defined or object -defined error. When click "End" actually everthing is ok, the row has been deleted, but was just wondering what is causing that error.
View 2 Replies
View Related
Jan 6, 2010
This error is telling me "application defined or object defined error" and i am unsure how to fix it. Basically i have a ton of files within a folder that i am trying to do some reformatting to. I would like to save the new files to a new folder and keep the originals where they are.
View 5 Replies
View Related
