Row Shifting Macro
Aug 19, 2009
This is far too complicated for me to program.
Ideally, if at all possible, I want a macro that will align every row of my data by the cell which has the value closest to 20. i.e.
8 10 12 14 16 18 20 22 24 26 28 30
9 12 15 18 21 24 27 30 33 36 39 42
5 10 10 10 12 10 14 50 54 59 62 72
1 10 40 42 44 46 48 50 56 48 62 70
would become...
8 10 12 14 16 18 20 22 24 26 28 30
........9 12 15 18 21 24 27 30 33 36 39 42
5 10 10 10 12 10 14 50 54 59 62 72
.....................1 10 40 42 44 46 48 50 56 48 62 70
As you can see, it looks quite tricky.
If it's too complicated, I can do it by hand (very time-consuming) and a simple macro button would help. I'd need something that will select the whole row of the active cell, and shift it all one space to the right. I'd have another button doing it to the left.
View 9 Replies
ADVERTISEMENT
Jul 26, 2007
I have a challenge for any macro wizard out there - this is something I really need and I'd be happy to reimburse whomever can help with an iTunes gift card or something.
I regularly receive a dataset such as the one you see here on the tab called 'Data comes in varying # of rows' (either link should work):
[url]
[url]
The dataset comes each time with a different number of rows, and what I need to do is combine all the 'Issue' and 'Issue Description' columns (there are 8 total) into just two columns.
I think this should be relatively easy for someone who has a better knowledge of macros by simply copying and pasting the data on top of itself (as I have done on the next tab called 'Want to change to this format'), and then simply deleting from the second section the 'Issue #1' and 'Issue #1 Description' columns, and shifting all the data left by two columns.
If you repeat this process for the third and fourth sections (for the third, you must delete 'Issue #1' and 'Issue #1 Description' and 'Issue #2' and 'Issue #2 Description' and shift the data left, while for the fourth you must delete Issues and Issue Descriptions 1 to 3 and shift the fourth Issue and Description to the left), you will end up with the columns A, B, C, and D as I have on the 'Want to change to this format' tab. (The extra columns after column D should all be deleted.)
Finally, the 3 extra header rows that are now at the top of the bottom three copied-and-pasted sections should all 3 be deleted.
View 5 Replies
View Related
Feb 12, 2014
Any way to automatically shift the numbers in a calculation range without having to manually enter for =SUM every time.
The spreadsheet shows a listing of the 20 most recent golf scores in one column, and the other column adds the best 10 of the most recent 20. Currently I am doing this by hand, and just entering the sum in the top cell. Is there a way once I enter the newest score for excel to read that and automatically adjust the formula?
View 7 Replies
View Related
Oct 23, 2008
I'm looking to create a program that enables the macro to run with placing the desired cell blocks (A3:F12) from Sheet1 into Sheet2, and when i press the shortcut key again, it automatically places the cell blocks underneath the previous data that was pasted in the Sheet2. The goal of this is to keep a history of the data into the Sheet2.
I'm new to this, especially with VBA but is there any possible way to create this type of macro program?
View 12 Replies
View Related
Jul 23, 2013
I have an array defined from 1 to 100; however, only six elements of the array have a value. I need to add a value in between the ones already in the array. I have tried this snippet already with no luck.
Code:
For Z = a To MinPos
WOList(Z + 1).F1 = WOList(Z).F1
Next Z
WOList(MinPos).F1 = CalcValue
A is defined as the total number of elements with values in the array (6 values in this example). MinPos is defined as the location of which the value needs to be inserted (lets say index 2). I tried to shift all the elements to the right one, and add the number into the array.
View 4 Replies
View Related
Feb 13, 2009
I need a macro to shift column data to the top row for a given cell range sharing common data in row A.
I need to get from this:
Name Fruit Meat Dairy Bakery Alex Apples Alex Milk Alex Bacon Alex Bread Bill Steak Bill Eggs Bill Chicken Bill Oranges
Carl Butter Carl Cake Carl Milk Carl Bananas Carl Pork Carl Peaches Carl Chicken
To this:
Name Fruit Meat Dairy Bakery Alex Apples Bacon Milk Bread Bill Oranges Chicken Eggs Bill Steak Carl Bananas Chicken Butter Cake Carl Peaches Pork Milk
It would be nice if the shifting would sort the data as well.
View 9 Replies
View Related
Sep 21, 2008
I had a sheet that had a web query imported into it which took up columns A to F. The web query varies in length upon every refresh. In columns G onwards i have lots of formulas that read the information from the query to produce results.
When i imported the web query, on some refreshes it has the ability to push my formulas down in certain columns. I'm assuming this is because the length has changed of the web query maybe? Although it can do it on the opening import of the query which i find strange.
I was told the best way around this would be to put my web query on another sheet, however this doesnt appear to have fixed my problem.
Lets say for example #Sheet1!A1 looks at #Sheet2!A1, #Sheet1!A2 looks at #Sheet2!A2 and so on. This will work fine. When the web query is imported into Sheet 2, its data goes down to cell A72. Upon another refresh though, the length will changed and now the data might go down to cell A81. However, #Sheet1!A72 will have changed its formula to look at #Sheet2!A81 and my formulas on Sheet 1 will now read as follows:
#Sheet1!A69 = #Sheet2!A69
#Sheet1!A70 = #Sheet2!A70
#Sheet1!A71 = #Sheet2!A71
#Sheet1!A72 = #Sheet2!A81 <----- Problem!
Basically the problem with this is Sheet 1 will not be including Sheet 2 A72:A80 which i also need.
View 9 Replies
View Related
Nov 16, 2013
I have been trying to produce a spreadsheet that works out the correct gear and rpm depending on shift limits across six gears.
In column M I have all of the rpm values for 1st gear, which I require in column S until the shift limit for 1st gear is reached, now I wish to have the rpm values for 2nd gear (Obtained now from column N) displayed in column S up until the 2nd gear rpm limit is reached and so forth until 6th gear. I have tried an IF() statement down the entirety of column S but this only seems good for taking into account two columns at a time, whereas I require six all dependent on the respective gear shift rpm. Also the position of the shift rpm will change relative to other functions in the spreadsheet, so I can't simply have different IF() statements lined up appropriatly in column S.
So if my shift rpms are in column B I wish column S to read column M until M>B1 then it will need to choose values from column N until N>b2 where it then picks column o and so on.
View 6 Replies
View Related
Aug 13, 2009
I was wondering if there was a way to create a two cell dynamic range that doesn't expand, but instead shifts.
The cells that I'm interested in are always at the bottom of the column of data. For example, the first two cells I want as my range are C13 and D13. Then, for my next use of the range, I would like it to include C14 and D14.
View 11 Replies
View Related
May 4, 2014
I began to ponder a way to copy down cells so that the copying of the formulas results in references as shifting horizontally instead of vertically. One particular reason that this occurred to me had to do with my attempting to use Excel to make more orderly text copied from Adobe.
So, for example, if I copied text from Adobe, I would paste it in Excel. Thirty lines of text would past vertically into a column, from Row A1 to Row A30, with each line of text in its own row. I wanted to figure out a way so that in adjacent columns, I could put it so that I would have a set of formulas reading in =A1 in Column C cell/row 1, =A2 in Column D cell 1, =A3 in Column E cell/row 1, =A4 in Column F cell/row 1, =A5 in Column G cell/row 1, and so forth. I realized that if I copied this down, in the second row, the result would entail references to A2, A3, A4, A5, A6. I would prefer that the references update to A6, A7, A8, A9, and A10.
View 5 Replies
View Related
Feb 4, 2014
I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).
As filter I would like to be used A,B,C columns.
EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
View 1 Replies
View Related
Sep 7, 2012
how 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].....
View 1 Replies
View Related
May 8, 2014
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
View 9 Replies
View Related
Nov 4, 2008
I have the code below that clears colums B-J and L-N in whatever row you activate a cell in. When a row gets cleared, how do I shift all the other rows up to eliminate blank rows in between the data real-time? I've deactivated the cut function already since this messes up references in the worksheet, so it would have to only use copy, paste, and clearcontents functions. Also, I don't want to DELETE any rows, just essentially shift the blank rows to the bottom. So when a row is cleared, all others shift up and the blank one goes to the end of the data that is available for data entry, so rows 17 to 116 are always available. But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared. So at any given time, my range will always go from row 17 to 116. This is Excel 2007. Here is the code I have to clear rows that can be built upon.
View 14 Replies
View Related
Oct 16, 2009
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
View 10 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Jan 26, 2009
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
View 14 Replies
View Related
Feb 1, 2009
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
View 5 Replies
View Related
Sep 17, 2009
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
View 13 Replies
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Mar 4, 2014
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
View 1 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
Jul 16, 2007
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies
View Related
Dec 15, 2008
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View 12 Replies
View Related
Jan 11, 2010
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
View 3 Replies
View Related
Jan 27, 2010
Macro to export and import a module and assigning different macro automatically ...
View 9 Replies
View Related