Error Occurs While Inserting Row - Cannot Shift Object Off Sheet

Dec 7, 2011

I have very old file which contains lot of sheets. And I am unable to insert rows in any of the sheets in the file. It throws an error "Cannot shift object off sheet".

View 2 Replies


Error Inserting Rows Cannot Shift Objects Off Sheet

Mar 1, 2007

I am running excel 2007 and I have 23 worksheets in a workbook. I have no links or hidden rows or colums. I have no merged cells or comments. My biggest sheet is A1:

AW8096. I use VB macros to add lines and move data from one set of workbooks to this one.

I have built several sheets using this proceedure.

All of a sudden I get the error "Cannot shift objects off sheet" when I run
a routine I have 50,000 times. This even happens on new blank sheet so I know its' not on the sheet.

I have checked to make sure there are no hidden comments, row or columns. I have deleted most of the worksheets and I still get the error. I have made copies of the workbook and used different file types to save it. I can create a new black sheet in the workbook that is empty and the same thing happens.

The only way I can get it to work is to creat a new blank workbook and copy and paste the sheets into the new workbook. Yes I have serched the FAQ and knowledge base and tried all of those fixes as well.

View 9 Replies View Related

Custom View Error. Cannot Shift Object Off Sheet

Jun 15, 2008

I have a very wide sheet in which I have hidden and saved seven views.I am using it for a very long time but when i did some edit work in the 'full' view,the other views are not showing. In stead I am getting the messages "cannot shift objects off sheet" and "some view settings could not be applied".This is very frustrating since a lot of patience was tested while making this wide data.

View 2 Replies View Related

Add A Row That Basically Says "Cannot Shift Object Off The Sheet"

Apr 28, 2009

I m getting an Error when trying to add a row that basically says "Cannot Shift Object Off the Sheet".

View 3 Replies View Related

If Error Occurs Then...

Jan 19, 2010

In the middle of one of my subroutines, my function sometimes hits an error (Subscript Out of Range: because it's looking for a string when it is not there).

If this error occurs, I want it to then look for a different string.

So I ONLY want this other string search to occur if I get an error at that point in the code. I've tried "On Error GoTo ....." but it's doing that GoTo everytime, even if no error occurs. I also can't put "Exit sub" before the On Error statement, because there is a lot of code after this point as well.

View 9 Replies View Related

Error Object Required When Copying Sheet

Dec 1, 2011

I have a script that creates a bunch of sheets. It was working fine when testing because I was just adding a sheet using worksheets.add, but now I've found out they all need to be created from a template sheet. I set up the template sheet and changed to the worksheets.copy method, but I'm receiving a runtime error "Object Required"

All I want to do is copy the sheet and rename it to the next name in the array (I have an array of sheet names).

Here is the code snippet in question (error line in red):


If lngX = LBound(arrSheets) Then strAfter = Sheet1.Name Else strAfter = CStr(arrSheets(lngX - 1))
Set WS = Worksheets("TmpSht_Checks").Copy(after:=Sheets(strAfter))
WS.Name = CStr(arrSheets(lngX))

As written, the duplicate sheet IS created...

I also tried the method below, but I get the same result (sheet created (but not named) and the same object required error)


If lngX = LBound(arrSheets) Then strAfter = Sheet1.Name Else strAfter = CStr(arrSheets(lngX - 1))
Worksheets("TmpSht_Checks").Copy(after:=Sheets(strAfter)).Name = CStr(arrSheets(lngX))

View 1 Replies View Related

Cannot Shift Nonblank Cells Off The Worksheet When Inserting Row/column

Jul 12, 2006

When I try to insert a row, Excel displays the warning "cannot shift nonblank cells off the worksheet". I understand what the warning means but there are no nonblank cells at the bottom of my worksheet! Just to be sure, I have selected the bottom-most rows, cleared them, deleted them, and everything else I can think of. The warning still appears.

View 3 Replies View Related

If Number Occurs In The Sheet

May 5, 2009

I have a list of numbers like

1. 8 1 4 7 6
2. 7 4 6 7 8

and so on. In another sheet I also have numbers such

4 3 8 9 0

I want to be able to compare any numbers that occur in sheet 2 that are in the rows in sheet 1 and turn the cells a colour for example turn the cell green if the number occurs.

View 11 Replies View Related

Inserting Dictionary Object Into One Dimensional Array?

Mar 30, 2012

What I'm trying to do:

1) I have data that has a header row of 6-10 values (created, action, type, id, ...). The rows are unique records.
Click here to see the data

2) I originally wanted to read the data into one array (DataArray) and the header into another array (KeyArray). Then I would add additional unique information about each record (additional columns of info) into the DataArray.

3) I stumbled upon Dictionaries as a way of storing key/item pairs, which I thought could apply to each record since the headers are all unique and would be a way of storing key/items without needing to:

a) Know the ordering (if I used arrays I had to know the upper bound of the array to insert a column
b) Look up which what position in the array mapped to which column header
c) Resize the array every time I wanted to insert a new column of information on all the records

4) So now that I know about a dictionary object, I believe I need to create one Dictionary object for EACH record (row) of my data and then store those Dictionary objects within a one dimensional array such that each element of the array is a Dictionary.

5) This way I can iterate through each element of the array to access the dictionary inside and perform calculations on each record, depending on which key/item I needed to work with at a later point

Questions for Dictionary experts:
1) Is my approach sound?
2) What is the syntax for putting the dictionary of one row's worth of data into an element of a one-dimensional array?

View 8 Replies View Related

Error Handle To Return Object Causing Error

Apr 13, 2006

whether I can handle errors in a way that tells me which object caused the error?

For example if a userform has two combobox's and one causes an error, can I return to a message box containing the name of the object (combobox1 or combobox2)?

I have tried returning err.source but I am either not using it correctly or it isn't intended to be used this way.

View 3 Replies View Related

Object-defined Error 1004 Application-defined Or Object-defined Error.

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

Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)

Dec 1, 2009

I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View 3 Replies View Related

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!


start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies View Related

How To Shift Objects Of Sheet

Jan 10, 2012

I have a Master sheet linked with another worksheets with formula.

Now I want to insert 1 column in between , and when trying to insert the column in it a message sowing that " Cannot Shift Objects of Sheet"

Why this error shwoing and how can i insert any column in it.

View 7 Replies View Related

Return The Value Of The Shift Time On Other Sheet

Mar 30, 2009

I have a worksheet that contains a 5 week shift pattern for workers. It is briefly laid out as:


Months Jan to Dec 09 are there and shifts are divided up to ABCDE. I need another sheet to return the value of the shift time i.e. 1330-2130, probably via a lookup?? In my second sheet I have the Day number, the month and the shift letter.

View 2 Replies View Related

Run Time Error 1004 Application-defined Or Object-defined Error

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...

Sub GetRangeName()


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

Run Time Error 1004 (application-defined Or Object-defined Error)..

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

Run-time Error '1004' Application-defined Or Object-defined Error

Aug 12, 2009

Run-time error '1004' Application-defined or object-defined error. I am trying to use this

View 2 Replies View Related

Run Time Error 1004 - Application-Defined Or Object Defined Error

Oct 4, 2009

I was trying to use the below code

View 4 Replies View Related

Error Message "run Time Error '424' Object Required"

Aug 17, 2008

For some reason my form won't open when the workbook is opened. I get an error message "run time error '424' object required" (which happens when I have in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed from the userform_initialize I don't get an error but I also get no form. I recall having this issue before but I can't recall how to fix it.

Can I call the userform_initialize event from the workbook open event to get around this successfully and properly?

View 9 Replies View Related

Run Time Error 1004 (Application Defined Or Object Defined Error)

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

Runtime Error 1004 Application-defined Or Object-defined Error

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)
Range("DesNo", "LocationPath").Select
End Sub

View 3 Replies View Related

When Protect The Sheet - Delete And Shift Up Disable

Aug 8, 2013

I have a sheet,i want the users unable to select any cells,but a column is free to select an change and enter the inputs. I want to delete and shift up zero cells on that free column,but this property is disable when i protect the sheet.

How I protect other cells from selection and change,and enable delete and shift up for the column that not protected.

For example: I have the attachment file,every column is protected except column "H". I want a macro to when I clear content of any cell in this column,every cells shift up and every non zero cell put from row 1 to... and after them zero cells put.

View 2 Replies View Related

Excel 2010 :: Cannot Shift Objects Off Sheet

Nov 13, 2012

I'm trying to hide a few columns on my spreadsheet. I'm getting a "cannot shift objects off sheet" error message 2010 Excel program. I never had trouble with my 2003 excel.

There are no comments on this sheet, 'Display options for this workbook' already says all. I do have macro buttons on the top... but changed them all to move and size with cells.

View 1 Replies View Related

Object Variable Not Set Error?

Jul 24, 2012

Am getting Object variable not set error in the selection.find code. I dont see anything wrong with the code..

rivate Sub Btn_Edit_Click() ' code for Edit Function
Dim findvalue As String
Dim FileName As String


I can see the value of "findvalue" do exist in the column "A:A".

View 3 Replies View Related

Error Object Or With Variable Not Set

Jul 20, 2013

I am trying to make this code work, I am getting the subject error:

HTML Code:
Private Sub CommandButton1_Click()Dim cols As VariantDim NextRow As ObjectDim Myrow As Object
Sheets("Sheet1").SelectWith Sheet1
Myrow = Range("A" & Rows.Count).End(xlUp).Row + 1

[Code] .....

View 5 Replies View Related

Error '424' Object Required

Jul 28, 2007

Sub Macro2()
' Macro2 Macro
' Macro recorded 7/28/2007 by i8ig

If Target.Column = 1 Then
If Target.Value = "Med" Then
Rows(Target.Row).Interior.ColorIndex = 4
ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
If Target.Value = "Tasc" Then
Rows("4:4").Interior.ColorIndex = 44
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
If Target.Range = "NBAR" Then
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
End If
End If
End If
End If
End Sub
I continue to receive an error '424' object required and I cant find it

View 9 Replies View Related

Error Object Required

Nov 3, 2009

I have two worksheets one called "invoice" and one called "tenants"
"Invoice" has a userform where I want to enter a tenancy number and then lookup the tenants name and address from "tenants"

I have put the following macro in

Sub lookup()
Dim res As Variant
res = Application.VLookup("A1", Tenants.xlsx("Sheet1"), "$A:$H", 2, False)
If IsError(res) Then
MsgBox "not found"
MsgBox "found at pos: " & res
End If
End Sub

but get the following error - "object required"

View 9 Replies View Related

Error 424 Object Required

Sep 4, 2006

I am trying to get the code to search for me specific words in the excel files in my Folder as specified, however when i try to run it, there will be a Error 424 : Object required.

Sub SearchText()

cr = vbCrLf
quot = Chr(34) 'quotes

Dim l As FileSearch
Set l = Application.FileSearch

s = InputBox("Search", " Enter the text you're looking for.")

With l
.LookIn = "D:FinancialNews"
.SearchSubFolders = True
.FileName = "*.xls"
.MatchTextExactly = True ............................

View 9 Replies View Related

424: Object Required Error

Jan 20, 2007

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If programmatic Then Exit Sub
selectionChange (target) ' 424 occurs on this line
End Sub

Private Sub selectionChange(target As Range)
' sub implementation here

I can't see anything wrong with my sub call that would cause a 424: Object Required.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved