I have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this
this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way.
Sub FinishOU()
Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range
With Application
. ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
Set wb1 = ActiveWorkbook
With wb1
.Save
For Each sh In Worksheets
Set TestRange = sh.Range("A1:S4")
For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
I have a sheet in my workbook with at least 180 small tables, there may be more. I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example: If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
First, to i change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.
Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. but its hideing the formulas. once i protect the cells it wont let me to edit the worsheet (eg- cell height, cell width)
I am looking for the best way to change the font of cells in a Worksheet that contain a formula. I have used Go To-Special-Formulas but have one slight problem with this method. It highlights all cells that contain an "=" sign. Some cells may be linked (ie cell A2 may say =cell A1). How would I change the font only for cells with a Function such as Sum or Vlookup and not for cells that simply link to another cell?
In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.
I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.
I have a spreadsheet that holds the NHL schedule for the season. (See thumbnail)
In the picture I've highlighted the formula I use to determine how many games each team has over the next 7, 14, 21 and 28 days. What I've been doing is deleting the column for each day after that day has passed. (So tomorrow, I'll delete column K, and all the other days will move up one. Sunday will become column K)
My problem is that the formula =7-COUNTIF($K3:$Q3,"—") in cell G3 will read =7-COUNTIF($K3:$P3,"—") after I have deleted Saturday, and thus will only count 6 days worth. (The 14, 21 and 28 columns will only count 13, 20 and 27 as well.) Is there any way I can write this formula so that it stays as K3:Q3? Right now each day I modify the 4 formulas for the 1st team and then copy/paste then over the other 30, but this is rather tedious.
I have a workbook, everyday 2 new worksheets get added to reflect the previous day's work. How can I reflect in a formula to go to next sheet instead of the conventional naming 'Sheet1' G7?
I have a 2 columns (A and B) next to each other that is using a Sum formula and other multiplying formulas. They reference 3 cells in a different column (C) to come up up with the answer for A and B. Therefore A1 and B1 reference C1 C2 and C3 (and possibly if want to use in future column D with D1 D2 D3).
Then in A2 and B2 I want to reference from C4, C5, C6 Then in A3 and A4 I want to reference from C7, C78, C9 etc.... and so on...
Instead of retyping formulas for each row in A and B, I want to copy down, but I getting the wrong answer when I do that.
I am therefore assuming I typing the formula wrong. How do I type it correctly so the cell reference changes automatically when I pull down columns A and B?
Formula for Column A: =SUM($G28:$G30) Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17
Therefore next row should be referenced from G31 to G33 and H31 to H33
But when I copy it only adjusts it for 1 reference down.
I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.
I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.
Is there a way change the central database file without affecting the vlookup formulas in the dependent files?
I'm coding a userform where there are some comboxes which are popolated by values coming from Sheet2. Up to now I work it out (maybe its not elegant but it works).
Now I would like that when the user selects one combobox the values of the other comboboxes are set accordingly to the grid in Sheet2.
I am using excel 2007 and when I change cells my formulas do not automatically refresh. I have set it to automatic and the calculations will not refresh. How I can force a calculation. I did the control alt f9 and nothing seems to work. Any macro that can force all calculations.
I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.
In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.
I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.
I've created a formula that concatenates 3 parts in VBA. column A, B, C.
- colunm C is a general info column and can be general text OR a date value. - if its a date value it ends up being concatenated as the sytems value i.e. col A1, col B1 and col C1(16/01/2007) becomes A1+B1+C1(39098).
I need to enter a range of dates and have any date that falls within that period (regardless of year) highlight. I currently have it referncing two different years, but I need more range than that and I can't get it to highlight dates in July if they were dates that happened 1, 2, 3, etc. years ago.
I have two columns... 1 with a list of dates (ex. 22-Apr-07) and the other which states either a pass or fail. Ignoring the day and year, I'm trying to get the total # of references for each month, and only count those with a 'pass'ing grade.
I am having trouble entering a formula that measures how many times "Open" occurs in one column IF another column's entry is between 2 dates or within one particular month time frame. I've tried a bunch of formulas but can't seem to get anything to work!
I am working with an Address Worksheet where the house#, street name and street type are 3 seperate columns. I need to combine that data into one "Address1" column. I can use a Concatenate() formula to combine the data, but I need to be able to have that data output to a new column, independent of the formula.
The problem that I am having is if I try to "copy" the concatenated data to either a new column or new worksheet it only wants to copy the formulas. I don't need the formulas anymore, I need the data.
Every so often I get a spreadsheet form some one else, and when I start to edit it formulas turn immediately into values.
For example, I enter a =10/2 ,when pressing enter, the cell displays correctly the result, 5. But when I click into the cell, instead of having the formula
=10/2, the only value inside is 5.
How do I keep this happening ??? I want the cells to keep the formulas.
I have a estimating workbook with a number of worksheets to provide a estimating process. this includes the final worksheet with the estimate on. This is fine if i am printing it to send to the customer or sending as a PDf.
However i need to save as a stand alone excel workbook. But all pre-formated and only with the values in it and not the formulas. It would be easiest for the users if it could be done with a button on the quote sheet (ie done with as macro as some users are not very IT literate and do mess up).
How pasting values/formulas only works. I have a spreadsheet I've been using to record product sales, commission, shipping etc. It currently has three pages of records. But now I have a new spreadsheet I want to use with basically the same 'look' as the old one (same columns, entries per sheet, etc) but using better formulas. I don't want to manually input all the data from the old one and I thought I could copy all the cells from the old one and then paste values only into the new one. But when I do that, it pastes the values but also deletes the formulas in the new one. So then I thought I could copy all the cells from the new and paste only formulas into the old one but then it deletes all the values in the old one. Is this what is supposed to happen? And if so, is there a way to achieve what I want?
I need to ignore all data in a row (it won't be averaged, summed or anything else) if a particular value in that row is "0". I need to be able to see it but just not have it counted. Change the 0 to something else and all the data in that row is included in formulas as usual.
I have a column of values (Sold Price) for which I need to figure extra fees related to that cost, but they vary depending on the cost. The different fees associated with the Sold Price are: A. If SP is $.01 - $25, fee is 10% of SP B. If SP is $25.01 - $100, fee is 10% of 1st $25, plus 7% of remaining balance C. If SP is $100.01 - $1,000, fee is 10% of 1st $25, plus 7% of next $25.01 - 100.00, plus 5% of remaining balance over 100
So I need one formula to go into the fee column which will work with all values of the SP column.
