Subtract From Last Cell Modified By Macro?
Feb 3, 2014
I'm building a work effort tracker for employees to record what they do throughout the day and submit it via email, and I've got everything but the "Undo" button working. Right now I have 13 buttons that add +1 to cells C9:C21, and a submit button that copies those cells into an email. What I'm trying to find a solution for is if someone clicks the wrong button, say clicks Inbound Call instead of Outbound Call, which would +1 to C9 instead of C10.
I've read that coding an undo macro button is pretty much impossible, but is there a way to locate the last cell modified by a macro and subtract 1 from it? Or possibly identify the cell contained in the last macro run and -1 from it that way?
View 1 Replies
ADVERTISEMENT
Feb 18, 2014
I need a macro to identify the largest value across columns I2 - AS2. Once found, I need to replace the value with value found minus the value in BQ. The final step would be to highlight the cell that was changed across columns I2 - AS2, then move to the next row and do same until I reach the last row in the worksheet. The number of rows vary each day but the columns do not.
I have written the attached code, which identifies the largest value in the first row, but I don't know how to replace the value found with the new value and then move to the next row until it reaches the final row for the day.
In the attached file, my largest value sits in AC2. That value needs to be replaced with 486.17 (1,175.37 - the value in BQ2 of 689.20). The cell should then be highlighted so I can quickly identified what cells have been changed.
View 3 Replies
View Related
Apr 28, 2012
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
View 6 Replies
View Related
Oct 14, 2009
I am trying to perform the following, but am unsure if a macro is needed, or if there is a way to do it by possibly modifying the transpose function. Here are the steps required:
1 - copy a selected range of cells from a single row in one worksheet (i.e. D3:AE3).
2 - paste the data from those cells into a selected range of cells from a single column (i.e. A1:A14) in a separate worksheet BUT skip the cells from D3:AE3 that were blank instead of having them create blank cells in the column of the target worksheet
3 - Need to maintain a reference back to the original row of cells so that if their values change (i.e. D3 from source), the corresponding values in the new column (i.e. A1 in target) are auto updated.
View 5 Replies
View Related
Jul 3, 2014
Below Macro which I am using to extract .PDF files. Now, I also want to see the Date moified while extracting the .PDF files. Hence, adding date modified to this macro.
Sub get_pdf_name()
Dim FR As Long, sh As String, FPath As String, FName As String
sh = Sheets("Sheet2").Name
FR = Sheets(sh).Range("A" & Rows.Count).End(xlUp).Row + 1
[Code] .....
View 1 Replies
View Related
Jan 12, 2012
I wonder if is posible to discover following:
Who modified the cell from various users.
After that how many cells from xls fiile were modified by one user and how many by another user.
View 1 Replies
View Related
Jun 10, 2009
I want to know if there is a way to have a cell beside a series of cells that will change its date (and time) when any of the cells in the series is changed.
On a single row, I have four materials that total to a fifth cell. The 6th cell in that row, I would like to have a "date last modified" that would change if any of the first five cell's value is changed.
View 9 Replies
View Related
Feb 26, 2013
I have a workbook that column C has computer names starting from C4 to C83. I need to get the Datelastmodified of a log file that robocopy is writting to the following path. \Server1folder1WorkSheetName\%computername%somefile.log. The Datelastmodified needs to be entered in column K for each computer name in column C. One last thing would also be great is if the file does not exist it would write a error value.
View 1 Replies
View Related
Sep 21, 2009
if there's any sort of script which allows the file's last modified date to be shown in a worksheet?
View 5 Replies
View Related
Jun 11, 2013
I want to detect if a cell within a range of cell has been modified
A range of cell is named "ChartData" =Data_In_Out!$E$15:$E$29
I understand how to react for a single cell, but not with a group of cells
Code:
Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = Range("LastBarString").Address Then
Application.EnableEvents = False
...
...
end if
View 2 Replies
View Related
Jul 31, 2013
I receive a workbook each day with approx 500 tabs of call data and I would like to relabel all the tabs with the user names. The user names are in a merged range "A7:M7". I'm having difficulty getting the following script to rename the sheets. Currently the values in "A7:M7" look like "User: ADAM ENGEMANN-558".
I am trying to delete each occurrence of "User: " while naming the sheets as well as removing the merged range before doing so that the names are in A7 only. The sheet names should look like "ADAM ENGEMANN-558", or, ideally just "ADAM ENGEMANN". For some reason my code will not modify the cells. (See attached example)
This works if I manually unmerge the cells before running and remove the ":".
VB:
Sub RenameSheets2()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A7").Value <> "" Then
[Code] .....
This his how I modified it but it doesn't work on the supplied sheets. Please note that if I manually insert some blank sheets into my workbook and merge the cells etc it does work. There seems to be something odd going on with the sheets they are giving me. (they aren't protected)
VB:
Sub RenameSheets2()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A7").Value <> "" Then
I tried adding the following to remove the merged ranges and remove 'User: ' from the names
Range("A7:M7").Select
With Selection
.WrapText = False
.MergeCells = False
[Code] ......
CallSample2.xls
View 3 Replies
View Related
Jul 11, 2009
How can display the last modified date and time of my excel file in a particular cell - Say in cell E1.
This is the same value that we get when we Right Click excel file->Properties->General and look at the label "Modified".
View 8 Replies
View Related
Feb 14, 2012
What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.
E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"
-- Make an update:
A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"
I'm not quite a super user in Excel, but this function would be very very useful for me.
ps. I'm using Office 2010
View 4 Replies
View Related
Jul 10, 2012
I need to insert a date modified column to an Excel 2007 workbook I am currently using. The columns range is from A to L, with about a hundred data points. However, I would like to have the date modified cell update only when there are changes to rows F through L, starting with row 3.
I need the code to still function if I add rows, and it would be great if it would also still run if I added columns, but that part is not necessary. I've gotten close by defining a cell name as myCol and using this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
[Code].....
View 4 Replies
View Related
May 22, 2007
I am trying to subtract from a collumn 14 seconds from the time that is in the cell. I do not want to manually enter in a formula, or copy and forula into the cell as there is a large amount of data for this. The report will be updated daily and will need to always subtract 14 seconds from the time, and the result should be populated in the cells that the time is subtracting from.
View 6 Replies
View Related
Mar 11, 2014
I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
View 11 Replies
View Related
Apr 15, 2014
I have been trying for hours now to write a macro that would do the following:
I have a worksheet that looks like this:
15:10:06.3671B56221
15:10:07.1770B56221
15:10:09.3021B56221
15:10:10.1320B56221
15:10:11.3811B56221
15:10:12.1810B56221
15:10:14.6421B56221
15:10:14.6420B56221
15:10:16.2721B56221
15:10:17.5220B56221
with many more rows
I would like to subtract (for column A) line 1 from line 2, then line 2 from line 3....etc the data in column A represents time I would like to have the result in milliseconds and in a new column (D for example)
View 6 Replies
View Related
Mar 11, 2014
I'm trying to subtract the value of a textbox in a userform from a value in a cell, and display this new value in another textbox. An added complexity is this takes place in Visio but communicates to excel to find certain values. I have the following code:
[Code]......
which displays the value of a cell in excel, in a textbox in a visio userform. The excel cell value then changes and I thought that it would work if I were to duplicate this code and run a new sub changing the last line to:
[Code] ....
But I get a type mismatch error on the line.
View 6 Replies
View Related
Oct 26, 2008
I need a formula that will subtract a value entered in A3 from a valued entered in A2 to be displayed in A1.
Also...the Value displayed in A1 must be the value of A2 minus the running total in A3.
Ex...If I type '10' in A2 and type '3' in A3 the value shown in A1 must equal '7'. If I then type '2' in A3 the new value in A1 must equal '5' (this being the value in A2 of '3+2' minus from the original value in A2 of 10)
So..A2 = inputted value , A3 running total of inputed value, A1 differnce of A3 from A2.
Is this even possible? The whole idea is this....if you type a number into a calculator, and then subtract a number from the first number, the calculator then displays the new total. If you then subtract an number from that NEW total, the calculator then displays that NEW total. Thats what I'm trying to re-create in excel.
View 9 Replies
View Related
Jun 29, 2006
would like a very small macro for the following:
Units units remain day 1 day 2 day 3 ... day 13
MACRO
The units are the total number of units that can be bought ( in a game) and these are purchased on a daily basis.
I would like a column " units remain" as units are purchased to just count down auotomatically as numbers are entered into the daily row.
Thus:
11 xx day 1 day 2
Where xx would chage as numbers are entered into day 1-13 ( these will be either 1 or 2).
View 4 Replies
View Related
Feb 27, 2013
formula which uses 4 cells
the 4 cells are
Cell B2 - this is a set figure which is the initial base figure - currently set to 43
Cell C2 - This subtracts Cell B2 figure (which is 43) from Cell E34 [=SUM(B2-E34)]
Cell E34 - Is a cumulative total of 4 cells [=SUM(E4,E12,E19,E26)] and displays the total.
Cell D2 is a set figure of 49
The formula I am looking for is once C2 reaches zero, I need C2 to remain displaying zero and the remaining figure to be deducted from D2 and displayed in that cell so C2 figure would descend from 43 until it reaches zero. but would then start decreasing D2 whilst C2 would remain static at zero and carry that the remaining figure to D2 which would be deducted from the starting total of 49 and display the figure.
View 5 Replies
View Related
Mar 19, 2007
in Cell J32 I want it so when i type in a number it would subtract the number from Cell C32. Is there a formula in which all you have to do is type the number without editing the formula?
View 3 Replies
View Related
May 30, 2014
EX:
1200
1205
1210
1215
Cell with total should contain 15
These are mileage numbers. At the end I need to know the total miles driven as miles are added. I need to be able to see the total miles driven as mileage is added. At the end of the week, I need to see total miles driven.
View 7 Replies
View Related
Jun 27, 2014
I am setting up a workbook for our absence reporting for work and have everything figure out except one thing. I have it where it subtracts points if the points were given over a year ago and also have it to subtract one point for every 90 days of perfect attendance. The problem however is if someone gets 5 points for a no show on January 1, 2013 and then don't have another reportable until June 01, 2014, it subtracts the five points and then an additional 1 since they had over 90 days of perfect attendence and it also goes into negative when employees cannot have negative attendence points. Is there a way to keep the extra point from being deducted and keep it from going into negative? Below is my workbook and the formulas used.
Absence Reporting.xlsx
View 4 Replies
View Related
Mar 22, 2013
Is it possible to subtract a year from a cell that has 2012/2013 in it? For example, if A1 says 2012/2013, how can I get 2011/2012 to display in A2?
View 3 Replies
View Related
Dec 1, 2013
I have a row A through N. In cell A1, have a dollar amount. In cells C1 through N1, I have different dollar amounts. In cell O1, I want to have a formula that will take the dollar figure in A1 and subtract from that amount the total of all the amounts in cells C1 thorugh N1. But I don't know how to write that formula.
Let's say I've got $100 in A1, and then in C1 through N1, I have $2, $3, $4, etc. for a total amount of $94 in cells C1 through N1. I'd like to have a formula in O1 that would show $6.
How to write up that formula in cell O1.
View 1 Replies
View Related
Oct 2, 2006
I wish to have a column with say 100 in it as that will be the initial stock levels
I would then like the next column to be blank so that when you enter say 25 (the number of stock taken away) the third column will show the new number of 75.
Simple? well here is the tricky part
I just want those three columns so the blank one in the middle will remain blank after it has been used so for example
on monday we sell 25 pencils and type 25 in the field and the new total is 70
on tuesday we sell 40 pencils and type (in the same field the 25 was put in the day before) 40 and the total will show up as 35.
View 4 Replies
View Related
Sep 3, 2007
I am trying to create a formula that will compute a variance between the numeric value in a given cell with the value in one of 3 other cells depending on the condition of the 1st cell. Example: If the Value in cell F4 has a condition defined as "PMR" in cell C4, subtract the value in cell F19 from F4. If the condition is defined as "DBR" in cell C4, subtract the value in F20 from F4. And if the condition is "OTH", subtarct the value in F21 from F4.
View 4 Replies
View Related
Jul 13, 2009
I have is a cell with a begining balance and a cell witha current balance.
I also have a column in which each expenditure will be placed ina cell in that column in sequential order.
What i want to do is take the value in the first cell of the expenditure column and subtract it from the begining balance and the place the result in the current balance.
But after the first cell i will need to subtract each value from its previous value to continue counting down so to speak.
I am at a loss on how to move the last value which will also be the lowest value in the column to the cueent balance cell.
View 13 Replies
View Related
Dec 6, 2013
I have a problem with a spreadsheet that im putting togeather, and that is , i want to subtract a set value of 7.6 from cell I4 when a value of 1 is counted in cell F10. So if cell F10 counts 2 then another value of 7.6 is subtracted from cell I4. im using a countif function in cell F10 which is counting from a dropdown list which populates cells E22:E418.
I don't want any formula in cell I4 because this cell is required to enter values as required.
View 14 Replies
View Related
