I have a cell that has a formula in it- "=IF(C13="","",VLOOKUP(C13,DATA,2,)).
Right now it works fine and returns a value of either "NANN" or "HZ". Which is fine.
But, what I would like it to do is, if the result equals "NANN", I would like the result to display "BURLINGTON". Or, if the result equals "HZ", I would like the result to display "CONOCOPHILLIPS".
See attached example. all details there and noted clearly.
current code:
Sub emailinstant()
Dim OutApp As Object
Dim OutMail As Object
On Error Resume Next
[Code] .......
I'm in the process of trying to get a macro to run when one cell, named "ImpVersion" changes.
Here's what I've got so far.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("ImpVersion").Address Then
Call Implement
End If
0:
End Sub
The problem I'm having is that ImpVersion doesn't always exist, as it is only added in when it's needed. When ImpVersion doesn't exist, I get a 1004 error.
Is there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
I want to make my code dynamic..
I need the following text in cell B:7 to change depending on the date.
"SAP F&R Poland Milestones for Tuesday the 9th of March 2013"
The thing is i need the date to read the date for the previous day? The reason being is that im reporting on the milestones for the previous day.
I guess im looking for a way to have the "Tuesday", "9th", "March" and "2013" to change automatically?
i am trying to make one cell automatically change to current date and time when the cell next to it has changed value. ive search other posts but cannot figure this out. so if cell A1 changes value cell B1 should automatically fill with current date and time cell A1 changed value.
View 9 Replies View RelatedI have several data validation style drop down menus, and I basically don't want excel to recognize changes in these cells and execute a calculation of the entire workbook. Since there is a large number of calculations being performed in the workbook the sheet gets really slow.
Overall the way excel "automatically calculates" really screws me up a lot. This built in functionality could be a lot smarter in my opinion.
At work I have a register/log. Its just a printout of a standard format in excel sheet. I print a month of sheets at a time and write the day and date on it by hand at the top. Is there some formula in excel by which it prints the dates as well in a progressive manner. Eg. If today is 29th May 2014 and today I print 30 copies of the register/log. The first copy has todays date on it and the the next one had tomorrows date....and the 30th copy automatically has the end of April 2014 printed on it.
I've found this code but cannot get it to work.
My workbook is called Shift Log, the worksheet is called v2 and I'm using Excel from MS Office Professional Plus 2010 (32bit)
#Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim sDate, i
retryDate:
sDate = InputBox("Enter the starting date, or click 'OK'" & _
" for the current date", "Start Date")
[Code] .........
I want to create an automatic page break at each value change in Column A, and also a page footer that "restarts" at each change in the same column.
For example, I would like a "Page x of y" footer, but I don't want "Page 1 of 3,500", I want "Page 1 of 3", etc...every time that Column A value changes.
I'm using excel 2000 and I just noticed something and am unsure how to change. I guess early on my building of a workbook I accidentally left calculation to manual. I would like to change calculations to automatic on permanent basis. I keep changing it and it changes back to manual after I close then reopen the workbook. I would prefer to change permanently to automatic....Any ideas would be a bonus. On all new workbooks I can save in automatic but I cannot change from manual to automatic.
View 4 Replies View RelatedI am looking for a vba where by the default colour of the font for new text typed in any cell of a workbook change to green or red and the default black automatic becomes secondary. The existing text in the workbook in its existing font colour must not change i.e stays in black or red.
View 1 Replies View RelatedI have this codes which will only trigger if I manually execute it. What do I need to do to trigger it automatically whenever the worksheet change.
Below is the codes:
Sub Risk_Color()
Dim c As Range, myFontCol As Integer, myCol As Integer
For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34
Case Is = 4, 5, 10, 20: myCol = 43
Case Is = 30, 40, 50: myCol = 6
Case Is = 70, 100, 140, 150
myCol = 5
myFontCol = 2
I am looking to add a function to a current spreadsheet that writes the current date to a cell when another is updated. The sheets function is to have a user record when a particular action has been completed and then remind them after a given amount of time. The user is faced with an option to input a "1" to essentially "start the timer" and then the date that is automatically input by the code will be used to compare with the current date. I have tried the following on the Workbook_SheetChange sub
If ActiveCell.Column = 9 And ActiveCell.Value <> 1 Then
ActiveCell.Offset(-1, 2).Value = Date
End If
But cannot figure out how to eliminate user error. Let me explain. If a user enters a value (will only ever be a 1 to indicate "yes") into column 9 then the date appears in the correct place (two cells to the left) If however a user deletes a value then the date will update one cell too high!? I also thought of using a cell based if statement (if j7 = 1 then today() else "" but this only updates with todays value each time you open where I want the date to stay as the day the cell was updated. The date is going to be used as a way of working out when to change the original value of the cell in column 9 to "".
I have data in table-1, in current shape
Table-1
B C D E F
Adda Zakeera 1239987801310037
Ahmed Pur East 5559998803310042
Ahmed Pur East-2 8888874805510041
Arifwala 3545555805510045
Bahawalnagar 3336666802610046
Bahawalnagar-2 1257777806610038
Bahawalpur 2206666804410044
Bhakkar 2223333805610042
I want to arrange my data like Table-2. I have tried vlookup() formula, it worked but every time I puldown the formula I have to manually increase cell # (e.g) In the row of Adda Zakeera, I have 4 values. When I use vlookup() and drag it downward the values should be shown as in Table-2 under Adda Zakeera.
=VLOOKUP($D$2, Sheet1!$B$4:$AF$90,2,0)
Table-2
D E F G H I J
Ahmed Pur EastAhmed Pur East-2 Adda ZakeeraBahawalnagarBahawalnagar-2BahawalpurBahawalpur-2
555 888 123 333 12548 2201#N/A
555 888 9987 333 77777 66666#N/A
which will calculate value of a cell when one of the variables has been changed?
To illustrate what I have in mind, an ecxample:
* User can add values to cells using UserForm
- Component name (to Cell "A1")
- Component price (to Cell "B1")
- Component quantity (to Cell "C1")
[Code]....
In the Yellow Cells, I am looking for a formula or Macro that will automatically recognise and give the correct Premises number. As an example, in the first block of Yellow cells, it should be MOR001&2; The second block should be MOR003 and so on...
One could copy and paste the Premises number for each premises, but with 100 entries or more, it becomes a tedious and time consuming task. An autonomous function to recognise the correct premises number would be much easier
In the end, this is to be used when drawing up a PIVOT so that one may easily pick up a premises number or numbers and their related charges
I have a worksheet with the following in Column E and F respectively:
ID Name
100 aaa
100 bbb
200 ccc
200 ddd
200 eee
300 fff
400 xxx
500 yyy
500 zzz
500 kkk
500 lll
500 mmm
When I input an ID number in Cell A1 and if it is found in the above table, it should bring all the names corresponding to that ID in a cell comment.
The ID Number is repeated in the above table because the names in Colum F are the dependants name for that particular ID.
I am trying to have one cell automaticall input infromation based on another cell. For example, if A1 = 10, then I want B1 to automatically equal 20.
The formula I am using in B1 is as follows - =if(A1="10","20","")
I have 2 problems though:
1st - I don't want to put the furmula in any cell because other information is put in there also. I tried putting it in conditional format but I don't think it is meant for such usage. Also, I already have something in data validation. so I can't put anything there because to my knowledge you can only put one validation per cell.
2nd - I have multiple numbers (around 7) that I need to be automatically inputed along the columns.
How to automatically change the colour of a cell if another cell is a certain colour.
For example, if Cell A is red, then Cell F also needs to be red.
If there a way for this to automatically update?
I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).....
View 14 Replies View RelatedI copied the code that was used to inserting the date when the cell next to it was updated, the original post can be found here: [URL]
The Code below will check a range of cells between c3 and c20 and if I make a change to the value in any of them, then the cell to the right of them will have the date inserted. I've had to modify the original code from the other post a little bit to stop an error appearing when I insert a new Row:
[Code] ....
On the first example that was posted it all ran ok until until I attempted to insert a new Row, then it would put the date into about 5 of the cells to the right of where it should do and I received an error message with the usual Debug stuff on it. It would also delete my column descriptions that I had on Row 2.
Would it be possible for it to not auto insert the date on any new blank row that I insert?
What would be the correct range for me to get the code to work on c3:infinity....
Is it also only possible to enable macro's and code like this in the current document instead of every document that you load through excel.
I want to learn how i can able to do a function or ... to LOCK a Cell automatically after editing or typing and no one able to edit it again without entering Password.
View 14 Replies View RelatedI want to be able to enter a single digit figure into a cell and have the cursor move automatically to the next cell to the right. then enter a figure in there and have it do the same thing. is this possible and, if so, how?
View 2 Replies View RelatedI have built a spreadsheet for my business which has an annoying feature I can't get rid of. When I click in a cell I want to enter data in the spreadsheet automatically highlights that cell and the neighbouring cell. I have to move away then back with the arrow keys to get back to the single cell I want to enter data in.
View 2 Replies View RelatedIn a spreadsheet a macro calculates some percentages and populates a small table, copies the table and generates an email. The table includes conditional formatting to fill a cell in green if it is above 95%, or red if it is below 95%. The spreadsheet works fine, but with every possible solution I have tried including converting to HTML, the color cell is always red in the outlook email. I am guessing it is because in the transition to the email, the small table no longer is located in the cells that are used to do the color format.
Whats odd is when I copy and paste into outlook it works fine, but I have been unable to figure out how to paste into the body of an email using excel vba. I have read many posts from people with similar issues, most have no solution. I am currently attempting to convert the table into a gif and try inserting that into outlook. Due to the proprietary nature of the spreadsheet, I am unable by policy to publish any code.
With "sheet1" there's a table c14:m38 with all formulas. But i want to expand the table if cell c38 contains a value or text.
Is it possible to automaticly insert a row containing the above formulas in row c14:m14?
I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.
Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.
know if there is any way of getting a cell produce an X (for example) when it is selected and blank when selected again? Basically a tick the bok response. I know i could use a drop down list but i was hoping for a quicker method as a lot of ticks accross a lot of cells are required.
James
I have a drop down list in cell F1 with all the months. In G1, I would like it to automatically display the number of days in the selected month. Of course it depends on the current year (due to leap year, etc).
View 14 Replies View RelatedBasically I have a spreadsheet to monitor behaviour in a high school. There is a column in the sheet with the heading 'PT Action Required?' which has a drop down menu where users can either select "Yes" or "No".
I would like the spreadsheet to automatically send me an email to inform me if a user selects "Yes".
So far I have:
[Code] ....
However, nothing happens.