I'm a novice in VB and can't work out how to solve the following problem:
I have imported NMEA-data in text format from a GPS into Excel. This data is acquired in real-time at 10Hz, which borders what the GPS in capable of calculating. As a result the data isn't quite reliable enough - there are strings missing and some lines have been skipped by the GPS. This is a typical example of what sometimes happens: ...
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................
I'm looking for a formula that would arrange the numbers from column K into the sum table, according to their sum total from column L, the formula should begging in cell P3 to maybe T3 and down to T30 I suppose ?
KLMNOPQRSTU1NumberSum Sum Table 2
30235 0 40246 1 50279 2 602810 3 702911 4 80347 5 023 903710 6 024 1003811 7 034
1103912 8 1204711 9 027234 1304812 10 028037 1404913 11 029038047 1507815 12 039048237 1607916 13
049238247 1708917 14 239248347 182349 15 078249348 1923712 16 079349 2023813 17 089278 2123914 18 279
2224713 19 289478 2324814 20 479 2424915 21 2527817 22 2627918 23 2728919 24 789 2834714 25
2934815 26 3034916 27 3147819 3247920 3378924 34
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
DAILY TRAIN RUNNING.xlsx
Looking for assistance in inserting 24 blank rows in between a column of names. And if not too much trouble have the ability to enter the number of blank rows to insert.
Before:
Column A
name1
name2
name3
etc.
After:
name1
blank row
blank row
etc.
name2
blank row
blank row
etc.
name3
blank row
blank row
etc.
Below is just a part of my very long list of statement that I posted into column A, B and C of excel worksheet:
10
67.12
03-Oct-12
10
85.16
02-Oct-12
[Code] ........
Any formula or macro that can automatically change to below expectation such as inserting 3-blank rows, totaling and sorting the date.
10
85.16
2-Oct-12
10
67.12
3-Oct-12
[Code] .........
I have a workfile, containing dates in column C and values in column G.
The data is imported. In most instances there are no dates in line with the second value. I need VBA code that will insert a the same date in line with the first value below the cell where there is a date....
I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins
name Acc # Balance Date
John 2222 200 june 2013
John 2222 344 june 2013
John 2222 700 Sept 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
I've got a spreadsheet that I prepare for a group that has patient data subtotalled by physicians. The problem is that the group uses this data for data entry and wants a blank row between each different patient name within the physicians subtotals and I have no clue how to get there. Any chance there might be some VBA code that could accomplish this?
View 4 Replies View RelatedI need to alter this code in order to do 2 things
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
firstly i am cross-posting this topic so here is the URL to the same thread in a different forum.
[url]
whats up everybody? i have to use two excel worksheets that someone else already created to make CSV (comma seperated value) files. these CSV files are to be dumped into a database. the problem is that the table has more fields than the spreadsheet does (the spreadsheet has 3 fields - network, mask, size - and the table has 10 fields). i can insert one entire column before (to the left of) network (for the ID field in the table) without a problem, but when i try to insert entire blank columns after size (to the right of the pre-existing columns) it only makes columns for the first 14 rows. i know this because when i save it as a CSV file there are only 6 extra commas for the first 14 rows. is there a way to insert an entire blank column for the entire spreadsheet (as in all the way to the bottom)? i dont want to have to manually type in all those commas. i am using excel 2002.
I am trying to insert a blank row into a table (created using the table function in Excel 2010) without the copying the formulas. Every time I insert a row the the formula is copied.
View 3 Replies View RelatedI have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:
Code:
A B C D
1 5 3 4
2 7 6 3
3 9 1 3
the end result should be:
Code:
A B C D
1 5 3 4
2 7 6 3
3 9 1 3
Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?
I'm Working on a sign-in sheet for the employees at my office...
I'm looking for a way to insert the current time as a value without having to copy and paste special (values).
in a certain macro, I generate graphs in different windows. At the end of the macro, I want to activate these windows one after the other putting a time delay in between. How can I do this? (I have found a "delay property" in the VBA help, but it only applies to forms...).
View 4 Replies View RelatedI have a spreadsheet where an engineer is expected to record sample temperatures of water outlets, along with the time he took the sample. Each outlet has a row on the spreadsheet with a column for the Temperature and column for the time. I would like to automatically input the current time(or time and date) on each line as the temperature is entered.
View 4 Replies View RelatedMacro that would insert multiple pictures at once into one column with two rows per page (so that only two pictures are on each page) for word?
If so, would there also be a way to make it to were photos would have a caption box underneath each photo that list them as "Photo #1: Photo #2: Photo#3:" ect...
I know this is quite a task to achieve but I'm still relatively new to Macros and VBA and have a ton of pictures to integrate into Word for work.
I make our employee schedule at work, and so in Excel I have eight sheets for one workbook. (Employee schedule, then each day of the week) On each day is a table separating the shifts. Morning, Split and Night.
I was wondering if there was a way for Excel to look at the schedule sheet, and then automatically place each employee and their shift on the following day sheets. (example: Joe has 2-10 on Monday, 10-6 on Thursday and Friday) So the Excel puts Joe underneath Split 2-10 for Monday, and Morning 10-6 on Thursday and Friday.
I have attached a file that I need a little guidance on. The list of information on the left needs to be organized and put in the appropriate tables on the right. Each row represents one person and I need to know how many people from the list fall into each category. For example: The first row has a gender of "1" and the age is "46" and the status is "1". Therefore, this person falls into the category associated with "K11", I filled it in already. My list will vary in length from 20 rows to 20,000. I am not sure how to go about this and have been frustrated by failed attempts.
View 6 Replies View RelatedI'm looking for a opinion on what might be the best way to organize some data for sorting later.
I work for a company and we have a lot of equipment. I have a spreadsheet with each piece of equipment in a row. Column A has the equipment name with all the following columns having info about the equipment. One example would be voltage, some equipment runs at 120v and some runs at 208v, some runs either or and some runs at other odd ball voltages, although this is rare. I am trying to determine what the best way to enter data like this would be. Would I be better off entering one column labeled voltage and filling in the voltage, but this becomes problematic when a piece of equipment can run multiple voltages. The other though is to do multiple columns, one labeled 120v, one 208v, one 480v or whatever and go from there. Than put a "X" or Yes or something in the columns that apply.
I have this same scenario for multiple variables like connectors, hardware accessories and things like that. I am trying to think this through before entering all my data of what could be the negative effects of this.
Would it be possible/make sense to do a radio box?
My end result is hopefully to be able to say I want a piece of equipment that meets multiple scenarios like runs 120v, this accessory and this connector. Ideally setting up some sort of easy sort method in my column headers.
I have a spreadsheet converted from pdf having more than 30 Columns .for Example( name, address, tel. website , products etc).
Problem is that data is not consistent in all columns.Some have full range(all 30+ columns having data) and in some not( only some columns having data )
I want to organize data as per headers and leave cell blanks or fill in (N/A) where data is not available for that field. How can I correctly populate it.I need data as per headers in rows.
Here i am dealing with 2k rows with 30+ columns.
I have a list of 35k files and the number of files keep changing every week. At the end of the month I manually have to sort the files i.e move files to the respective folders, delete them or take no action. This takes me an entire day. There are useless files which needs to be deleted.
Si NoFile nameFile PathNew PathAction
1Store C:Jay PersonalDesktopRXProjectSix SigmaStore.xlsC:Jay PersonalDesktopNewProjectSix SigmaMove
2AppointmentC:Jay PersonalTeam QualityAppointment.pptC:Jay PersonalTeam QualityAppointment.pptDo Nothing
3QualityC:Jay PersonalDesktopRXProjectSix SigmaQuality.docDelete
The format is as follows. File name is the file which I intend to move, File path is the current file path, New path is the path where I want the file to be moved, Action (Move, Do nothing, Delete) is the action i intend to take on the file.
I'm setting up a type of form in excel where I have some validated drop down lists and I'm trying to use a macro that will, upon a button click, take the data from the drop down lists that are entered into the boxes, and organize them into a chart on a different sheet of the document. This would be the form with some drop down lists.
Juice Type
Apple
Juice Size
8 oz.
Juice Buyer
Ronald
I want to add it in to a table with every button click filling in a new row in a table like this,
Juice Type
Juice Size
Juice Buyer
Apple
8 oz.
Ronald
Is there a way to sort the worksheets in a workbook after they have already been entered. My clerk put 200+ worksheets in one workbook....out of order.
View 7 Replies View RelatedI have a question on how to organize cells from lead sheets that I load inside excel, when it loads it shows as is:
Name, Last, Address, City, State, Zip, Phone #
I need it to be organized so when I print they read:
Name
Address
City, State
Phone#
Instead Of them All together in a landscape view.
I am in process of making an appraisal system for my organization through excel templates .Each employee will Have 2 KRA templates .One specific to their roles and other in the area of their interest.
So while rating i will select role of the person and his interest area.Then the comprehensive template integrating both templates should be generated from the master templates which are in place.If i make any edits in Integrated template,it should not be reflected in master templates
I would like a macro to do this...If a cell in column G is blank and the cell in the same row in column C is NOT blank, highlight the blank cell in column G Red.
I need the search to stop ONLY when it gets down to the bottom-most row of data in column A.
Note: Any row headers will always be in row 1 only
Current...
I have a bar chart that monitors month over month activity. I use Layout 5, which allows for the numbers data to neatly be organized in a table below the bar chart. However, i recently started using the data on a secondary axis to accurately reflect trend lines. It works great, but now the data table below displays redundant information. see attachment.
example.jpg
I just want that data in the data table to be displayed once, completely leaving of the axis legend.
Sub SplitOut()
Dim DataSH As Worksheet, OutSH As Worksheet
Set DataSH = Sheets("Input")
DataSH.Activate
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For Each ce In Range("D2:D" & lastrow)
Application.StatusBar = "Actioning " & ce.Row & " of " & lastrow.............
I am working on this spreadsheet that has a lot of information that will only get bigger as time goes on. I would like to organize the contents of one column into separate spreadsheets. So if I have "stacking" in one cell of that column, the whole row associated with that cell will go to a new spreadsheet labeled "stacking".
I have attached the sheet. I would like column D (Type_Code) to be organized by the words in that column. So I would have 5 or so worksheets labeled stacking, cartoner, wheel, inspection, etc. The data here is input by a machine.
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
Excel 2010
Userform = Tab 1
Database = Tab 2
