I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.
I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.
Imagine the spreadsheet contain informations. (For eg, columns A to Z all got contents) Then i want to delete columns A, B, C, K,L,M, X,Y,Z
Problem faced:
When i delete A,B,C D,E,F . . will shift to the left.. becoming A,B,C I need to use macro buttons so that next time i dont need to remember what to delete and what not to delete.
I am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
what i want to do is have an area where i can input data and then i have three options of inputing the data or resetting data that is already in the list of data so i start to create a list of data but i can reset/change values if i wish for example quantity. i would also like to have a way to subtract or add a number from the quantity assigned to a code/name of some form. i know that's quite a bit
I have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.
The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).
Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?
I have a very simple inventory spreadsheet that I used to keep track of certain products. The issue is that I often have to insert new columns and when I do, the formulas get convoluted. Is there a trick to making them follow the structure of the formula before?
My formula is =SUM(GT4:GU4) and appears in the Running Total column. When I insert two columns - a white column where an order will be inserted, as well as a running total column. When I perform the insert, the formula for the inserted columns, as well as the set of columns immediately to the right require corrections:
Inserted columns: =SUM(GT4:GW4) [Should be GV4:GW4] Columns to the right: =SUM(GV4:GY4) [Should be GX4:GY4]
I know how to manipulate the formulas correctly, I would like to keep a clean file for my predecessor.
I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.
For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.
Here is the Sub Macro1() ' ' Macro1 Macro ' Macro recorded 6/30/2008 by kmayfield ' ' Range("C3:F3").Select ActiveCell.FormulaR1C1 = "Jan-2008" Range("G3:J3").Select ActiveCell.FormulaR1C1 = "Feb-2008" Range("K3:N3").Select ActiveCell.FormulaR1C1 = "Mar-2008" Range("O3:R3").Select ActiveCell.FormulaR1C1 = "Apr-2008" Range("S3:V3").Select ActiveCell.FormulaR1C1 = "May-2008" Range("W3:Z3").Select
I'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.
I have a spreadsheet that I can modify. It currently has a three product column but I need to insert 2 or 3 more columns to make 5 plus the total at the end. It also have a summary sheet. I insert the 2 columns and somehow got the formulas flowing. However when I hit the summary page its not showing results for those two new columns in the total. I am lost. I wish I could post the sheet. Its a multiple product break-even analysis exercise.
lets say i have 200 used columns. if i delete 150 of them and then (without saving the file) try to add 100 more i get an excel has reached its limit error. theoretically 200-150+100 = 150 columns, which should be well within the 258 column limit. but excel still gives me the error )its like its calculating 200+100=300 > 258). HOWEVER, if i save the file after deleting, i dont get any errors at all. is this a "feature" of excel and if so is there anyway of circumventing it? because i dont want to save the file prior to adding the columns (i do the whole thing in vba). actually i dont want to save the file at all unless the user click on the save button or choose save/save as from the menu.
For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
firstly i am cross-posting this topic so here is the URL to the same thread in a different forum.
[url]
whats up everybody? i have to use two excel worksheets that someone else already created to make CSV (comma seperated value) files. these CSV files are to be dumped into a database. the problem is that the table has more fields than the spreadsheet does (the spreadsheet has 3 fields - network, mask, size - and the table has 10 fields). i can insert one entire column before (to the left of) network (for the ID field in the table) without a problem, but when i try to insert entire blank columns after size (to the right of the pre-existing columns) it only makes columns for the first 14 rows. i know this because when i save it as a CSV file there are only 6 extra commas for the first 14 rows. is there a way to insert an entire blank column for the entire spreadsheet (as in all the way to the bottom)? i dont want to have to manually type in all those commas. i am using excel 2002.
I have a range (of 2 rows) that are set up as a header row and sub row. I want a macro to insert these above the active row (ie. where the user places the cursor), but when I select and copy the range in the macro, I don't know how to refer back to the 'active row' because that's not active anymore.
I'd also like the cursor then to be placed into one of the cells in the new row, ready for the user to start editing.
Suppose that i have a table that displays different metrics for each month of the year. I'm dealing with waste management at a thrift store so I'm looking at the weight of donations salvaged, weight of donations sent to landfill and weight sold and so forth. It would look something like this..
weight sent to landfill weight sold
january 500 450
february 350 390
march 400 789
In my dashboard I'd like to be able to sum the total weight salvaged for certain months like Jan-Feb or Jan-March.
I've named the ranges such as "weightsalvaged.Jan_Feb". So for the sum of the weight salvaged, I have the formula =sum(weightsalvaged.Jan_Feb)
I would like to have another cell where I write "Mar" and the name of the range in the formula automatically changes from
=sum(weightsalvaged.Jan_Feb) TO =sum(weightsalvaged.Jan_Mar)
I have a long row of text cells in a range. Starting with the 1st cell I want to insert 3 empty to the right, then repeat for the next cell that had originally butted up against the first cell until 3 cells have been inserted for each cell in the range.
I would like to insert a formula in a column, the formula is in column D and must be something like "=C1-B1" for row 1, "=C2-B2" for row 2,...etc. I would like to do it without using a loop cycle in VBA...I have attached an example of the data I'm using..
I am having trouble looping through a range and inserting a formula where a condition is met.
My range is "h9:i"
My current code inserts a formula in column i when there is a value in the adjacent row in column h:
[Code] .....
However, because there are so many rows (12,000+) it is taking a considerable length of time to run.
Some of the cells in column i will already contain the relevant formula, so to speed this up I want to insert the formula in column i only when the cell value (in column i) is not null, as opposed to inserting it for all cells within the range.
I know how to loop through the range, but the problem is the 'H9' cell reference in the formula in the code above will need to change depending on what row it is being inserted into- so if it is going into "i31" this will need to be "h31".
I want to maintain a history of exam results held on different dates
For this purpose I created a file with three sheets named Inputs, Working & result history. Description of all sheet is as follows :
“Inputs” Used as main Input file to feed marks for each subject on certain exam date. “Working” Linked with sheet inputs and perform calculations ( total marks, % etc) “Result History” Date wise history of exam results based on sheet (working).
Required Solution Steps:
1.Score entered for different subjects in Sheet “Inputs” for a particular exam date. 2.Obtained marks are totaled and score % calculated based on scores entered in sheet inputs. 3.Date wise history of exam results recorded based on working sheet.
To transfer/copy/paste results from “Working” sheet to “Result history” sheet I recorded a macro which is linked with a button. This macro copy record from working sheet and paste it as value to result history sheet....
This may seem like an odd request for help, and there may be other options that I am unaware of but....
I am looking to maintain a top header row and a bottom footer row. The sheet is 300 rows deep and If I put a freeze pane or split, the header or footer ends up scrolling off of the viewable screen.
The header range is A1:G6 The footer range is A301:G304. I am looking for 2 sub()'s. The first will check the DATA range A7:G300 and hide all empty rows between the header and footer ranges. The worksheet will display lets say 23 rows of data if the user needs to enter data in a new row I would like them to be able to run a macro that will hide the first data row and unhide the first empty data row below the last row that is not empty (in the data range), and a second macro that would do just the reverse. If there were any hidden rows at the top of the data range, the first hidden row above the first unhidden row would be unhidden, and the last row of the data range would then become a hidden row.
In essence it would allow the user to scroll through the data range and never loose sight of the header range & footer range.
I'm going to attempt to explain the problem I am having with a project in Excel. I have tried to search using several different terms but have not been successful in finding an answer to my problem. I came close with a MS help document entitled "HOW TO: Sort Cells Without Sorting Linked Cells in Excel 2000". But that document does not directly address the solution. I think there is a clue there but I can't figure it out.
What I'm trying to do: I am trying to sort a data list that is one worksheet without affecting the links I have created to that data in a second worksheet. I have two worksheets; one holds the database, or list data; the other is a formatted price list where I have created links to the data in the first sheet. The idea is that I want to be able to manipulate the data in the data list and make changes to it while keeping the price list in a fixed format but with updated prices. I am using relative links and I have had some success in the past with these links automatically updating when inserting rows or cells, or moving the cell containing the formula. But this time I am trying to sort the data. When I sort the data the formulas in the linked cells simply point to the same cell reference as they did before the sort, producing the wrong results. I want the links to follow the referenced cells so that the results remain the same on the price list even when the rows in the data change position. For example:
The data on the worksheet named "Database" is in a list from A10:P65. I sort this list by two columns, first by Categories then by ManuPartNo. After I sorted the list I went to the worksheet named "Price List" and set up links to the cells in the data list that contained the correct information for my items. Some of my items are a combination of two or more rows from the data list. This works fine. If I make a change to the data in the list the price list automatically updates. If I insert a row or column to the data list the formulas on the Price List continue to work just fine. But, if I sort the data again, the formulas on the Price List no longer point to the correct cells in the data list. The formulas remain unchanged, but the rows of data move. I expected the formulas to update just like they do when I insert a row or move a block of data with cut and paste. Is there a way to force Excel to follow the rows as they move after a sort? Excel does this automatically when inserting rows. Why not with sorts? Where can I go to learn more about how to do this?
I am working on a macro to insert a row below if a cell with a validation list has an item from the list selected. The new row needs to maintain the same format and formulas as the original row. To explain this better I have attached a very generic spreadsheet Called Custom Order. In this example cell A3 has a validation list. If the user selects one of the items in the list... I need the macro to insert a row below and maintain the format and formulas found in row A3.
Also this is posted on another forum. http://www.excelforum.com/showthread...=1#post1893257.........
i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet
i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.
Numbers with Dr as suffix denote positive numbers and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.
I have a userform that opens up to full screen, however, when i designed it everything is central and in its place, but when the userform opens full screen everything is pushed to the left and not using all the form.
I know that in VB2010 u can use docking and table layout panels for this sort of thing but VBA 2007 which I am using with excel doesnt seem to have these options?
