I am trying to make a macro to take a set of data and shift the data range down depending on the value in the A:1 cell. For example if the data in column A starts with 5, 6, 7, 8 and so on.... I would like to copy/ paste and shift the column down 5 cells so I can start A:1 at 0.....so that the data can begin as 0,1,2,3,4,5.....etc.
View 3 RepliesI 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.
I have this code (command button1) which works just fine. What it does is if cell 02 in sheet1 is "14" then it copies the entire row to sheet2. The same goes for all O column
I would like to change it a little bit.
Instead of copy i would like to cut the row, paste it in sheet2 and shift cells up in sheet1
Code:
Private Sub CommandButton1_Click()
Dim FirstAddress As String
Dim myArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim I As Long
Dim LastCol As String
[Code] ........
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 In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
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 code that pulls information into excel from a database and pastes it into a sheet called paste.
This will have around 15 columns of data, with 24 rows for each day. The first column is the date and the second is the amount of sales which was taken in that hour.
I would like to add code to my project that will take all items in the 2nd column that are next to yesterdays date which will be in the format yyyymmdd sum the items to make a total and then paste into a column on another worksheet they will need to be pasted into the next empty cell in that column on the other worksheet.
After the number has been pasted into this column I need to clear all of the data on the paste page ready for it to be updated again the next day.
VB:
Sub GetSomeData()
Dim db As Database
Dim rs As Recordset
Dim SQL_string, Path1 As String
[Code] ......
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 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
Im looking for a macro that can get a cell RANGE, the data in the RANGE will be copied then if the checkbox is selected it will copy the same data in the RANGE and add the selected checkbox in next column
I have column A and Column B < this is my selected range
Capture1.PNG
I will have a form that has checkbox and buton.
When all the checkbox is selected. when the button is click. the result will be.
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 RelatedI 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 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
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.
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 RelatedIn 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
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 View Relatedi 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.
How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop.
View 5 Replies View RelatedI'm trying to do is check each Row and move the last 3 Columns of data in each Row to the left so they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W). Columns A and B are temporary. Column A utilizes the formula "= COUNTA(B2:AG2)" to count the number of cells in the row that contain data. Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data in the active worksheet. Columns A and B will be deleted at the end of the Macro.
The GOAL (END RESULT) is for Columns A through W (and ONLY these Columns) to contain data (keeping in mind that Columns A and B will be deleted at the end). If some rows have Column W (and beyond) blank, then I want to MOVE data from Columns U & V over to V & W and then COPY data from Column T into the [currently] blank Column U.
I've been trying to get the Macro to start in the last row and, using CASE Statements, delete the proper range of cells and SHIFT LEFT as it counts backwards towards the first row. I'm not too sure this is the best approach and could really use some advice from the experts! I can provide a "test" file if necessary ... the test file I've been working with is approximately 6.5MB, but I can delete most of the 14,287 rows and still give a good representation of how the data varies.
Sub b_DeleteCellsShiftLeft()
' DeleteShiftLeft Macro
' Macro recorded 3/14/2007 by George Nicholaou
' Need to assign variable for current row (?)
' r=ROW()
' Columns A and B are temporary
' Column A utilizes the formula "=COUNTA(B2:AG2)" to count the number of cells in
' the row that contain data
' Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data
' in the active worksheet
' They will be deleted at the end of the Macro
' What I want the Macro to do from this point is:
' 1. Check each Row and move the last 3 Columns of data in each Row to the left so
' they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W)
' 2. The GOAL (END/RESULT) is for Columns A through W (and ONLY these Columns)
' to contain data (keep in mind, Columns A and B will be deleted at the end)
' 3. If some rows have Column W blank, then MOVE data from Columns U & V over to V & W............
I am trying to find a method for deleting empty cells in a range and shift cells left upon deletion automatically. I could do this manually, but with 3000 rows of data it could be a long task. I have attched a small spreadsheet to show what I am looking for.
View 5 Replies View RelatedI 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.
I need assistance in shifting cells to left based on three (3) specific text values in column B. There are tons of data and extra lines and stuff but consistently these three (3) text values are consistently not aligned and I simply need to shift entire row 1 space to the left. I can do the long and tedious process of going through them all but is there a macro which I can play that does it instantly...
View 9 Replies View RelatedI'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'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
I am looking for a method which would delete empty cells in a row, and then shift the remaining data to the left (filling the cells with data). I know that this could be done manually, but I need it completed for 3000 rows! I have attached a simple sheet to show what I'm Looking for.
View 2 Replies View RelatedI'm trying to have one cell be the constant place I enter data and then when I hit enter, I'd like the data I entered to be moved down to the cell below it. Then for every new number I enter in that first cell, it would auto shift the previous data down and add the new data to the cell below it.
Basically, I'd like to record data but I dont want to have to scroll to the bottom every time to enter new data since this list will be continuously updated. And I'd like the most recent data entered to be on top.
Then I want to take the top 100 cells in that column, and do a running average of them.