Move Cell/range Of Cells

Aug 12, 2009

I've been trying to figure out how to use a formula that would enable me to copy/copy & move a range of cells right one column if a certain criteria is met.

Basically, if a criteria is met, I want to 'copy' everything from a row to another row, but offset it by one column (so that what was in, let's say, a1 would be in b2).

I've been tinkering with index(), if() and offset() in various combinations but think I must be missing something.

View 5 Replies


ADVERTISEMENT

Macro To Move Range Of Cells When Value Of One Cell Changes?

Oct 14, 2013

I have an excel file that contains data from bank transactions.

In column A at irregular intervals is a cell with " User Group:" It depends on how many transactions there are in a batch, could be 1 or up to say 200 as to when the next "User Group" cell is found.

After that also in column A is "Item" then "Lodgement Ref" then at the end "Batch Totals"

i.e.
User Group:
Item
Lodgement Ref
Batch Totals:
User Group:
Item
Lodgement Ref
Lodgement Ref
Lodgement Ref
Batch Totals:

I want to find each instance of "Item" then move that cell and every cell over to column CK down to and including "Batch Totals" i.e. A3401 to CK3410 to CM 3400 so it lines up with the top of the transaction block.

There are over 60,000 lines so it's worth trying to find a solution as I can then use the methodology to make other refinements in the spreadsheet.

View 9 Replies View Related

Move Range Of Cells Based On Cell Criteria

Nov 16, 2007

I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.

Sub MoveRangeOfCellsBasedOnCellCriteria()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp))
For Each cell In myrange
If IsNumeric(Left(cell.Value, 1)) _
Or Left(cell.Value, 5) = "UNIT " _
Or Left(cell.Value, 4) = "THE " _
Or Left(cell.Value, 5) = "FLAT " Then
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
cell.Offset(1, 1).Range("A1").Select
End If
Next cell
End Sub

View 7 Replies View Related

Move To Next Cell Below Without Using Range

Jul 2, 2008

Is there other syntax that can be used other than:

Range("A23").Select?

I'm trying to move to any cell below some text. So, it will find "Total" and move the cell below.

Also, is there any way to specify to select cells without specifying the actual cell name?

View 9 Replies View Related

Move Formula Range One Cell Down?

Jun 19, 2014

how to move formula range without inserting any rows. For example, I have a sum(a1:a3) and I need to have sum(a2:a4). I have hundreds of these little formulas through the spreadsheet and they are driving me crazy. Is there any way I can select a block of data and have a macro simply shift all formula ranges within that block one down? Copying the formulas down and then moving the result in place of the original doesn't work as I have formulas relying on these results and I will get #REF errors. I bought an Excel VBA for dummies, but have been so swamped with work that I haven't had a chance to look at it, or even to think about where I would begin to look.

View 1 Replies View Related

Move Range To Last Cell On Another Sheet

Aug 22, 2008

Im trying to create a macro that when ran it copies all information from that row and puts it on a different worksheet. It needs to be put on the next available line in the new worksheet. and then the information deleted off the original and all rows moved up to fill in the gap. the rows all contain 2 formulas i'd like to keep specific to the row number Also it contains formulas id like to keep but change the cell numbers according to which row it is copied over to.

View 2 Replies View Related

Move To Next Blank Cell When Pasting Range

Jun 10, 2009

VBA to find the next appropriate blank cell in column "A" when copying ranges of varying sizes and pasting them in a new worksheet one after the next.

Currently I have recorded a macro that uses the code below to get to this location, but I am worried this will break when I get new data of a different range size;

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'my initial range selection
Selection.Copy
Windows("myfile.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select 'finds last cell of my paste
Range("N69").Select 'I hit the down arrow once to get to new blank row
Selection.End(xlToLeft).Select 'to go to begining/col A of new row for next paste

I have tried playing around with

ActiveCell.Next

but can't get it to work.

View 3 Replies View Related

How To Move Multiple Cells Into One Cell

Aug 26, 2013

I have come across a format issue I need to deal with it at work.

I have a list of data like this:

A
1234567
8956423
4561237
7531596
8524561

In Column A, I have about 2,000 lines of information. What we want to do at work is create this information to be held in one cell at the top of the excel and in numerical order.

Like this:

A
1234567, 4561237, 7531596, 8524561, 8956423.

View 5 Replies View Related

Move 2 Cells Directly Below The Cell?

Jan 1, 2012

I have the following code that will go down through column "L", look for the value "ATS", and if found will move it to the right 1 cell. this works find,

My need is to also move the 2 cells directly below the cell containing "ATS", then clear the contents of the "Original" 3 cells before going to find the next instance of "ATS".

Sub Move_Values()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("L4", Range("L5000").End(xlUp))
For Each cell In myrange
If InStr(cell.Value, "ATS") > 0 Then
cell.Offset(0, 1).Value = cell.Value
cell.ClearContents
End If
Next cell
End Sub

View 5 Replies View Related

Move Cells Down To Selected Cell?

Jul 31, 2012

I have 2 col A & F. F col associate with date values & Col A has some color cells.I want to move down those color cells to a selected non-color cell row when run a macro.

I have code which perform to move color cells down to selected cell but problem is, if i have A4 & A6 color cells and i selected F2 cell & run macro then color cell A4 & A6 move down to cell A2 but cell A3 move to place A6. The sequence is

A1A2A4A6A5A3A7A8 & so on but i want cell A3 just push down the order & sequence will be

A1A2A4A6A3A5A7A8 & so on My code is as

Code:
Public Sub Move_colored_cells_rows()
' Instead of moving the entire row , 10 columns will be moved
Const NUMBER_OF_COLUMNS = 10

[Code]....

View 2 Replies View Related

Remove Cell & Move Cells Below Up

Mar 17, 2008

I would like to move the data in one column up by 1/2 a row without moving the other columns/rows in the spreadsheet

Here is an example I made quickly in Photoshop - the above image is what I currently have in Excel, and the lower image is what I would like to achieve, with the column shift that I would like circled in red

(the empty space under the column heading is just an artifact from the edit, it doesn't have to be there)

View 9 Replies View Related

Move Cell Comments With Cells

May 3, 2008

I have a cell comment on a series of columns and want to show only one column at a
time. For this reason I hide the ones not used, but the cellcomment doesnt move and
is still at the original place. How I move the comment when the original colums between are hidden ?

View 2 Replies View Related

Macro To Move A Range Of Data Over 1 Cell, If A Certain Condition Exists

Mar 4, 2008

I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example.

14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178

View 9 Replies View Related

Code To Move The Cells Right Depending On The Value On One Of The Cell

May 4, 2007

I have attached the sample file where I have noted what I want as a comment. I need to check for the value " Function Name: RF Pick" if this is there then the code should move 2 rows down check whether that row has the value " Each" if its there just move one row down and move the cells as I need in the output.

View 7 Replies View Related

Verify Data In Specific Range Or Move To Next Open Cell Down And Past Value?

May 9, 2013

I have a command button that will run paste a value in a specified cell but I also need it to check for the next open cell and paste the data if the previous cell in a specifid rage is already filled with data. Here is my code I have below. *jumps with Joy* My Range is E6 to E10

VB:
Private Sub CommandButton1_Click()
Dim f As Double
Dim t As Double

[Code].....

View 1 Replies View Related

Move Contents Of Cells From One Cell To Another At The Press Of A Button

Jun 14, 2014

Attached is a document, column C & F highlighted in green are areas that users will input data. I need to create a button (top left of spreadsheet) that when pressed moves both the data from column C & F up into the box above. The top set of dates would be deleted and the dates from Trip 2 would move up into the Trip 1 position, Dates from Trip 3 would move up into Trip 2 position and so on.

View 2 Replies View Related

Copy And Paste Cells Into Another Cell If There Is An Empty Spot, If Not Move Down.

Mar 8, 2007

i need to have 6 or so cells copied and pasted into another sheet. (when a button is pressed) However before the data is pasted it checks if there is anything in the cell(s) that are being pasted into, if there is something then move down a line and paste. Here is the code i have already:

Sub Order2Invoice()
Sheets("OrderDatabase").Select
Range("B65536").End(xlUp).Offset(1, 0).Select
With ActiveCell
.Value = Orderform!G5.Value
.Offset(0, 1) = Orderform!E10.Value
.Offset(0, 2) = Orderform!E11.Value
.Offset(0, 3) = Orderform!E12.Value
.Offset(0, 4) = Orderform!E13.Value
.Offset(0, 5) = Orderform!E15.Value
.Offset(0, 8) = Orderform!E15.Value
End With
Sheets("Invoice").Select
End Sub

View 4 Replies View Related

Is There A Way To Stick A Few Row Of Cells Together So That When I Move One They All Move

Jul 7, 2009

is there a way to stick a few row of cells together so that when i move one they all move.. i have some rows that when i sort them i want them to move together with the other ones..

View 9 Replies View Related

If Cell Text Equals Cell Text In A Range Of Cells Display Value From Range Of Cells

Oct 4, 2012

I have the following formula that is supposed to grab a value from C2 and check for that value in a range of cells and if it matches it is supposed to display the corresponding value in another range of cells. What am I missing??

=IF(C2='Google Doc'!$B1122:$B1266,"'Google Doc!K1122:K1266'","Not OK")

View 9 Replies View Related

VBA Check If A Cell Is Empty - Move 7 Cells Over And Check Again (Loop)

Aug 10, 2012

I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.

Code:
Sub Tester()

Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range

Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)

[Code] ......

View 1 Replies View Related

Find Matched Value In A Range For Named Cell Then Copy Range Cells Below

Aug 6, 2013

I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.

The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.

View 9 Replies View Related

How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

View 3 Replies View Related

Move A Range Using VBA?

Feb 16, 2013

I have a small formatting issue in my excel spreadsheet.

I would like to move cells in the form below:

1

0

95

35

21

2

12

110

272

14

To:

1
2

0
12

95
110

35
272

21
14

View 4 Replies View Related

Move To End Of Used Range In Row

Nov 1, 2007

Is there a key combination to move Right to the end of the row even though there may be blanks within that row?

If I do a Shift>Ctrl>Right arrow it will highlight to the cell just before the first blank cell.

I am trying to "Move" to the Right end of the table reguardless of what cell I'm in or if there are blanks in the row.

View 9 Replies View Related

If Value Exists In A Range Then Move To Next Row

Jun 18, 2014

Basically, if a cell value exists in a list on another worksheet then move to next row. If the cell value does NOT exist then look at columns J, K, L and M and see if a specific value exists in each. If any of these cells contain this criteria then copy the row and add it to the bottom of a growing list on another sheet. Here is what I have so far.

Sub OrdersHeld()

Application.ScreenUpdating = False

Dim x As Double
Dim ws As Worksheet
Dim numberofrows As Double

[Code] ......

View 11 Replies View Related

Cut Move Range To New Sheet

Nov 8, 2006

I convert many large files from csv to Excel. Once I import the csv to Excel I must break the worksheet into multiple depending on when a Cell in column F is equal to a "cata" of which I do successfully with a macro.

The next step in VBA I attempt; is to go 10 cells to the right of the active cell (cata) and anchor that cell address so I may copy the range from "A1" to the anchored cell address.

I have been unable to pass the the value 10 cells to the right of the active cell. I would be grateful of any suggestions.

Here is the VBA Code I've been trying. (It bombs after it finds the cell 10 cells from the active cell.)

Sub SelectRange()
ActiveCell.Offset(0, 10).Select = EndRange
Sheets("Sheet1").Activate
Range("A1,EndRange").Select 'not working'
Selection.Cut
Sheets.Add
ActiveSheet.Paste
End Sub

View 9 Replies View Related

Move Chart Range Every Month?

Aug 30, 2012

I am working on automating a report via vba code where the range of the chart needs to be changed every time the macro is run. Basically the chart is a snapshot of performance of 6 months .

For example : if the chart is already displaying Jan - Jun data , when the information for July is added and report updated the chart should be updated to reflect Feb - Jul data .

There are many graphs but have added 2 sample here for ref .

View 5 Replies View Related

Move Range Selection By Two Columns

Nov 19, 2009

I have a spreadsheet with data organized into columns in sets of two - the first column is an X value (Pixel number) and the second column is the Y value (pixel density). I'm trying to make a macro that will select the first set of columns, chart it, move the chart to the next sheet, then select the next two columns of data, chart them and move the chart to the next sheet etc. I've gotten as far as having it make the chart and move it, but I can't seem to make it select the next set of columns - I've been trying different things for a couple days now.
Code:

View 4 Replies View Related

Cut Then Move (insert Or Paste) Range

Feb 3, 2010

I'm having a toughs time with what I thought was a simple task. I need to cut and insert/paste a range of cells and then repeat in a loop. Explicitly, I have 3 columns with 2990 rows. Every 46 rows is a 'group' and I want to cut each group and paste at the top of the sheet so I have only 46 rows and (2990/46*3) 195 columns. I need to preserve the order so that the group at the bottom becomes the rightmost group........

View 5 Replies View Related

Move Correct Spelling To A Another Range

Oct 19, 2006

Let's assume I've got a list of 100 words (most spelled incorrectly and located in cells A1:A100) and I want to place only the words spelled correctly into Column B. For example, if 10 words are spelled correctly, they should go in cells B1:B10.

I start with the first word in cell A1 and move it to cell B1 to be spellchecked. If it's spelled correctly, I keep it in cell B1 and move the word in cell A2 into cell B2 to be spellchecked. (If A1 is spelled incorrectly, I move the word in A2 into B1, thereby discarding the misspelled word.)

Can I code this so that when the spellchecker box opens (i.e. word is spelled wrong) it will close automatically (e.g. [cancel]) and move to the next word?

View 3 Replies View Related







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