I have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.
View 2 RepliesBasically, I have a table of data and I want to make a VBA coding so that Any row with "cell value = 0 in column C" will be deleted.
But I can't get my code working and i don't know why. There's no error pop up either.
I have a worksheet set up where one column of data (we shall call it "A") is the result of several other columns' calculations (uses a formula referencing other cells). I have an entirely separate cell that also gets its data from other cells (we shall call this "B"). What I'm trying to do is make the shading of column "A" dependent upon data from column "B". For example, if column "B's" value is >24, I want column "A" to shade in red.
View 10 Replies View RelatedI would like to automatically color the fill of a cell based on the cell contents.
For example, if the cell content is "1" I would like the color of the cell to be cyan and if the cell content is "2" I would like the color the cell to be red.
I've tinkered with several different formulas but I just can't get anything to work.'
This works
ws2.Range("C5").Formula = "=sum(D5:ZZ5)"
guide me on the syntax when the final column reference is a variable
ws2.Range("C5").Formula = "=sum(D5:" & Cells(5, l_LastCol) & ")"
doesn't work
how to code to specify a cell in which to enter data into a spreadhseet, when the heading contains only one row.
In the attached example the headings contain multiple rows with merged cells, is there any way of overcoming this so that the entries are placed in the correct cells?
fill the column "Level 2 Area" based on the value selected from "Level 1 Area" which is coming from a drop down list. So the "Level 2 Area" will be a drop down list also based on the selected value from the drop down list from "Level 1 Area".
Level 1 Area
Level 2 Area
I am trying to code a macro to run down through a column and hide the row if it finds a cetain marker (i.e. the letter "n") in the column, and then move onto the next one.
View 9 Replies View RelatedI have a workbook with two sheets. The idea behind the workbook is an Interview Guide to be used just before an Interview. For now my problem is this.
Sheet 2 "Competencies" is just data. It stores competencies with their associated definitions and questions.
Sheet 1 "Control Page" is the sheet where the questions will eventually go. The user (Interviewer) will input data on the first two pages which will include name of candidate, date of interview etc. but they will also select 5 Competencies from the already existing drop down menus on page two. From there as the selections are made I wish for a code to copy the corresponding definition on sheet 'Competencies" to cells lower down in sheet "Control page".
I don't see a place to upload a file as I have a sample of the sheet ready to go.
My spreadsheet calculates the date on a daily basis.
I would like to create a code in my spreadsheet which uses the year and month in question.
First, 2006 is is designated as a "3"
2007 "4"
2008 "5"
2009 "6" etc
Don't worry about anything prior to 2006.
Next, the months are represented by single digits. So, January = 1, February = 2, March = 3, April =4, etc. September is 9, and October = 10 but all the numbers over 9 have to be reduced to a single digit, so October = 1+0=1, November =1+1= 2, December =1+2= 3.
Now, the formula for the Month is:
Year + current Month
So, May 2007 = 4 + 5 = 9
June 2007 = 4 + 6 = 10 = 1
July 2007 = 4 + 7 = 11 = 2,
January 2008 = 5 + 1 = 6, etc
Is there a way of coding a macro in VBA that allows you, with one macro, to enter text in one cell and at the same time enter a value to the cell on it's right?
I don't want to name the cells, but I wondered if you could add to the code a piece of code that basically said "Select the cell to this cell's right and add the value x".
I have made this vba to go out and look at 2 different locations which contain simular folder names.
The script then writes the folder names in Column A, followed by the Size of both the folders from the 2 locations. The script is comparing the second location folder name to what the first process put in Column A so the folder names will be aligned with the file sizes. It is also converting the file sizes from bytes to the most appropriate one.
The problem I am having here, is once I put the second part in for the second folder, the script is either taking forver (More than an hour) or is freezing. So I am not sure if I put it in a loop somewhere or what exactly is happening. I am just wondering if I can get some help troubleshooting and optimizing this code....
How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"
View 9 Replies View RelatedI am looking to create coding that once stock gets to a particular level a text box pops up to alert the user that more stock needs to be ordered and ideally I would like once the user selects ok for it to take them directly to the appropriate email template.
However, I don't want the text box to come up while we are waiting on the stock to be ordered.
i.e our current minimum stock level before placing our next order is 10,000 units. Order is placed and can be received within 10 - 14 days. During this time our 10,000 units will be used but I don't want an alert to pop up to remind the user to place an order as this action will already have been carried out.
I'm trying to write a code which would place formulas in the row just below the my range (normally a matrix of figures) to sum the column in that just above that. Here's what I've got so far but it doesn't work.
View 6 Replies View RelatedI am writing VBA code to do the following:
IF A1 > 50 then print array C1:E7
I have this code and it works.
I have placed the following code in my spreadsheet but it's not working!
the code after option explicit (now at the bottom) works, (thanks to Andy on here!) but all the disabling the right click and cut/copy/paste etc doesn't.
when the accounts exported to excel, all the tabs shown in the file are generated. However, i need to change tab '3' to tab '10' name to the name stated in tab 'menu'. eg tab 1 need to be renamed as 'BB' (not BB/Bunut), tab 2 to 'GEN' (not GEN/General), etc. All must be capital letter.
View 5 Replies View RelatedI have table where i have to find a value based on 2 criterias. So i have used in Excel the below formula:
=INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7
I was wondering if there is a workaround for sorting in Excel by MORE THEN 3 CRITERIA?
I have seen a couple of solution doing 2 seperate sorts but was wondering if you can write code to expand on Excels limited sorting capabilities?
i'm trying to figure out a formula that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
I am trying to figure out how to run a macro for 1 particular workbook in an excel spreadsheet. I don't want it run on any of the other workbooks in that file, just the 1. I can't use conditional formatting because I need more than 3 values (if statements).
This is what one example of data in a cell and below is the color I'd like it to turn when I run the macro:
T: 6/1/07
A: 6/8/07
Anything that has a A: (which means an actual date it happened) I'd like the cell to turn blue. If there is an RT: (which means revised target) I'd like the cell to turn red which means it missed it's target date, and has been revised. If it has a TBD I'd like the cell to turn pink (or yellow or any color really). All other cells are just white. I don't know where to find the color codes in excel as well. Other samples of what cells look like are below.
Here is another example:
T: TBD
And final example:
T: 6/4/07
RT: 6/15/07
where can I get vba coding for a simple clock.
I would like the clock to be digital rather than analogue if possible
I am trying to learn how to use it in some of my Excel application. I am just wondering if someone would be willing to help what I am thinking would be a short string of code.
What I have is a list of files that I have compiled along with a name and description in cells B3 and C3 respectively. In cell E3 what I have is the path to that file which is linked so that I can click on it to access the file. Is there any way that I can write some kind of simple code to look up the last modified date of that file whose path is specifed and place it in cell D3.
Is it possible to select a number of cells that are say yellow when blank but once someone type "N" or "No", they change to blue and if "Y" or "Yes" they change to green. I tried conditional formatting but it does not change the blank range to yellow until I have entered something into it.
View 6 Replies View RelatedIn each VBA book I've read the authors will write their code like this:
nStart = UBound(GenArray1)
nEnd = UBound(GenArray2)
Redim preserve NewArray(nStart To nEnd)
Instead of the less line method of:
Redim preserve NewArray(UBound(GenArray1) To UBound(GenArray2))
I have a spreadsheet with cells containing different names. I want all of the cells containing the word Volitility to be colored one color, all of the cells with the word Momentum colored another color, and so on. I have more than three names so I cannot use conditional formatting. Attached is a workbook that has the uncolored cells in worksheet 1 and an example of what I want it to end up looking like in worksheet 2.
View 9 Replies View RelatedI have a spreadsheet with numerical data in B1:B11 and textual data in C1:C11. The numerical data is a couple of digits, nothing big, and the textual data is always two letters, in this case AB, AC or AD. I have the options for AB, AC or AD for each of the cells in C1:C11 in a data validation drop down list. What I'm trying to do is make a VBA macro, of some sort, that acts upon when, AB for instance, is changed to AC, and then multiply the adjacent numerical value in the B column by a conversion value:
AB -> AC = 3
AC -> AD = 7
AD -> AB = 4
So for example, if I had cell B2 as 10 and C2 as AB, then changed AB to AC using the drop down menu, the macro would recognise the change, select the adjacent cell, B2, and multiply it by 3 to get 30, and put 30 in B2.
This is what I've got so far, and I'm no VBA expert, so I've probably made mistakes in multiple places. At the moment, the code below is getting stuck on active.celloffset - and I'm really not sure why. I did manage to get it working with specific cells rather than a range, but that only ever worked once and I had to close and re-open excel to get it to work again. Is my code any good, or am I coming at this from the wrong direction?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Code:
Static ov As Variant
For Each Cell In Range("B1:B10")
[Code].....
I have attached a work book to this to show what i want to do.
On sheet 'lorry 1'.
I have data in columns g to m.
I need that data to transfer to the lorry sheet as per example on sheet 'what it should look like'
It needs to segregate days and leave a line clear in between drops as per example
lorry 1.xlsx
I'm trying to run a pivot in Macro where the Pivot needs to choose the whole sheet and not a specific range as the data pasted in the sheet may fall in different range or rows however the columns are stable.,
Below given is the coding for that Macro Recording for Pivot.
[Code] .......