I have a spreadsheet that has times in G column in military time. Some of the entries have "##:##" while others have "###" or "####" with no colons inserted.
I want to search through the g column and convert "###" to "#:##" and "####" to "##:##"
I am trying to loop through a list of numbers in column "U" Each time we encounter a 10 we insert a row before it and copy the text out of column "T" in the old row, and paste it into column "W" in the new row
Then resume and loop to the next 10 and repeat the action till we encounter blank cells. I can do the first loop and insert the new line ok but then I’m out of my depth.
I'm currently trying to insert the copy range of one worksheet to another. Let's say I have 3 worksheets, one main and two additional info sheet. I want to search column C in main worksheet for blank cell and use column F in the same row as the worksheet name which I want to copy from, then use column E from main worksheet as the keyword for the range I want to copy(range from column E to column Z). But when inserting, I want the keyword in main worksheet such as 'G' in the same row as blank cell to match the 'G' in copied worksheet. Because the 'G' in main worksheet is not in the same column for each row, I try to search the forum but can't find the inserting part. I also attach the example workbook.
View 3 Replies View RelatedIs there a way to not use colons when entering time formulas. I have a start time in a column and and end time in the next column. In the third column is the time difference between the two. I use a 24 hr military time to do this. Is there any way to enter the military time and not use the colon between the hour and minute? Example 1130 and not 11:30?
View 5 Replies View RelatedI need a macro that will copy a row to "n" number of identical rows below it, depending on user input. I am not skilled at VBA but I cobbled together some code I found online (see below). Unfortunately, it does not work properly. The input box pops up, but it only copies one new row regardless of what number you enter.
Sub InsertCopyRow2()
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
End Sub
I have a column named "Time" and when I enter the data in the first row (for instance 6:00), I would like time to be inserted in the rows below in 5 minute increments.
But as an addition, I would like an input box that requires the end time. For instance, if I enter 6:00 in the first row, I should have a input box requesting me to enter end time. If I enter let's say 8:00, then time (in 5 minute increments) should be entered until time is 8:00.
Is it possible to do this in Excel? If it is, how would I go about it?
I want to be able to type a 24 hour time into the column and have it format with colons - for example: I want to type 123456 and have it show up as 12:34:56
View 5 Replies View RelatedI need to work out a total of column A7:A39. Even with my lack of excel knowledge, I can do that with the sum formula. But, each week, I need to delete the values in A39 by deleting that row, and inserting a row into A7 and putting different data in.
I have my $ signs in place to make sure the formula relates to that column only, but when I insert the row at A7, the formula moves to A8:A39 not A7:A39. Basically I need to be able to delete and insert but still be able to view the sum of A7:A39 at all times.
how to format a cell, or what formula to use, so that when an entry is made the colon is automatically put in? so that 1011 shows as 10:11
How I can find instances of text strings containing two colon characters separated by any two (arbitrary) characters?
So in other words, "xxxx:xx:xxxx"
If I use :
Code:
SheetName.Cells.Find(What:=":**:", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
...I get a return of any strings containing two colon characters (regardless of the number of characters in-between) - because obviously the asterisk(s) denote any text whatsoever.
How do I specify a finite and exact number of characters between the colons when searching?
I need a formula that will search between dates and time to identify if the person will be present at the conference breakfasts, lunches and dinners.
Therefore the cell needs to look if the current date falls within the arrival and departure date and also if the time within eating times falls outside the arrival and departure times.
I have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
I have a sheet that contains the following columns:
Invoice, Document #, Date, PO #, Part #, Part Description, Quantity, Net Amount
Based on the quantity in the row I need to copy the row, and insert it n-1 times. So if the quantity is 5, I need to copy and insert the data below the original row 4 times for a total of 5 rows of data.
I plan on firing the macro with a button as the data will change month to month.
Using Windows 7 and Excel 2010
I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.
I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.
Here is my problem:
I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)
Example of data (Where "/" delienates column seperation):
Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain
6-1-06 12:15 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:30 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:45 am / 45 / 35 / .0001/ 95 / 5 / 7 / 3 / 0
6-2-06 6:00 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-2-06 6:15 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
So what I need to do is this:
1) Find which data times are missing
2) Add the appropriate amount of rows in between where the missing data would be
3) Add the correct dates to the new rows
4) Add "N/A" to the columns that have no data
You can see that my date and time are formatted in mm/dd/yy hh:mm
I found this on the website and was trying to modify it to my needs:
Sub InsRow()
Dim c
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
Attached is a sample of what I need completed.
Monthly, I have to do a chart just like this except slightly more complicated.
In the Sample download, there are three charts, "Sample Chart", "Sample Input", and "Desired Result".
"Sample Chart" has a list of accounts from different companies, The first column being their number, the second being their name, and the third being the money they spent.
The "Money Spent" Column is always blank when I start for ALL companies.
I have another chart, "Sample Input", which contains the prices that I'm supposed to put in "Sample Chart, Money Spent" column.
The thing is, "Sample Input" only has the companies with prices listed.. Not all companies have prices, so this means the "Sample Input" is always a shorter list than "Sample Chart".
What I need is the prices from "Sample Input" to be put in the correct position in "Sample Chart". The "Desired Result" chart is what I want it to look like.. exactly like that!
When I do this monthly, I have to scroll through several thousand accounts doing this.
Suggestions:
- Possibly have a macro or formula take the Account # in "Sample Input".. Sample it in "Sample Chart", then copy the price and paste it in the right location.
- Possibly make "Sample Input" have blank rows inserted in the places where it should have the account with no prices.
Sheet1 has 500+ rows and 30+ columns of data, sorted by text in column G (last name). I want to create a data input sheet for users to manually key in data. I need help to create a macro to cut and insert the row from the data input sheet into Sheet1 - the first row after it finds a match in column G. The text in G can only be one of ten different "last names".
It needs to be after the first event because of graphing from the data sheet.
I currently have a search and insert data userform which finds specific data from the textbox and displays the results in a listbox. Then I can select a specific result in the listbox and insert this data into a selected worksheet row. The problem is that the userforms code is out-of- date, i have changed to workbook to a point that the userform doesn't work properly. What i need is a new userform that can do the same thing but with the code simpler to change in case of new updates. Here is some information what is needed:
The data that userform gets information from is on a different Workbook called Database.xls, Worksheet called Data and the Range is A:E. The Data is Product Information which is in the Columns A to E:
A = Product Number (The format is 000-000-00)
B = Product Name (No format is Text)
C = BA (The Format is 00)
D = Price (The Format is £0.00)
E = PQTY (The Format is General)
The Textbox needs to be able to search two different Columns A and B and also their representing formats. For example able to search for the product name or number which formats are 000-000-00 or 00000000. The Listbox needs to show the Datas formats like 000-000-00 and £ 0.00. The Insert button needs to insert a selected row from the lisbox results and insert the Product Number from that row into a selected worksheet row cell which is in Column J. This is the existing code and example.xls if you want some more information on what i need:
Private Sub cmdInsert_Click()
Dim X As Long, ws As Worksheet, i As Long, ii As Integer
Set ws = ActiveSheet
ws.Unprotect ("seasons")
With ActiveSheet
X = ActiveCell.Row
End With
With Me.ListBox1
For i = 0 To .ListCount
If .Selected(i) Then
ws.Cells(X, 10) = .List(i, 0).............
I have data in cells B2:E2 and this can go down 100+ rows.
In column B i have invoice numbers but some cells contain the word "Deposit".
I have sorted this data so that the invoice numbers appear first and then all the Deposits.
I need a code to find the first instance of the word "Deposit" and to insert a row so that all the invoices and Deposits are seperated by a single row.
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.
View 1 Replies View RelatedWhat I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies View RelatedI have been able to make a excel sheet which takes inputs from userform for First Name, Last Name, Address etc. I have included a duplicate check for column "B" for "First Name". This checks if any existing data is already which matches the new data input for "First Name" through userform.
The userform only checks for the "First Name" check as required and gives a message that duplicate has been found. Then I have to close the userform and do a Control Key+F ( to find the new name for example, James) in excel sheet and validate that new name is same or different from existing name "James". This I want to do since this new name "James" may be another "James" as his "Last Name" is different. So even though First Name is same, since Last Name is different I know they are two different persons. In that case I will add the entry manually in sheet, instead of userform, since I would not be able to input the new data for "James" since the duplicate check with the current code will not allow me to do so.
What I am current trying is -
1. If the new name say "James" is entered through userform, then excel should point me to the existing row where the record for existing name "James" is there, say row 4.
2. Now without closing userform I should be able to see in the background excel sheet the search results for "James", as excel is pointing to that now. There may be multiple "James" in the existing which should be pointed out.
3. Based on the results that I see in the background excel sheet I can now decide that, this new name "James" is different from old "James" (of row 4) since his Last Name is different. Accordingly excel code should then ask me to add this record or discard this new record.
4. Duplicate check for First Name is enough for me. I would not require "Last Name" duplicate check.
I hope I have been able to explain my problem. I have also attached my current code as I am not able to attach any sample test file.
Code:
Private Sub cmdCancel_Click()
Unload Me
End Sub
[Code]....
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
View 1 Replies View Relatedis there a code to hide specific columns that i dont really need. i dont want to delete them, just hide them. even if i try to unhide them manualy from Format- Unhide they dont unhide. These columns stay hidden at all time.
View 2 Replies View RelatedI am trying to average times from one column that is the difference of two other columns (generally just minutes). It is formatted in a military time format.
However, I do have criteria it must meet from another column.
Ex:
Col A Col B Col C Col D
1 23:57 00:07 00:10 "=C2-B2+IF(B2>C2,1) fx I am using"
2 03:15 03:20 00:05
1 10:30 10:42 00:12
That's the basics, now I want to average Col D but only if it is a "1" in Col A. There are some empty cells, being its formatted for 100 entries. I can get an average of Col D and a count of Col A, but I can't combine them.
I have several columns of times in the HH:MM.SS format. I need a sum at the bottom of each column.
If I put a Sum or a Cell+Cell formula in, it gives me the #value error. I've tried a few different things but can't get it to work.
Could someone be so kind as to tell me the formula or point me to the instructions for this?
(The data reflects times spent on certain tasks for each day, and I need a sum at the end of each column giving me a total amount of time spent on that task for the month.)
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I'm trying to create an excel worksheet as follows:
Column1 Column2 Column3 Column4 Column5
Date Time In Time Out Hours Worked Pay
01/03/2000
01/04/2000
01/05/2000
.
.
11/11/2008
I want to insert in the first column (Column1) dates starting from 01/01/2000 all the way up to today, 11/11/2008. So I would end up with a unique date on each row.
To accomplish this manually would be a daunting task. I'm a newbie to excel and don't know how to use any in-built functions.
I have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50
Balloons 12567 Blue Princess balloon .86
Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27
Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
etc..
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.