I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).
I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.
I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.
1) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?
2) How do I get the options from the userform to the worksheet?
I have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.
I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.
However, I can't seem to build a macro that will run from whatever cell I am in.
Everytime and try it just goes from the cell I was in when I recorded the macro.
For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.
I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"
I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.
In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,
Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following Cells(J6)*1.04)*2080/12 If False then ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following Cells(J6)*43.33)+((J6*1.04)*129.99 If False then ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following Cells(J6)*86.66)+((J6*1.04)*86.66 If False then ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following Cells(J6)*129.99)+((J6*1.04)*43.33 If False then ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following Cells(J6)*1.04)*2080/12, but put this into column P
If row 6 Column O if the month is greater then 1 then I need the formula to do the following, =J6*2080/12
What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.
In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.
I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!$A$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.
I'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
I start an Excel program from a shortcut in different folders. I know that I can find the path of the Excel program in "Parent.Path". But I don't know how to find the path of the folder the shortcut was in. It should be findable because when I try to do something like <Save>, Excel knows the correct folder there.
An example: I open Excel in "Folder 1" using a shortcut to the Excel Program in "Folder 2". Parent.Path tells me "Folder 2". How do I find out what "Folder 1" is from inside Excel?
I have a form pop up when user runs a macro and it populates the form beginning with ActiveCell. How do I always populate the form beginning from column A (row based on ActiveCell)? It would be really helpful if I could just set cl to take the ActiveCell and back it up to column A.
So it will work much in the same way as match does for VLOOKUP ($A2,'Sheet 1'!$A:$FG,MATCH(AP$1,'sheet 1'!$1:$1,0),FALSE) but the array needs to change.
By doing this my vlookup will work automatically depending upon the header.
I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.
After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.
None of the functions I have see so far will facilitate this...
I would love to be able to click on an assigned button in a new sheet, and a pop box appears.
Then I can type in a word, click find, and the macro will go through my whole list of keyword phrases and find all the words STARTING with the word chosen in the pop up box.
Yes, I could just sort from a-z, find the word, then copy and paste etc. But I work with large lists sometimes, and its just so much easier to type the word, click find and the macro returns the list of just the phrases with the starting word I've chosen
As a note. I did put a post up a while ago which a coder called Jindon solved which works perfectly. (A great job by the grand master) This macro finds the words ending with. So, what I would really like is exactly the same macro, but it finds words at the start of a phrases not the end of a phrase. I'll post Jindon's macro now
Sub Find_Words_Ending_In() Dim sTime As Single, res As String, msg As String, x As Range Range("c3:c" & Rows.Count).ClearContents res = InputBox("Enter word to find") If res = "" Then Exit Sub sTime = Timer On Error Resume Next................
Is it possible to set the starting path for the open dialog - using expression. GetOpenFilename so that the contents of the correct folder are visible immediately?
Using an external UNIX program that quieries a database, I export all the data found based on my query and export the report as a text file. THe text file can be up to 1 MB of raw text.
Selecting all text and pasting the data into collum 1 using PASTE SPECIAL -->(TEXT)
All Data is in Collum A
Each record returned has between 40-50 values each on a seperate row, only 10 of which I need.
I am looking for a way to find the rows that begin with the unwanted field, and then delete the entire row.
Otherwise ignore the row.
IE: AUDFI : (data path loc here) BILLNUM : 060606 CUTNUM : (0000) SD : 120012 ED : 121259 CC : 123456789 MATERIAL LISTS AND ITEMS COMMENT : Multiple lines of text here with continuing free form data, numbers, operators,
then the next data file would repeat:
ADUFI : (data path loc here) ... ... ... etc.,
In the above example, I want to delete the rows AUDFI, & CUTNUM and move the below items up to take the empty rows place. The BILL NUM, SD,ED, and COMMENT are filed that are always kept (as well as others). Each record has roughly the same data, each tailored to an event or object.
The number of rows of data dumped into excel can easily exceed 50000, about 200 - 1500 records in the report.
After wards, the data is dumped to a text file for easier analysis.
In BASIC my line might look like 5 START 10 if A:$="AUDFI :" then delete row, move below contents up. 20 if A:$="CUTNUM :" then delete row, move below contents up. 30 END
(I think there needs to be a loop since the vaules AUDFI and CUTNUM (as well as other unwanted fields can occurr 200 - 1500 times each.)
I am using Chip Pearson's example of importing text into my worksheet.
Sub ImportTestFiles() ImportTextFile "C:Documents and SettingsKevMy Documents est.txt", "," End Sub
Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
Application. ScreenUpdating = False On Error Goto EndMacro:
Let's say I have a long macro and I want to test some code a ways down in the macro, but I don't want to have to run through all the code to get there, because I already have my Excel spreadsheet set up at the point I need to check and I just want to start checking the code a ways into the procedure.
Short of commenting out all the code above where I want to start, is there another easier way to do this?
I have a list with all our company cars (300). I also have a list with all the transactions of the fuel cards (12000 lines). Now I need the find for every company car (exact match) the nearest fuel-consumption-date (closest match to the given starting date) and take the mileage of that transaction. I've added an example. I have tried linking carnr and date (like carnr&"#"&date) and match these, but I don't always get the right car number.
Assuming that Sheet2 is blank, this of course copies the rows to Sheet2 starting at Cell A2 and down column A. I would like to modify the code so that I can specify at which row the copy should start. For example, I might want to start the copy at A1 instead of A2, or at A10 instead of A2.
