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 RepliesI have a table that I am storing into an array. I have an array FDate where I store a date and another array FTime where I store times.
FDate is formatted as m/dd/yyyy Hh:Mm: Ss
Ftime is formatted as Hh:Mm:Ss
I know in excel that you can add and subtract dates and times. How do you accomplish the same task in VBA?
How do you subtract start/end - dates/times in vba?
I know how to do it with formulas, but can't grasp it with vba. My range will always change, so I'm trying to avoid formulas (Cutting rows etc...)
I need Col D-Col B (end date-start date) and Col E - Col C (end time - start time)
Would doing the calculation on the userform be the best way, or trying to do the calculation with the ws code?
In the same period of time I have two countings of different stopped time.
sheetA
range (A3:A23), = product code.
range (H3:H23), = time used.
with this formula I take the time used for each product. "= SUMIF($A$3:$A$23,M2,$H$3:$H$23)"
sheetB
On this one I record all different lost times in the same period of time.
What I want is:
Take out from the result obtained with the above formula, the time down from SheetB.
Ex:if I used on sheetA between 7:00 and 18:00, 4:10 of the product A, I want to take to this value the downtime in the same period from sheetB.
I'm trying to subtract two times to get the difference. Entries are in military format (1615) and VBA converts the time to 16:15. My problem is that when a time is entered between 0001 and 0059 (i.e. 0015), the VBA converts the time to :15. My formula does not recognize this, but does recognize 0:15 (entered without VBA). The cell is custom formatted as h:mm. I've tried several other formats including [h]:mm but can't get any to work. How can I get a formula to recognize it?
=IF(U12<T12,(U12+1)-T12,U12-T12)
where U12 is :15 and T12 is 23:00
I get a spreadsheet downloaded to excel with a variable of 15 names.
I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500.
I have created a userdefined function. the Idea is for the user to calculate the difference in hours and minutes. The input would be like budtime = 30 , realtime = 1.25 the answer I would like to have is 28:45 (28hours and 45 mins). Im aware that I can format the cell manually with [h]:mm but I'd like the function to do it.
I have written this:
Function RT(BudTime, RealTime)
RT = Format(((CDec(BudTime) - CDec(RealTime) / 24)), "[h]:mm")
End Function
I've taken data from a fixed-length text file and the records are identified using the first two characters on each line of the text file. I've managed to identify these in each row with other bits of code so that was fine.
Now, I have 56 string variables (identifying my record types), which I'm assigning into an array. Then, I want to go through each variable in the array, performing the same action by way of a For Next Loop where, by taking this particular RecIDNo (see my code) string variable I will get the code to copy/paste the data into the relevant worksheet (where later on I will apply a text to columns using another array). But first, this set must work before I can do that! To avoid confusion, note that the record types i.e. 01 through to 56, will be filtered, then copied into the corresponding worksheet with the same number.
The problem I'm getting is the autofilter I'm applying, then the array I'm trying to reference (to avoid repetitive coding!) doesn't work as the code doesn't compile (I've not worked a lot with Arrays but I'm learning all the time!)
Option Base 1
Sub test()
'The array is declared here:
Dim RecIDNo(56) As String
'The arrays are assigned the string variables below:
RecIDNo(1) = "01"
....
RecIDNo(56) = "56"
'This worksheet contains my data:
Sheets("DATA").Select
For RecIDNo(1 To 56) '<--I THINK THIS IS WHERE I'M GOING WRONG!!
'Filter and select arrays:................
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
column A4-A9 equals the sum on A10. What I am trying to do is put a formula in H10 that will start out at 35 from no addition, but if cells A10-G10 go over 22 I want it to subtract from H10.
View 5 Replies View RelatedI have a column which contains following values:
Code:
646-900
634-645
611-633
589-610
and so on
I want to first reverse the range in each row and then subtract the values from 2000 so that it now becomes
Code:
1100-1354
1355-1366
1367-1389
1390-1411
How can I write the formula?
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?
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.
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?
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies View RelatedI have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.
However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.
I have a new project which I need some of the wonderful expert help with here at the MrExcel.com forum.
I have two columns of data, ITEM#, and LINE#, where ITEM# repeats multiple times in the report, and LINE# shows individually each line where the ITEM# appears in the source:
ITEM# LINE#
------- --------
12120 0001
12120 0002
12120 0003
12120 0009
12120 0020
12120 0021
55555 0004
55555 0005
99999 0006.........
I work at a call center, and I need to find the average duration of time our employees spend on the phone. Here are some of the call times:
0:00:00
0:00:01
0:00:02
0:00:03
0:00:04
0:00:05.......
How would I find the average call time of these calls? When I use the formula =AVERAGE(Num1:Num2) I receive a # DIV/0 error.
I have a range A3:E70 where A3:E69 have the same formatting and the text in A70:E70 is bold. I want to copy-paste the entire range a number of times with a for ... next-loop. Actually, it's kind of a template system. I make the template A3:E70 and uses it a number of times.
View 2 Replies View RelatedWant to enter 6 digits for each cell in a range of cells with VBA mask change to time format hh:mm:ss for each cell when entered. Code below does this for hours and minutes but I haven't been successful in modifying to work including seconds.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
I am trying to get rid of some duff data by running a comparison to a fixed value on a defined range. However, the macro tries to compare the cell value (00:05:00) as a decimal value.
I think I need to use the format function to get round this.
For Each timecheckcell In range("g3:g60")
timecheckcell.Value = Format(MyTime, "h:m:s")
If timecheckcell.Value <> "00:05:00" Then timecheckcell.Offset(0, 1) = ""
Next
I am trying to capture data for statistical analysis, but have hit a snag. As shown in attached sheet I have each day for the year broken down into hour time slots into which data will be added. As I will need to come up with a number of similar sheets in the future I was wondering if there is a way to automate through vba, a quick way to copy down the hour slots for each day, 365 times, rather than the laborious task of copy and paste manually.
View 5 Replies View RelatedI have the following data in one "Project_list" tab:
Project
AAAA
BBBB
CCCC
DDDD
I want to copy this data into another tab "Transpose" in this form:
AAAA
AAAA
AAAA
AAAA
BBBB
BBBB
BBBB
BBBB
[code].....
So basically copying 4 times every row... as simple as that The constraint: I have about 1500+ projects in the project list, but this list can change so i need a statement that copy values 4 times till source tab has empty values. I would like to have in the second column the following serie for each project.
AAAA Q12014
AAAA Q22014
AAAA Q32014
AAAA Q42014
Note- i want to do this in VB not though formulas as i am doing other derivations in macro.
I have a range of cells that is 10 rows high. I want to copy and paste this range 11 times while identifying each of the 11 copies of the range, such as; 02,03,04...12.
Please see attachment for an example of what I am trying to do.
Does anyone have an idea of how this might be done in VB?
Is it possible to have the user inputing a value in cell $G$8 and have the range B11 to G11 to be copied x time depending the value of Cell $G$8 down. ie: Value is 5, it will be copied on 5 rows under B11 to G11??
View 3 Replies View RelatedI have a Macro that I wanna run 53 times. Is this gonna be possible without running it manually that many number of times.
I have a list of names in C1 down that I want to count. There could be hundreds of names in C1 and I don't want to sumif all of them. There must be a simpler solution
View 1 Replies View RelatedI have various start times for employees.
I need to find out the total movement in start times per week for the employees.
I have used MIN and MAX to find the earliest and latest start times, and then a subtraction to find the difference.
This works brilliantly until my employees who start around midnight.
If they have started before and after midnight, it takes midnight as the earliest time and 23:00 as the latest, giving a difference of 23 hours instead of 1 hour.
i.e.
Start Times
23:00, 23:15, 00:15, 00:30
Range of Movement
00:30 - 23:00 = 01:30
I've tried, =$B$9+($C$4>$B$9)-$C$4 but this does not work when the MIN reads 00:00.
I have a rand () which provides me a different number upon calculate. I would like to run a loop for 1000 times and take the value and copy and paste in a range.
The below sorta works, but at times it stops or doesn't work at all. I have a loop for the counter and the for each to place the value into the cell and have it run over and over and placing the value in the next cell underneath. I have the counter at 10 so I dont get stuck in some endless loop before I know it works.
Code:
Sub montecarlos()
Dim MCs As Long
Dim c As Range
Dim lCount As Long
Dim lNum As Long
lCount = 0
lNum = 1
Worksheets("MonteCarlo").Activate
[code].....
Id like to run a Sub Macro a certain number of times, dependant on the amount of data provided. It could be that I need to run it say 290 times today, but 500 tomorrow.
Is there a bit of code to say, run the macro '500' times?
At the moment, if I set it to run more than it would need to it debugs and gives a compile error, procedure to large.g