i have a list of dates from A1 to A31 , say in january 2009. from the 1st to the 31st, Im trying to get a macro that when i run it it removes all these dates and replaces them with feburarys dates 1st to the 28th. run the macro again and it changes the dates to march etc etc.
View 4 RepliesI 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?
I have a problem in changing a date automatically in a macro.What I want t to do is the following: On sheet "Stock Count" at cell I1 is a date. I want to open new sheet and copy "Stock Count" to this new sheet then rename the new sheet to the date on "Stock Count" cell 1. I have the following:
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Stock Count").elect
Range("I1").Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = 14-Apr-14"
Sheets("Stock Count".Select
Range("A1").Select
ActiveCell.Paste
I have an excel dataset and I have dates entered differently. For example they could be written as:
MM/DD/YY
MM/DD/YYYY
M/D/YY
M/D/YYYY
....
....
Ultimately what I am interested in doing is writing a macro (and saving it in its own file) that can be run on any excel spreadsheet that will change the dates to be formatted the same: MM/DD/YYYY
The issue is that a date written as 10/10/27 would need to be changed to 10/10/1927 (so adding the 19).
Is it possible to have a macro run when a date is changed each month instead of clicking on the button. I have a few macros that are ran each month in a spreadsheet (manually) but would I would like to have them run automatically when the date is changed in cell a4 each month. The date in cell a4 is always the last day of the month. Also the name of the workbook changes each month
View 9 Replies View RelatedWhen the date changed today it stopped working and I cant see why as i have altered the dates that it looks at.
View 4 Replies View RelatedI need a macro that will create a new blank worksheet, move it to the last spot and paste the entire contents of the sheet named "master" into the newly created sheet.
I have tried to do this with the recorder but have been unable to get it to work.
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?
I want to change value if i change products from drop down list.....
For more information please find attached file: Book.xlsx‎
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
i mtrying to get a validation list change depending on what is chosen in another list. I have attached an example, Yellow box is my validation and weather List 1 or List 2 in chosen I want the red box to be a choose of the list attached to those options. I've tried to put an If in there but I’m al a loss.
View 3 Replies View RelatedI have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies View Relatedi have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC
I'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.
if it is possible to have a selection from a list change the list for another cell?
For example, if I have a drop down list in A1 which is "Apples, bananas, pears", and I select "Apples", is it possible for A2 to have a list of "Green, Red, Yellow", and if I select "Bananas" in A1, for A2 to have a list of "Yellow, Black, Blue"?
Is there a way to either change this so that it lets me to select the whole area or a way to make a macro to do what this does to one cell?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("M13:IR458")) Is Nothing Then
Select Case Target.Value
Case "1"
Target.Font.ColorIndex = 20
Target.Interior.ColorIndex = 10
Case "Good"
Target.Font.ColorIndex = 2
Target.Interior.ColorIndex = 35
Case "Stable"
Target.Font.ColorIndex = 2
Target.Interior.ColorIndex = 27......................
i have a excel database made up of about 5000, points of interest every thing from nightclubs, pubs police stations, restaurants, etc all in alphabetical order A TO Z
my database read like this
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F
POINT NAME ADDRESS POSTCODE TYPE DETAILS ONEWAY ST
odeon cinema leicester sq WC2 cinema lve on left no
COLUMN G IMFOMATION
lve cinema on left this is next to the raddison hotel
what i would like to do is change the order so that every thing is listed in post code order W1 W2 W3 W4 EC1 EC2 EC3 EC4 WC1 WC2 etc so that i can search in pacific post code
I have 4 lists and I have some code that let's me select an item in 1 list and get its corresponding value in the second list. I have managed to convert my lists to combobox on doudle click by using some code and now I want to be able to hit enter and the value that I searched for in the first combobox gets its corresponding value in the second combobox.
View 1 Replies View RelatedI have a list of names and they are in the following format :
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
How can i list them in the following order ?
A1
B1
C1
A2
B2
C2
A3
B3
C3
This is quite a long list containing about 5000 names in total.
I have several column of numbers with like-numbers grouped together. I need to insert a blank row after every group of numbers. For example:
883
883
(need a blank row inserted)
772
772
772
772
(need a blank row inserted)
991
(need a blank row inserted)
The ranges are all named but I don't know how to write a macro to put in the blank rows.
I have a dynamic list of names from B2:B500. I want to write a macro that finds all the unique entries from that list and pastes it to AD3:AD501 everytime the macro is run. What would be the code for this procedure.
View 9 Replies View RelatedI have a coworker trying to create a dropdown list and is using wingdings for the checkmark, x, and other symbols for filling out for. Everything works great except when you click in the field the drop down box doesnt show the wingdings font but the corresponding arial font. Is there a way to change the dropdown box to wingdings because currently the dropdown box shows various 'u's with different grammatical stresses.
View 2 Replies View RelatedI have a formula that basically copies a cell into another workbook if 'other' is entered into the column to the right. i,e cell A1 has 'other' B1 has '100', the figure in B1 is placed into another workbook cell. The results are only shown if a month is selected from a drop down on the 1st workbook with the meta data.
What I want to do is to add a command to the formula below so that it will recognise that after the first item is entered it will look for the next data item and not just repeat the first….
{=IF($L$92:$L$580="","",INDEX($L$92:$L$580,MATCH(1,(TEXT($C$92:$C$580,"mmmm yyyy")=TEXT($E$5,"mmmm yyyy"))*($L$92:$L$580<>""),0)))}
In the attached worksheet is an example of what I want (table 2) and what I'm currently getting (table1)…
Is it possible to change the Pop Up box formatting after creating a List.?
View 7 Replies View RelatedIn Excel 2007; I need my drop down list to then change the next cell down to a series of different values? How do i do this? i have created the drop down list, but can't link the values to the item selected in the drop down list. The JPG attached shows a basic set up of what i am trying to do: The drop down list contains various product models (yellow) Once selected this then changes the m2 value (red) which is linked to the formulas in orange; product factor is entered manually.
View 3 Replies View RelatedI would like to find and change certain values in a list. For example I would like to change TG1050 to 1 from a list that contains the following values
TG1050,TG10500, TG420, TG350.
When I run my VBA the 2nd Item in the list changes to 1. I also need to keep the commas seperating the value.
I have attached a file: I want to select the currency in Data!F7 and based upon the selection I want the number formats in Prices!D17:D19 to dispaly the relevant currency symbol.
View 3 Replies View RelatedI inherited a spreadsheet with a combox box, drop down list for user to select a project by name. The cell right below uses a lookup function to select the code listed with that project name from a list on a separate worksheet. When I edit the codes on the list used as the source for the lookup, the new code will appear as it should.
I want to edit the drop down list that appears in the combobox. I cannot find the source for the drop down list. Any ideas where/how to look for it?
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?
I created validation list using Validation option in Data menu. Now I would like to change font for that list.
View 3 Replies View Related