Copy/Fill Down Formula To Maximum Row Of Last Cell In Columns
In a worksheet I retrieve data from a SQL query. Now I have to add 5 different calculations per row and each calculation will be stored in a new cell (so 5 columns will be created)
I want to do this with a loop macro and calculate this till the last "filled" row. The number of rows differs every month.
What loop code should I use and can somebody give me an example of the VBA code.
Note that some formulas contains nested If-functions.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automate Formula Copy/Fill-Down Until Last Row
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro. My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead. I'm working with a simplified version of what I want to do to eastablish a Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy. Heres my Scenario If my Excel Cells were Cell A1 = 135252 Cell B1 = $13.95 Cell C1 = 3 Cell D1 = Oversize I want to run a Macro from E1 that would Result in text of Our Stock Number is 135252 - The Price is $13.95. We have 3 in stock. Type of Packaging is Oversize being left in Cell E1. I would like the Macro be able to run on other rows as well when launched from Cell E of that row. My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows. My Excel Formula to accomplish the same thing is - ="Our Stock Number is "&A1&" - The Price is "&B1&". We have "&C1&" in stock. Type of Packaging is "&D1&"." If I could get this much working, I think I can expand and edit to encompass my actual application.
View Replies!
View Related
Fill Cell W/ Value Present In Another Row, If Columns Match
I have a slight conundrum that goes as follows: If in a given row, the cell in column A is empty, but the cells in columns B and C have values--would it be possible to automatically fill in the empty cell with the same value of another rows column A cell, if both rows share the same value (nb: not case sensitive) in the column B and C cells. For example: If one row, say Row 44, contained the following: Column A Cell = Column B Cell = sunday Column C Cell = dog and another row, say Row 10023, contained: Column A Cell = Walk Column B Cell = Sunday Column C Cell = Dog How would I go about automatically filling in Row 44's column A cell with the value "Walk", as both rows columns B and C cells match up. I should mention that I am dealing with a sheet with over 30'000 rows, so it would be incredibly time consuming to find matches and input the missing value directly.
View Replies!
View Related
Copy Formula Across Columns But Increment Row
I am trying to link cells between two worksheets and am having some difficulty. For instance, in E17, I want the cell to reference cell H41 on sheet 2 and for F17 I want it to reference H42 on sheet 2, etc. However, using the formula =Sheet2!H41 when I cut and paste the formula it changes it to =Sheet2!I41 (ie it changes the column and keeps the row constant). I want to find a way to fill a large amount of cells this way and would prefer to not have to manually change each cell individually. I assume I would use the indirect( and the column( commands but can't seem to figure that one out.
View Replies!
View Related
Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View Replies!
View Related
Determine Cell Above Maximum Value Of Row
[TABLE] item01/11/200702/11/200703/11/2007 a1710 b2611 c359 [/TABLE] I have the above table. I would like to determine which is the item that has Maximum value on a particular date. example on 2/11/2007, the maximum value is 7, so the result should be item a. How can I achieve this by using a formula.
View Replies!
View Related
Copy Cells Within Row Containing Green Fill
Look within sheet "highlowperf" column K and copy the row containing cells that are filled green in column K into sheet "overallperfomers" starting from row 9 within the "overallperformers" sheet until completed. It needs to be from row 9 as i have other data above this which i need to keep intact. Further to this i do not require all information from the row that is found to contain a green fill. I wish to copy columns A B C D E F G K. If this second part is not possible I can settle to just copy the whole row over and i will try and work around.
View Replies!
View Related
Fill Formula Reference By Rows When Copying Across Columns
I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.
View Replies!
View Related
Find The Maximum Value In One Column And Retreive The Name Of Cell In Corresponding Row
I spent couple of hours trying to figure this out, but without success. Please check the attached example. I need a formula that will find the max value (number of views in English anguage) in the column "I" and display the corresponding "Headline" value in the column "B". In the example it is "Headline 5". I can not change position of the columns, i.e. "B" must remain "B" and "I" must remain "I". I experimented with Index and vlookup but to no success. I have outlook 2003.
View Replies!
View Related
Copy Fill Formula With A Range
I have a formula that is averaging seven (one week) consecutive cells, I want to Copy/Fill the formula down the worksheet for the entire year. Is there a way to use the fill handle to drag down and make the formulas use the next seven colums? I have removed the $ from the row numbers in the original formula, but when I use the fill handle it fills like this: =average(B2:B8) =average(B3:B9) I would like it to fill like this: =average(B2:B8) =average(B9:B15)
View Replies!
View Related
Copy/Fill Down Formula Macro Code
I have created a macro with the below formula in it: Range("L2").Select ActiveCell.FormulaR1C1 = "=LEN(RC[-1])" Range("L2").Select Selection.AutoFill Destination:=Range("L2:L234") Range("L2:L234").Select I am using column K and it looks up the length of this cell. When I created the formula I was using info that only went to line 234 which was fine. Once I clear the info in column K and input new info it can go further than line 234. How do I get the macro to look up the whole of column k without having to create a new macro each time?
View Replies!
View Related
Drag/Fill Formula & Skip Some Row References
I have the following formulae in seperate cells were A1, A2 and A3 are the cells A1 =MAX(Data!C2:C3) A2 =MAX(Data!C4:C5) A3 =MAX(Data!C6:C7) I would like to drag the cells so that automatically the cells below A3 are updated as follows A4 =MAX(Data!C8:C9) A5 =MAX(Data!C10:C11) etc up to 600 rows Unfortunately it is not being updated in that order. Do you have any suggestions how it can be done.
View Replies!
View Related
Fill An Empty Cell With First Data Cell In The Same Column, Next Row(s) Down
I am looking for advice on how to move cells containing data (columns D to K in EXAMPLE A), upwards so that the information lines up with data already existing in columns A to C (to be included as part of an overall VBA routine). In the upper example data in cell D2 needs to move upto D1, E3 to E1, F4 to F1, G7 to G1 etc and K9 to K1. Now, data on following rows is to move upto row 2 eg: E5 to E2, F6 to F2, G8 to G2. The first 'block' of data starts at row 1 and finishes in this case at row 9. The next 'block' of data starts at row 10 and finishes at row 18. Row 19 shown is the start of the next 'block'. These 'blocks' may have upto 20 rows ....
View Replies!
View Related
Compare Columns In 2 Worksheets And Copy From Same Row
I'm trying to do the following comparison and copy: Step 1: See if value in Sheet1 column A matches any cell in Sheet2 column A Step 2: If a match exists then copy value from that same row on Sheet2 column C (Min) to the same row on Sheet1 column D (Min) Step 3: Do the same with the Max columns For example, Sheet 1:A5 (11PL10012) matches Sheet2:A5 so copy Sheet2:C5 to Sheet1:D5 (the Min columns). I've attached a sample spreadsheet for your review.
View Replies!
View Related
Copy Columns With Specific Text In Header Row
The column header row for a string; if the header contains one of two possible strings then copy the entire column. I've tried the following code but it's not working. Select Case oColHeader Case InStr(oColHeader, "how much", 1) > 0 Selection.EntireColumn.Copy Case InStr(oColHeader, "level of sat", 1) > 0 Selection.EntireColumn.Copy End Select
View Replies!
View Related
Copy 3 Columns And Paste With Varying Row Offsets
I am trying to copy three columns of information from sheet1 into sheet2. When pasted I need the information to be spaced or offset as per below sample. Sheet1 HTML ABCDEF 1#AnimalDescription 21Dogfour legged fur ball 32Catother four legged 43Horsegsdfgdfgdbfb 54Snakedfgegergeg 65Ratlkjlmlbnm, 76Birdtshgjmgjkk Sheet2 HTML ABCDEF 1#AnimalDescription 21 3Dog 4four legged fur ball 52 6Cat 7other four legged ...................
View Replies!
View Related
If Fill Down Formula To Designated Last Cell?
I read in a book that if you enter a formula in a cell, like cell A1 contains =rand() for instance, that if you select Go on the menu tab, and then enter the final destination cell or range (ex: A1,A200), then hit ctrl+Enter simultaneously, it will fill the formula down to that cell. I can get it to select the range, by hitting Shift+Enter, but not copy down the formula using Ctrl+Enter, or Ctrl+Shift+Enter. I am using excel 03, XP. It only returns blank values for the range.
View Replies!
View Related
Transpose Row Formula To Columns
Using the formula =E+1 will produce a sequential list when copied down. Changing to E+2 produces a list that increases by 2 in each row. I am looking for a formula that will give me a list of column references that increase by 2 . If the first row is row "F" the next row will be "H" then "J" and so on. Something like =E+(columnF+2).
View Replies!
View Related
Formula For Columns Of Different Row Lengths
Say I am downloading a column of stock prices and it cannot be predicted precisely how many rows will be contained in each downloaded column. The column is, however, always Column E. Two formulas must be computed. 1) Last row with a price in column E / average (all rows, beginning with row 2 with prices in column E) 2) Last row with a price in column E / average (last 50 rows with prices in column E) Can Excel manage the variable number of rows required for these formula, and if so, can somebody recommend how to write these formulae?
View Replies!
View Related
Maximum Hidden Columns
Is there a maximum amount of hidden columns within Excel? I have double-checked protection, and I have enough columns total for the action I'm attempting to do. I am using Excel 2003, and working on a rather large spreadsheet for a forecast. The basics of it is that I have macro's within buttons set-up so that users can hide columns and just view certain columns for approximately 30 columns per week and 5 weeks. And I can run the hide macro's for up to 4 weeks at a time, but when attempting to hide the 5th week it gives me a "Run time error 1004" Unable to set the Hidden property of the Range class.
View Replies!
View Related
Auto-Fill Formula When Adjacent Cell Changes
what is prompting this formula to auto fill down when a value in an adjacent cell is entered. In column A I enter an unformatted fax number. In column B I use the following Text Formula: "="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4) Someone in my office copied the workbook and now any time a value is entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office. There are no macros embedded in this workbook so no event is firing to cause this. I have attached a small copy of the workbook called "Auto format1.xls" How to use: -Select cell B20 and not nothing is in it. -Enter any 9 digits in A20 and B20 somehow copies down the formula from B19. how to duplicate this.
View Replies!
View Related
Add Formula To Cell & Fill Down Macro
I have written a macro that creates a new spreadsheet and copies text into it. The number of rows varies depending on the input. Yet, running the macro results in a sheet with the max rows (65k+). This is unmangeable, as it makes it a huge file and size and harder to print. I only want there to be the number of rows that have text in them. The larger problem is how these were even created in the first place. Currently: ' Formula for Job Name Range("A2").Select ActiveCell.FormulaR1C1 = "=LEFT(RC[12],8)" ' Fill Job Name Range("A2").Select Selection.AutoFill Destination:=Range("A2", Cells(Rows.Count, "A")), Type:=xlFillDefault
View Replies!
View Related
Copy And Paste Columns Based On Formula
I need to copy certain columns from one sheet to another sheet based on a formlua on the date. So, on 'Master Sheet' I have a column called 'Date'. I would like a formula, or macro, which does the following. If date (column D) is over 2 weeks old (today's date - 14 days) then copy columns A, D, S and T to another work sheet called 'Not Attended' and paste them into columns A,B,C, and D.
View Replies!
View Related
Increment Formula Row Number Filling Across Columns
I am trying to increment a row when i copy it across columns. I have searched for a couple hours on how to do this, but I have not come up with a solution that I can understand that works for my situation. I found this page, but I guess I am too novice to comprehend it. [url] I would like the number in this formula to increment when i copy it to the adjacent column. This formula is in B178. =IF(B8="x",A8,"") So in C178 I want it to be =IF(B9="x",A9,"")
View Replies!
View Related
Increment Row Number Within Formula When Copying Across Columns
to copying a formula from one column to the next adjacent column and also incrementing the 2 numeric values inside the formula. Please see attached sample. One value increments with each new column whereas the other increments with each new row. Please see attached sample. And in the last row there is the average formula which must copy the new column and average the new column. I tried to create a Macro in attached but it did not work with formulas.
View Replies!
View Related
Find All Row Values Selected In Multi Column ListBox & Fill Adjacent Cell
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives. After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero). Listbox and expected result are in attached example worksheet. The attached does show the need much better then I can explain it.
View Replies!
View Related
Mark Maximum Value In Range Block Based On 2 Columns
I have a long list of stock values (col C) arranged in time blocks so; CREATE TABLES LIKE BELOW?B C M N 19:00 50.00 0 47.1 19:00 51.00 0 47.5 19:00 52.00 1 48.3 19:00 51.00 0 47.3 19:10 36.00 0 45.3 (nb time has changed) etc I need a routine that takes the following action; When the value 1 occurs in col M row .. (it will only occur once in each time block) and N1 on the same row has the highest value [ 1 and 48.3 above] put 1 in column O on the same row else a 0. If 1 occurs in column N (time block X) and the value in N is not the highest then store a 0. This needs to be done for each time block. It feels like this can probably done by an If or vlookup type of routine but I can't come up with one that works across blocks of times.
View Replies!
View Related
Formula + 1 (copy Only 1 Row)
I play with 12 lots (rows) and these refer to another tab sheet where the result of lotery numbers are. So the first draw refers to Tab Results $B3 (=number.if('Lotto numbers'!$B$3:$G$3;Draw!B$3). Now I want for Draw 2 to copy the formula that Draw!$B3 becomes $B4. If i only needed to copy this 1 row below it is not a problem but I need to copy the formula 12 rows below ! Draw 1: Row 1: =number.if('Lotto numbers'!$B$3:$G$3;DRAW!B$3) Row 2: =number.if('Lotto numbers'!$B$4:$G$4;DRAW!B$3) Copied 12 rows below should become: Row 12: =number.if('Lotto numbers'!$B$3:$G$3;DRAW!B$4) Row 13: =number.if('Lotto numbers'!$B$4:$G$4;DRAW!B$4)
View Replies!
View Related
Find Maximum In Mutiple Columns & Delete All Values After
writing a macro to find the max value in each column and delete all the data points that come after it (or preferably: delete all the data points that come 2 rows down after the max, if possible). There will be many columns of data where the max will come at different positions in the column. Instead of deleting post-max values, it would also be acceptable to just copy values from the beginning to the max to the same column in a new worksheet.
View Replies!
View Related
Copy Row To Sheet With Same Name As Value Of Cell In Row
I have a workbook that has about 300 different sheets- each the name of a different chapter of a fraternity. I get new lists of members daily and have to manually sort them into the 300 different sheets. Is there any way I can automate this? For example, if column "E" contained the chapter name- in this case cell E38 contained "Oklahoma Kappa" , the macro would automatically copy row 38 to the first empty row in sheet "Oklahoma Kappa".
View Replies!
View Related
Insert Row And Copy Cell From New Row
Attached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?
View Replies!
View Related
Displaying The Maximum Value For Each Row Of Data
I have some code and it's not quite working. For each row of data in my table (each row of data is contained in columns D:F) I want to display the maximum value in column H, of the same row. For some reason though it is putting it in 21 additional rows down. Sub Winner() Dim lngMax As Long Dim lngRowLast As Long Dim rng As Range Dim rngAll As Range Dim rngRow As Range lngRowLast = Range("D65536").End(xlUp).Row Set rngAll = Range("D2" & lngRowLast) For Each rng In rngAll Set rngRow = Range(rng, rng.Offset(0, 2)) lngMax = Application.WorksheetFunction.Max(rngRow) rng.Offset(0, 4).Value = lngMax Next rng End Sub
View Replies!
View Related
Method To Copy Formula When Inserting A Row
some method by which a user can insert a row that will then copy any forumulas, not their resultant value, in the row above into the new row. The best would be for a user to be able to just use Insert > Row from the toolbar and then have a macro automatically run, but I am willing to use any possible solutions. I know that there are other posts on this subject, but I can not figure out how to make the information in them work for me. I know that I need to go to View Code in order to insert the code, but I am not clear on what to do after that in order for the code to function, even how to exactly enter the code (should I enter into General or Worksheet, into Sheet1 or ThisWorkbook, etc..). I saw someone mention something about using now() somehow to get code to automatically run or using toolbar buttons, both of which I do not understand how to do.
View Replies!
View Related
Have Data In Row , Copy Formula In Column?
I am having a problem with copying a formula and I know that there is at least one way in which to solve this. I have all my data in one row. A1 -C1 (for example) ... I am looking to reference these values to a column in another part of my spreadsheet, K10-30 (for example) How do I go about getting excel to let me do this? I know that if you want to do it opposite, like data in columns and copying to rows you would use =Indirect(Address(Column()-X,X) with the X's being numbers ... but I am not sure how to manage this in my scenario.
View Replies!
View Related
Automatically Fill Formulas In Newly Inserted Row From Row Above
What I would like to do is on a sheet when I insert a new row that it will "FILL" the formulas that are the row above it. For example I have cells A1-F1. On cell A1 there is 1, B1 there is 2...etc. When I then insert a new row I would like the row below A1-F1 to read. A2 = 2, B2=3 so it had a linear growth. I want to do this with my formulas so whenever someone adds a new line it knows to copy the formula as well but only in certain cells if possible.
View Replies!
View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1). The inserted row is changeable and is determined by whichever is the current active cell. Eg: Active cell is something random like E16 I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
View Replies!
View Related
|
TRACKER
