Note Last Time In String

Jan 12, 2009

What I am looking for is whether or not there havebeen at least three, uninterrupted true cells in that particular column. The time is based off of the last time in that string that the cell returns a true statement. For example: O-Data returns 12:46:00 PM because there aren't any true statements in any of the other three columns between 12:43:00 PM and 12:46:00 PM and there are three true statements in that column.

M-Data does not return a time at 12:42:00 PM, because at 12:39:00 PM there is a true statement returned in the L-Data column (This is what I mean by interrupting a string).

View 14 Replies


ADVERTISEMENT

Speed Improvement - Macro Extract Higher/lower Values And Note Time Differences

Jul 8, 2009

I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.

The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.

I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:

1) The user inputs the starting cell. In the Sample I use G200.

2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.

If the value is greater than original cell then there are 2 possible outcomes:

a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.

Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.

At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.

OR
Here's the logic now in reverse I need:

If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:

a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.

Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.

Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.

Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.

There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.

I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.

Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.

The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.

Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.

EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.

View 14 Replies View Related

Adding A Note To A Formula

May 14, 2009

My formula is : =SUM(C8:N8). How do I add a "note" as opposed to a comment to this formula?

View 3 Replies View Related

Convert Note Pad To (2003)

Jun 6, 2009

i have note pad file. for your kind information i attched the note pad file i like to convert this note pad file to excel. i want each coloums and rows separate separate same like note pad in xls.

View 5 Replies View Related

Note In Another Workbook Cell

Nov 20, 2009

I am trying to run this macro and I get always an "Error 1004" with the select instruction. The aim is to put a note on the same cell in the same sheet in a second (NewFile) workbook and highlight it.

Here is the
Sub FetchNoteinNewFileActivecell()
'
' This macro highlights in red the cell in the NewFile corresponding to the selected cell and adds an empty comment
' Warning: this works only if there is a sheet in the target (NewFile defined in range in Sheet1) file with the same name as the active sheet
'
Dim NwFile As String
Dim CurrenCell As String
Dim CurrenSheet As String

View 9 Replies View Related

Add Cell Note Based On Criteria

May 28, 2013

I have a range of cells containing text values in BS15:DS50000.

What I want to do is look at each cell in the range BS15:DS50000 and if the cell contains the "" character, then populate text from column BH (if any) as a cell note.

For example, if cell DB42 contains the "" character somewhere within its text, then populate the text in BH42 as a cell note within DB42.

If BH42 is blank, then no cell note. (Also, if DB42 does NOT contain a "", then no cell note either.)

View 2 Replies View Related

Make All Cells That Have Note In Them Yellow?

Jun 3, 2013

I need to make all cells that have a 'Note' in them 'yellow'.

this will be for the entire workbook.

View 9 Replies View Related

Compare Sheets In 2 Different Workbooks And Note Changes

May 21, 2006

I've attached two separate workbooks that have indentical sheets in each. One workbook is a 2 day later update of the first. I'm trying to develop VBA code that will open both workbooks uses column B (work order #) as the unique identifier for each like sheet (which represent a month) and compares all other columns for that unique indentifier. Then I need to generate 3rd workbook that is identical in format (has a sheet for each month) and content to the two source ones but shows the unique identifier and any changes:

Column A (due date)
Column d (order quantity)

Also,if in the newer workbook(5_12_06) there exist a unique indentifier (work order #) that was not in the older workbook(5_10_06) and the same if there exits a unique indentifier in the older workbook that was removed in the newer workbook.

View 2 Replies View Related

Edit VBA Code For Lotus Note To Display Email Instead Of Sending Out?

May 28, 2013

below is the code i use to create a lotus note email.

i would like it to display my email and send it manually instead of sending out automatically.

' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName

[Code].....

View 4 Replies View Related

Time To String

Mar 17, 2007

I am converting minutes to [h]:mm (thank you DaveR (UK) for this part)

The formula bar shows:
1/1/1900 11:51:00

The cell contains:
35:51

Is there a way to convert the 35:51 to a string and not time?
(changing the cell format to general after the 35:51 is placed gives "1.49375" which is the time in its raw format)

Sub test_time_2_String()

Dim a As Variant
Dim b As Integer
Dim c As Variant
Dim d As String, e As String, F As String

Cells(10, 21).Activate ' Arbitrary cell

' Break it down to its smallest parts in order to watch the progression

I Dimmed d, e, & F as strings yet it still formats the cell as time as soon as it's pasted.

The ActiveCell.NumberFormat = "[h]:mm" line is there so a least I have 35:51 instead of 35:51:00. Neither General or Text formats worked.

View 4 Replies View Related

String To Time In Formula

Oct 2, 2007

I have a formula in VB code that is like this

sheet1.cells(irow,asktimecol)=asktime

when i place the cursor over each element i get :

irow="9"
asktimecol="5"
asktime="13:30:00"

so it reads

sheet1.cells("9","5")="13:30:00"

but iget the the error
application defined or object defined error
this is saying it cant place the asktime to cell
I have to bring the time thru as string as it come thru as one

View 9 Replies View Related

Extract Date And Time From A String

Feb 19, 2014

I have this data & would like to extract the date in dd/mm/yy and the time stamp separately to use in some calcs.

Jan 10, 2014 4:47 AM
Jan 10, 2014 4:47 AM
Jan 14, 2014 4:30 PM
Jan 15, 2014 4:00 PM
Jan 16, 2014 5:01 PM

View 10 Replies View Related

Formatting Number String To Time?

Feb 20, 2014

I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?

View 8 Replies View Related

Time As A String And Converting To Seconds

Jan 25, 2008

I have an ACD program that gives me a time in "mm:ss" format, or in ":ss" fomat. I have 2-3 problems with charting this data.

1 I am inheriting the production of charts from someone else, with old data being displayed by seconds only I am supposed to keep this display.

2 the data that is in mm:ss format is recognized as being in full date format, for example 2:14 is recognized as 2:14:00 AM instead of 2 minutes, 14 seconds.

3 the data that is in :ss, ie :44, is recognized as a string.

Any thoughts on how I can convert both cases to a integer seconds? I know for example that I could mid() the second condition, but I am stuck thinking about the first condition.

I can't adjust the way the time comes in, unfortunately. It is an auto-generated export of the data from a switch.

View 9 Replies View Related

Removing Seconds In Date / Time String?

Sep 26, 2013

I need a macro, if possible, to remove the seconds in a date/time string.

I can format the cells to remove the seconds, but the actual value still contains the seconds. For example:

The cell shows 9/24/2013 19:21 but the formula bar shows 9/24/2013 7:21:17 PM.

I need to remove the seconds so I can us the time in an averageifs formula as a criteria.

I would like a macro because I want to do other manipulations of the spreadsheet with the idea of importing the data at some point into a database.

If I can automate it, or at least decrease tha amount of time I use to get the data in the correct format.

View 4 Replies View Related

Count Number Of Time A Text String Appears

May 25, 2007

I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.

Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith

So if I search on *Dan*, the function should return a count of 2.

I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.

View 14 Replies View Related

Run Time Error 1004 (find The Cell That Contains The String )

Dec 14, 2008

I'm trying to find the Cell that contains the string "Grand Total" along row 5.
The following code is giving me Error 1004

Dim column As Long
column = 1
Do Until ActiveWorkbook.ActiveSheet.Cells(5, column) = "Grand Total"
column = column + 1
Loop

The "Do Until ActiveWorkbook.Activ...." line is highlighted in Debugger as the problem line.

View 2 Replies View Related

Join Date And Time String And Keep Cell Appearance Same

Aug 23, 2007

I am joining two cells together, date and time.
I1 & J1
12/13/2005 12:35:00 PM

I want it to appear as
12/13/2005 12:35:00 PM

but after joining i get the serial value of:
38699 0.524305555554747

View 9 Replies View Related

Code That Counts Number Of Time String Occurs In Column?

Jul 5, 2014

I need a code that search through a column, counts the numer of times a string occurs and creates on another sheet the unique string and the number of times it occured.

e.g

I have this columns in a worksheet

s/n name company trainer
1 ball Dell Mk
2 doll Msed Kl
3 kol Dlink Mk
4 ball Msed Cl
5 Koll Dell Gl

I need the result to appear on another worksheet like this

Name NumberofTraining
Ball 2
doll 1
Koll 2

View 9 Replies View Related

Comparing A String To Values In An Array Resulting In A Run Time Error 1004

Jul 15, 2009

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.

View 3 Replies View Related

Spreadsheet To Note Book And Back To Spreadsheet

Apr 14, 2007

It also renames the CommanBarPopop with the new filename.This allows the user to open both Projectworkbooks/files (If required) and load each CommandBarPopup for different filenames .Therefor opening the Userforms and worksheets for the CommandBarPopup clicked ...

View 9 Replies View Related

Convert And Format Date Time String To True Date Value

Oct 19, 2012

I have a spread sheet with a date colume that reads: 2012-06-27-19 I need to have this read like 06/27/2012 but nothing I do is working I have tried to go to the formatting process and backing the hr:mm out and that doesn't work. I really don't want to go line by line to manually correct this issue.. HENCE ... over 2000 lines

Second question: If I have a column that reads 02/15/2012 and another column that reads 3/27/2012 how to a format a 3rd column to make it read total number of days between 1st date and 2nd date?

View 1 Replies View Related

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View 3 Replies View Related

Display End Time Automatically Upon Entering Start Time And Time Usage

Dec 19, 2008

I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?

View 2 Replies View Related

Excel - UDF That Returns String Of Multiple String Objects / Possible To Color Font?

Sep 19, 2012

I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.

Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):

VB:

Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function

Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3

View 2 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?

Example;

Cell A1 has 123XVF1234
Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value

Example;

If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

Conditional String: Repeat The Same Account Number In The String

Jan 10, 2009

I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?

View 4 Replies View Related

Replacing Misspelled Sub-string In Varying Full String

Aug 27, 2009

I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:

Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2

I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.

Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...

Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".

View 14 Replies View Related

VBA To Search String And Insert Row If String Not Found

Apr 11, 2013

I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.

I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved