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 RepliesI've got a problem with one of my macros. This code should import a text file. The name of the imported file is the same as the value of the cell that is active the moment the macro starts. The way I've written it, the code can't find the needed text file.
Sub addfile()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:aprojects" & ActiveCell.Value & ".txt", Destination:=Range(ActiveCell _
))
.Name = ActiveCell.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0.........................
i need vba to automate counting the number of nonblank cells in a colum.
i may be in a diffrent colum each time i run this so i need it to be dynamic in that sense
i need to store the result as a variable once i have the result.
i have a feeling the best way to do this would be to calculate it in the bottom most cell.
but i am unsure how to make (C:C) dynamic
=65536-(COUNTBLANK(C:C))
I need to shift all the cells in columns B, C, D, E & F up a row to align with the names in column A.
I have code to delete the empty rows.
Start:
Bank Holiday 1 on a Week End
ABCDEF1YASMIN AKHTAR (YA1) 2 59038.753MARINA BELAIDI (MB2) 4 12325.55MARGARET CANT (MC6) 6 33002.25
Result Required:
Bank Holiday 1 on a Week End
ABCDEF1YASMIN AKHTAR (YA1)59038.752 3MARINA BELAIDI (MB2)12325.54 5MARGARET CANT (MC6)33002.25
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 View RelatedI 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'm currently working on an excel project for work, to replace our production tracking to something more current (anything is better than pen and paper!). I am in charge of keeping track of cases of product made on our shift: product code, flavour, bag size, current shift scheduled #s, next shift scheduled #s, total scheduled #s, produced (on our shift), and product on hold. For the most part, I've achieved to get all of it working, but I want to take it one step further, where it is completely automated.
Normally during the dayshift, we will receive an attainment report from the scheduler, and on this schedule, it lists out what amts are scheduled for each code, for the next 3 shifts. We would then manually write down each product code running on our shift, how much we're scheduled to run on our shift, and the next shift. Before the end of our shift, we would write up another production sheet for the afternoon shift, and the afternoon shift would do the same for the midnight shift, and again for dayshift until the scheduler revised the attainment report.
My file consists of 5 worksheets: Days, Afternoons, Midnights, Product, Schedule Days, Afternoons, Midnights will be where production data is stored throughout each shift. Product holds records for each type of product we produce. Schedule holds the scheduled #s for each product scheduled to run for the next 3 shifts.
So finally getting to the point, I would like to check the Schedule worksheet, column C for any non-blank cells, and copy the corresponding product code to the dayshift worksheet starting at A3, and the same for column F for afternoons, and column I for midnights.
I've attached the sheet I'm working with. I need to delete most of column F and shift the rows to the left, but when I delete the cells it doesn't give me a prompt to shift the row, it just deletes the entire column or individual row. I need them to shift, what can I do?
Attached File : Coordinators Test.xlsx
There will be anywhere from 1 to 5 values in each of these columns, but not
successively. There will be several blank cells inbetween these nonblank
cells, which I need stacked neatly into the first five rows at the top of the
worksheet.
Example:
(A10) - Apples
(A17) - Oranges
(A23) - Peaches
(A38) - Some other fruit of your choice...
(B14) - Toyota
(B21) - Honda
(B44) - Mercury...........
I am trying to write a macro to do the following:
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
View 2 Replies View RelatedI have a pretty big worksheet (230 columns x over a thousand rows). Any given column might have 3-4 nonblank cells; the rest are blank. What I want to do for each column is obtain the address of each nonblank cell, then grab the contents of the corresponding cell in the B column and concatenate the results. In the attached example, I want to put the function in d2, and the expected result would be ar 001, fc 001, hw 003.
View 4 Replies View RelatedI need to add nonblank cells (cells have text) in a column that equal the
criteria of another cell.
So I want to add the cells in Column C that have text but also equal the date in Column A (which is equal to the date in Cell A1).
I have tried the follwoing:
=SUM(IF(A2:A19=A1,IF(C3:C19="x",1,0)))
=COUNT(IF((A2:A19=A1),C2:C19))
=IF((A2:A19)=A1,COUNTIF(C2:C19,"x"))
Suppose I have done a spreadsheet of,say 100 entries in alphabetical order in a column and I discover that I have missed 6. Is it possible to move the existing part from where I missed below this to make room for more entries without losing any of the entries?
View 3 Replies View RelatedThis may sound trivial, but how do you insert a column to the right of the current selection?
If I do Selection.EntireColumn.Insert that inserts a column to the left. Anyone know how to do this?
I am operating Excel for Mac 2011 version 14.4.2
I have a list of 1500 addresses in a column, something like, 5VistaTerrace, and I need to insert a space between the number and each of the words so that it reads 5 Vista Terrace in the cell.
All of the addresses are different, some with 4 or 5 words like 5ABrownBayCourt... that particular one I would want to read 5A Brown Bay Court in the end.
Is there a formula or excel trick I can perform to save me the time of manually adding the spaces?
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies View RelatedSoftware: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
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 am using the code posted by Reafidy on [html] http://www.ozgrid.com/forum/showthread.php?t=62708[/html] to create a list of all possible combinations. The only problem i have is that sometimes the total no. of combinations exceed the excel row limit of approx 65,536. once the row limit in column 1 exceeds 65k, the remaining combinations automatically shift on column 2 and then on 3 n so on.
View 6 Replies View Relatedhow to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.
Here is my code - it's ugly but it was working when I just needed it to move down one cell:
lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then
[Code].....
How would write a macro that would delete a row and shift up if they have a value in Column A?
View 6 Replies View RelatedI need a macro which will manipulate my data like this:
If a blank cell is found in column C, then I'd like to have the contents of that row in column D shifted to the right.
I wanna shift a particular column (lets say column A) to the left, so I do the following:
hojaFAC.Columns("A:A").Select
Selection.Insert Shift:=xlToLeft
hojaFAC is a worksheet
BUT I get the next error when I execute my macro :
RUNTIME ERROR 1004
Select method of range class failed
Why do I get runtime error nš 1004 all the time also for other reasons. I think itīs the only runtime error I ever jumped into. But If I ever get a runtime error It's allways number 1004.
Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.
View 1 Replies View RelatedI have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.
View 5 Replies View RelatedI have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...
View 9 Replies View RelatedIf I put a length in (G13) such as 4.44 & position from (H13) is 1. Then I want that length of (G13) to move its value (4.44) into (C15) the 1st cell to add to is (C15)
Then Move (C15) to (C16) 11.12 becomes C16
and move (C16) to (C17) and so on all the way down the C column.
When it hits the last joint (C374) position 360. I want a messageBox that says you can not insert anymore.
Basically shift the existing values down untill (C374)
I have only column C to shift the calue from (G13)
I do NOT want to delete rows!
Below is how it looks before macro:
GH12LENGTHPOSITION134.441
C14LENGTH1511.121612.221711.561811.861913.112012.872113.062211.992312.03
Below is how it will look after macro: The RED moves to (C15)
C14LENGTH154.441611.121712.221811.561911.862013.112112.872213.062311.992412.03GH12LENGTHPOSITION134.441..........................
how can i get this
Sheet1
ABCDEFGHIJKL7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N