Insert-shift Cells Down Macro

Jan 29, 2009

I want to be able to insert-shift cells down based on criteria in another cell. For instance, I would like a macro that would look at column B for saturday and sunday and then insert-shift cells down on the corresponding cells in column D...

View 9 Replies


ADVERTISEMENT

Insert A Value And Shift Cells Value Down

Feb 6, 2009

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


View 9 Replies View Related

VBA Insert Copied Cells Shift Down

Sep 25, 2013

I'm using a macro to copy cells (2 columns and between 1 and 5 rows [19 - 23 on s/s]) from one workbook to another but if I use

Rows("2:2").Select
Selection.Insert Shift:=xlDown

then every cell in the inserted rows are populated.

this is what I currently have

Windows("agent master.xlsm").Activate
Range("B19:C23").Select
Selection.Copy
Windows("distribution master.xlsm").Activate
Sheets("L38").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

but that doesnt work either. It just gives me an empty cell in A2.

View 1 Replies View Related

Using VBA And If Statements To Delete / Shift Up And Insert Cells

Sep 7, 2012

I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.

If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End

ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If

Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.

View 3 Replies View Related

Insert Cut Cells - Paste And Shift Cells Down

Aug 18, 2014

Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.

View 1 Replies View Related

Delete Row Macro (shift Cells Up Then)

Jul 1, 2007

I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?

View 9 Replies View Related

Macro To Search Column For Blank Cells & Shift Entire Row Right?

Nov 2, 2008

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

View 3 Replies View Related

Macro To Search Column For Blank Cells & Shift Entire Row Right

Nov 2, 2008

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 Related

Insert Column & Shift Left

Sep 14, 2007

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.

View 4 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!!

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

View 8 Replies View Related

Find Macro And Insert Cut Cells?

Dec 21, 2013

I have a worksheet with several thousand rows. During a month new entries are added at the bottom of the previous entries. One of the columns for each entry is a stock symbol and I've written a macro to enable going quickly to any specific stock symbol's location by searching for the symbol.

Once a month I select rows for some of the new entries for a given symbol, cut the relevant rows, activate the macro to find the location of other entries of the same symbol, and then "Insert Cut Cells" so as to move the new rows to the same area as other similar older entries.

What I was finding was that activating the macro was causing a recalculation of the workbook, which delays the macro from finding the symbol in question. This was getting bothersome, so I avoided the delay by adding an Application.Calculation = xlCalculationManual to the beginning of the macro, and at the end of the macro I reset the Calculation back to Automatic.

This eliminates the delay, in finding the old symbol, but it also clears out whatever memory location was storing the cut cells, so that I don't have the "Insert Cut Cells" option available once I get to the found location.

So, currently I have either: A) a quick find of the symbol, but I can't use the "Insert Cut Cells" feature, or B) I have a delay in the finding, but am able to Insert.

how I can have the quick find of the macro but still retain the ability to Insert the cut cells ?

View 8 Replies View Related

Macro That Insert Formulas Into Cells

Mar 19, 2008

Macros that will insert formulas into cells, and I've been able to modify the macros to insert additional (simpler) formulas well enough on my own. But I can't seem to find the pattern of where to put the quotation marks! Here's the macro code that will enter my formula, including the formula (and the portion of code that the debugger highlights):

r.Offset(, 1).FormulaR1C1 = _
"=VLOOKUP(rc[-1]"",""'Blend List'""!""A2"":""E250"",5,FALSE)"
And here's the code in it's entirety:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Columns("d")) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
For Each r In Intersect(Target, Columns("d"))
If r.Row 1 Then
If Not IsEmpty(r.Value) Then...........

View 9 Replies View Related

Macro Insert Cells In First Blank Column

Jul 6, 2008

I am trying to come up with a code that will copy column A entirely, and insert the copied cells into the first blank column. In the columns with information in them, there will always be text in the first cell, so that can be used as the test to find the first blank column, but I'm not sure how to get this done for my macro.

View 12 Replies View Related

Insert Copied Cells Modification (Macro)

Jan 26, 2014

I don't know if a macro is necessary, but the built in Insert -> Insert copied cells -> Shift cells down function can't accomplish what I require.

I've pasted in two example tables for what I'd like to do. For the first table, the data in column C is a name and the cells from columns D-Z, rows 7-10 is what I want to copy and paste from row 12, column D; row 13, column D; etc, etc. However, I want the full rows below the name in column C to move down when I paste from column D. Using the Insert function on the Insert blade only gives me the data in columns D-Z moving down.

The second pasted table gives an example of how I want it to look.

A
B
C
D
E
F
G
H
I

[code].....

View 4 Replies View Related

Insert Copied Cells Into Multiple Locations Using Macro

Sep 14, 2009

I've been using the following macro ....

View 8 Replies View Related

Excel 2010 :: Macro To Insert Formula Into Cells?

Oct 6, 2011

I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.

I had a go at the code, but keep getting Runtime error 13.

I'm using Excel 2010

Code:

Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer

[Code]....

View 6 Replies View Related

Creating Macro To Insert Text Into Predefined Cells

Apr 22, 2014

I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.

Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.

The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.

A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name

[Code] .......

A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter

The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.

View 3 Replies View Related

Delete Cells And Shift Up

Nov 7, 2009

I 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 Related

Delete Cells, Shift Everything Else Below It Up

Feb 6, 2007

I 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 Related

VBA To Shift Cells To The Left

Apr 30, 2009

how can i get this

Sheet1

ABCDEFGHIJKL7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N

View 9 Replies View Related

Macro To Shift Rows Up And Down

Oct 12, 2011

how to write a macro where I can select a row or rows (adjacent) press a button, and the secelected row(s) will shift down one row every time the button is pressed. I also need another button with a macro that shifts the row(s) up one row at a time. The following code works for shifting selected row(s) up, but I can't figure out how to shift multiple rows down a row at a time.

Code:

Sub RowUp()
Selection.EntireRow.Cut
Selection.Offset(-1, 0).Insert Shift:=xlDown
Selection.Offset(-1, 0).Select
End Sub

View 8 Replies View Related

Excel 2003 :: Macro To Insert Cells When Paste Data

Jun 11, 2013

I have two spreadsheets in Excel 2003.

Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.

When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.

I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.

I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?

View 1 Replies View Related

Conditional Macro - Insert Text And Lock All Duplicate Cells Except For First One

Sep 16, 2012

I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:

If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))

This is my macro:

Sub SetVoters()
'
' SetVoters Macro
'

'
Sub SetVoters()
'
' SetVoters Macro
'

'
ActiveSheet.Unprotect

[Code] ......

View 1 Replies View Related

Shift Cells Down Depends On Condition Of Others

Oct 20, 2009

Whenever "online" appears in the "Desc" column I need to shift the matching row down (shaded area). for example, "online" appears in H2, hence, C2:E2 need to be shifted down to C3:E3 leaving C2:E2 blank. Next, H3 has "online" there, C3:E3 need to be shifted down one row so the result will look like the data on the "result" tab.

View 5 Replies View Related

Shift Cells Up A Row To Align With Column Using VBA

Jan 13, 2012

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

View 3 Replies View Related

Delete Range Of Cells And Shift Up

Jan 30, 2013

I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?

Code:

Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Range("G1").Value Then
Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp
Exit Do
End If
Loop

View 1 Replies View Related

Shift Cells Up If Cell Equal 0

Dec 27, 2008

In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.

Here is the code I tried, but nothing happened:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub

View 9 Replies View Related

Make Cells Shift Down Automatically

Jun 18, 2009

i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.

View 9 Replies View Related

'shift Cells Right' Depending On Value Of Cell

Jan 4, 2010

I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell:

Selection.Insert Shift:=xlToRight
Range("B2085").Select
ActiveCell.FormulaR1C1 = "Click for detail image"
With ActiveCell.Characters(Start:=1, Length:=22).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic

Note that "B2085" above is the cell number that the script stops on to execute this code.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved