Comparing Time Value Generated By Two Different Methods
Feb 28, 2014
I have thousand rows of data in the following format:
Despatched Time
Batch
Date
Time
Amended Time
20/4/2013 3:45:00
1
20/4/2013
03:45
[Code] ..........
'Date' is generated by =IF(A2="", "", TRUNC(A2))
'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))
And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.
I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula:
=IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))
The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example: 1st average output at z3, 1st std dev output at z4 data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6 data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8 data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
I'm trying to write a macro that compares 2 coloms(open,close) of dates/times and calculates how long each was open for. This is what I have so far. I've also include as an attachment an example of the date/time format i'm using.
Sub Time() Dim A, B, C As Long
Sheets("Sheet1").Select
For i = 7 To 5000 A = Fix( Range("A" & i).Value) B = Fix(Range("B" & i).Value) C = A - B Cells(C, i).Value = C
Assume there's an even number of rows containing textual data.
COL A text1 text2 text3 text4
I would like to compare the lengths of text1 to text2, and return the row containing the greater of the two/delete the row containing the shorter of the two. In either case, the contents below would then have to be shifted up by one. Then repeat for the next 2 rows all the way down to the last row using VBA.
This workbook is designed to prompt the user with inputboxes allowing them to enter a name, hyperlink a file to that name, and then a date. Next all the drawing names are read into an array and stored. Along with some minor sheet formatting, this is what happens when the button "Add Drawing" is clicked.
Next, when the button "Revise Drawing" is clicked, the user will be prompted with an inputbox for a drawing name. This name will be compared to the names in the array. If the name is found, the row containing that drawing will selected and the user will be prompted to update the hyperlink, then the "drawing date" will be moved to the right by one column, then the user will be prompted for another date replacing the date that was moved. The first sequence of commands or button works fine. The button "Revise Drawing" prompts the user with the inputbox requesting the revised drawing name, then develops the runtime error 1004.
I am developing a VBA for Excel application which uses transient worksheets to collect data from users before writing the data back to master spreadsheets.
My application opens an instance of excel with a workbook and worksheet using the following code:
Dim xlsInstance As Object Dim wkb As Workbook Dim wks As Worksheet
Set xlsInstance = CreateObject("Excel.Application") Set wkb = xlsInstance.Workbooks.Add wkb.Activate Set wks = wbk.Worksheets.Add wks.Activate
I can reference various ranges in the worksheet and do most of what I need but I need to override the Worksheet_Change method. Easily done for static sheets, but this must be set at runtime when the worksheet is created.
Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.
Code: Sub DBPreferredWay() folder = ThisWorkbook.Path & "mydatabase.mdb" Set db = OpenDatabase(folder) strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus Set tR = db.OpenRecordset(strSQL)
[Code] ........
I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb
Code: Sub SqltoACCPAC() Set conn = CreateObject("adodb.connection") Set rs = CreateObject("adodb.recordset") phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password" strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]
Instead of typing out my code hundreds of times, I would like to replace it with an abbreviation or variable(?). Here is my example. If I have this in the wrong place please let me know. I want to replace Activecell.Interior.Colorindex with ACIC. I tried to Dim ACIC as range, variant, string...to no avail. In combination I Set ACIC = Activecell.Interior.Colorindex and that didn't work. I have been toiling with VBA for a year and I am still trying to learn the terminology and understand it so please forgive me if I seem really NOOBIE. :-)
I am trying to compare two workbooks and am running into the problem that since I declared my two workbooks, I am limited in the methods that I can use. Here is all of the code so far.
Option Explicit Sub UpdateMasterFile() Dim wbMaster As Workbooks Dim wbEmailed As Workbooks Dim wsPC As Worksheet Dim Master As Long Dim Emailed As Long Dim intMaster As Integer Dim intEmailed As Integer Set wbMaster = Workbooks("Master Info.xls").Sheets("PlantsCom") Set wbEmailed = Workbooks("EmailedData.xls").Sheets("NewInfo") Master = Workbooks("Master Info.xls").Sheets("PlantsCom"). Range("a65536").End(xlUp).Row Emailed = Workbooks("EmailedData.xls").Sheets("NewInfo").Range("a65536").End(xlUp).Row..............
I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.
I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.
Do While i < num_checkboxes eval("Checkbox" .i. ".Value=1) Loop
I need to learn the syntax and all options I can use with the Selection function - where can I find that info? I can't find anything in in Visual Basic Help (I find microsoft help is generally totally useless).
For example, in the code below, what are all the options I can use after "Selection. " (ie .Copy, .End, .Select) and what do I use if I want to select a certain number of cells to the left or to the right or up or down?
Sub Fill() Selection.Copy Destination:=Range(Selection, Selection.End(xlDown).Offset(-1)) Selection.End(xlDown).Select End Sub
I'm just noticing now that the "(xlDown)" is X L, not X 1, which I thought might make sense to say "1 time Down". What does the "xl" stand for?
I am doing a spreadsheet where I have 1 main sheet and 2 sheets with data.On the main sheet I have a drop down list to select either one of the 2 sheets and upon selecting,all the data from that selected sheet will be displayed on the main sheet.
I’m creating a spreadsheet to control corrective actions. Each corrective action will have a date that it needs to be completed by. When the completion date plus one day is passed, I need the cell that the completion date is in, to turn a specific colour and a warning mail to be sent via outlook When a further six days have passed I need the cell to turn a different colour again and another email is sent
Ive generated the following code to call an Outlook subroutine if the correct conditions are met. The subroutine runs ok but I can get the sheet code to run.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1"), Target) Is Nothing Then If IsDate(Target.Value) And Now() = Target.Value + 1 Then Target.Interior.ColorIndex = 3 Call Mail_with_outlook ElseIf IsDate(Target.Value) And Now() = Target.Value + 6 Then Target.Interior.ColorIndex = 5 Call Mail_with_outlook
I'm trying to sort some data that is generated from a PDF. When I copy the PDF into Excel all of the data is placed into one cell. Is there a function or formula I can use in VBA or within the spreadsheet to accomplish this?
For example, the cell may contain "Employee John Employee Steve Employee Dave Employee Jed Employee Mark" in cell A1. I want the function to list John, Steve, Dave, Jed and Mark in separate cells. So I need to get rid of the space bar hits, the word employee and to separate the names to different cells.
A1 currently is "Employee John Employee Steve Employee Dave Employee Jed Employee Mark"
I wish to create a new sheet/workbook which promts the user to enter a start number and an end number. I want to then display 20% of randomly picked numbers between the start and end figures.
For example, the user enters 1 as a start and 20 as an end figure. 20% of this is 4, hence I want the system to randomly return 4 numbers between 1 and 20.
I have the following code that will look through an individual worksheet, if the value in column D is equal to 5 and the value in cells E6:L1000 is "a" then it should generate an email. The problem I am having is that it generates 8 emails, one for each column. What did I do wrong in the code? It should only generate one email for the cell which has the "a"
Sub Test() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim cell As Range Dim wsWH As Worksheet Dim lngMax As Long Dim intFiveDays As Integer Application. ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error Goto cleanup Set wsWH = Worksheets("WH") lngMax = wsWH.Range("A65536").End(xlUp).Row For intFiveDays = 6 To lngMax...................
We are having a sporadic issue throughout our company where when users attempt to open an ASP generated spreadsheet in Internet Explorer and then hit CTRL+A to copy all contents and attempt to paste into a regular Excel-launched spreadsheet (i.e. launching Excel independent of IE), the last two columns are not being copied. Further, when attempting to just copy the two columns, themselves, only the first is copied, and when attempting to copy one cell each in the two columns (that are in the same row), only the first cell is copied. Additionally, when examining the clipboard in this instance, only the first cell is being copied to the clipboard, so for some reason attempting to copy it out of the Excel spreadsheet that opens in IE is when the issue occurs (vs. having the issue occur when you are trying to copy into the new Excel spreadsheet from the clipboard).
I am have some code that takes values from a range (sourceAll) defined on my worksheet as:
Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", "S34")
I'd now like to be able for the range to be generated dynamicaly, but my problem is that the far right column (S) does not always have a value for every entry. Is there a way of using a column that does have a value for every entry, say column B, but then extending the range accross to S? If possible I'd like to avaoid having to move one of the columns with values for every entry. Auto Merged Post;I was looking at some of the suggested threads and found some code that I worked into my own
Set countRow = Workbooks(pathnm).Worksheets(m).Range("B1250").End(xlUp) Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", countRow) sourceAll = sourceAll.Resize(sourceAll.Rows.Count, sourceAll.Columns.Count + 17)
now this seems to work mostly, but it has deleted everything that was in column B.
I have a file here that already has macros in it. The file is basically a excel document generator. When you click create sku, the document will generate multiple documents based on the user inputs.
1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)
2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)
I was unable to attach my file to this thread because it was to big. however I copied the module where I think the code is causing problems:
Sub procData() ' Starting point for read/extract process Cells(4, 6).Select ' Range = F4 (row 4, col 6) For x = 6 To 52 Step 2 ' col F to col AZ
Prior to Excel 2007, the functions provided by the Analysis Tool Pack (ATP) are provided by a separate add-in. You cannot call them using the methods shown above. First, you must load the Analysis Tool Pack - VBA add-in. Note that this is different from the Analysis Tool Pack item. Once this add-in is loaded, go to VBA, open your project, and choose References from the Tools menu. In that dialog, choose atpvbaen.xls in the list of references. Once you have that reference in place in your VBA code, you can call the functions in the ATP as if they were native VBA functions.
I have written the VBA code to color any figure above 15, but it display rows above 15 as red including other rows.
Below is the VBA code
Code: Sub FillCells()Dim lRow As Long, lColumn As Long 'Using Cells property to refer to range 'Loop through rows For lRow = 1 To 10 'Loop through columns For lColumn = 1 To 5
[code].....
Which shows the output as Workbook1.jpg But my query is how to get only rows above 15 with red color
how to use the random number generator to generate unique numbers (1-55) based on the odd percentages in the attached chart.
for example in the chart, chances 1 through 10 are more likely to be generate first rather then the last chance number 55 based on the draft pick odds....
Suppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go. So far so good.
And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.
Is there a way to control the number generation. I would like to be able to run different scenarios with the same set of random numbers, then regenerate the random numbers and do it again. Right now every time I do anything in the spreadsheet, I get a new set of numbers.
