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
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.
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 RelatedI m getting an Error when trying to add a row that basically says "Cannot Shift Object Off the Sheet".
View 3 Replies View RelatedIn 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.
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):
Code:
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)
Code:
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))
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 RelatedI have a list of numbers like
A B C D E
1. 8 1 4 7 6
2. 7 4 6 7 8
and so on. In another sheet I also have numbers such
A B C D E
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.
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?
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.
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...............
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.
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!!
examples:
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"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
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.
I have a worksheet that contains a 5 week shift pattern for workers. It is briefly laid out as:
JanFebAB
111330-21300700-1400
221230-20300700-1400
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.
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.
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
Run-time error '1004' Application-defined or object-defined error. I am trying to use this
View 2 Replies View RelatedI was trying to use the below code
View 4 Replies View RelatedFor 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 Form1.show in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed form1.show 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?
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.
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
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.
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.
Am getting Object variable not set error in the selection.find code. I dont see anything wrong with the code..
VB:
rivate Sub Btn_Edit_Click() ' code for Edit Function
Dim findvalue As String
Dim FileName As String
[Code].....
I can see the value of "findvalue" do exist in the column "A:A".
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] .....
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
Range("H3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
Else
If Target.Value = "Tasc" Then
Rows("4:4").Interior.ColorIndex = 44
Range("H4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
Else
If Target.Range = "NBAR" Then
Range("J5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
Range("H5").Select
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
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"
Else
MsgBox "found at pos: " & res
End If
End Sub
but get the following error - "object required"
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
.NewSearch
.LookIn = "D:FinancialNews"
.SearchSubFolders = True
.FileName = "*.xls"
.MatchTextExactly = True ............................
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.
