Extending Range Function To Account For Different Row Lengths?
Apr 25, 2014
VB recorder in Excel. The macro is designed to process a spreadsheet that will have the same number of columns and same type of data in each column, process this data with a formula (hat tip: AlKey+XOR LX) and then clean up the columns ready for use in another program.
I have got it working but when I alter the row length the macro only works to the final row in the recording spreadsheet. I realize this is because when recorded the macro I told VB to set the range to a given row.
I remember there is a way of setting the Range function that tells VB to always go to the bottom of the rows filled with data but I can't find it for the life of me.
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273 {=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date ID CREATE Suspend STOPPED Unsuspend Elapsed Days (Create to Suspend but NO Stopped) Elapsed Days (Suspend Only, No stopped or Unsuspend)
I have a formula IF(A2=A1+1;B1+1;1) that I need to develop further, so it can recognise same date for same patient, so it doesn't start back at 1. e.g. ( see attachment) In column B are real dates( 01.09.10-01.09.12, trough my formula => day 1,2, 3). What I need help with is to make the formula linked to column C so that if there are more of same date in column B( because of several different analyzes taken same day of the patient-column E) fore example several 09.10.11 should be recognized as day 2 all of them for the same patient, and if a new date follows for a new patient it should start back at day 1. This is so I can make a diagram of my analyzes taken at the ICU, the problem is that my research is during 3 years and because of that I have different dates of analyze, and I need them to be comparable so that the day that they arrive changes to day 1 and so on. PS! I have made an example were I have changed the patients IDs and taken away unnecessary data.
I imported my data and I have a macro that updates the data. Nothing new until new items were added, creating new rows at the bottom and now the Table Format does not include these rows. There are so many formats available that I don't want to guess which one I used when I created it I just want to extend the data range.
I do not care if this is done through the macro or right on the sheet as this does not happen that often.
I'm having a problem with a dynamic range. I've re-checked my formula at least 10X and its syntax is correct. The problem is the dynamic range stops short 3 rows up from the last entry.
Goal: To extract the barcode (numbers and letters between B/C: and , P/G) from the following string:
"Codec B/C: A01234567, P/G: 123456728912345" Current Function: =MID(A1,(FIND("B/C:",A1)+5),(FIND("P/G:",A1)-14))
Problem: I need this function to work for barcodes of varying lengths. For example, in some cases the barcode may be 8 characters long and at other times the barcode will be 14 characters long etc.Below is a screenshot of how I have this currently set up in excel.
I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.
For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......
I am trying to use a Mid function to help me in my vba problem.
In my first table I have a list of account numbers. E.g. 'ZZ500543'
In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'
I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
I would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
How can I extend a table backwards my table starts from column B and i want to make it from column A to make it start from column A that is, if its possible.
I have uploaded my workbook : Stock Request - Copy.xlsm
Using the oldest version of Excel (200) I have to insure all members of our squad can utilize this function. Trying to format a whole row based on one cell in that row that reports number of days till the next physical. My formatting works correctly for the initial cell, but does not extend correctly to the rest of the row. I am using the "Cell value is" option. I used:
Cell value is between 1 and 365 - color green Cell value is between 0 and -30 - Color Yellow Cell value is between -31 and -365 Color Red.
All cells with no value in it are yellow, even if the original formatted cell is another color and the cells that have other data in them such as the physical date, have no color in them regardless. read and tried several approaches from other threads with no good results. considering the results from those threads, I am curious if it has to do with it being an older version.
I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
I want to extend a formula like so- =Sheet2!M3 =Sheet2!M60 =Sheet2!M117
Basically I want it to go up in increments of 57 when I copy the formula down. Is there an easier way to do this rather than typing it over and over again? I looked on an older post and saw some information about OFFSET and INDEX but couldn't figure out exactly how that worked.
I work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas.
The data is imported into excel from a database and therefore is constantly growing.
What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service.
Because the data is ever expanding I would like this on a separate sheet from the actual data.
I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
I have a list (external), which will be updated every month from an external source with new numbers and possibly a new size. This will be a count of services people use. On a 2nd sheet I need to allocate a cost to these services (pivotdata). This works fine if the list stays the same size, but it most probably won't so I need it to update automatically, not manually extending the list on the 2nd sheet. In brackets are the named ranges of the data in the spreadsheet.
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work
unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.
I can't keep going manually as I need to go to about 300 cells...
I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from... 14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.
I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.
I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.
I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.
eg: what I have: Time Time Tidal Height (2 sec intervals) (min) (meters) 1:15:00 1:15 1.342 1:15:02 1:16 1.221 1:15:04 1:17 1.115 ... 1:15:58 1:43 0.024 1:15:60 1:44 0.012 1:16:00 1:45 0.008 ....................
I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally
Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.
Is there any way that a macro could be created to do this.
10015200 US Government Bonds 18,369,423.52 10012301 US Government Bonds 10,232.21
There are many varying lengths of digits. I am trying split off the numbers to the right of US Government Bonds into another column. Here is my formula: =RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))
My result is 9,423.52. Its not pulling the whole number.
