Find Last Cell Used And Create A Formula Using That Cell
Jul 31, 2014
I want to find the last used cell within a column. Then I want to write a formula to find the Min from the cells above (Row 1 is a Header, so I do not want to include it).
Like this:
Column A has data in an undetermined amount of rows below.
A1 is a header and I do not wish to include it in the formula.
A2:A15 has numerical data.
I want A16 to have the formula =MIN(A2:A15)
Next time there may only be data in A2:A10 or maybe A2:25...
View 2 Replies
ADVERTISEMENT
Jan 7, 2014
Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.
I tried this but it's not working:
=IF(C3="0","",C3-C2)
View 2 Replies
View Related
Mar 10, 2013
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
View 4 Replies
View Related
Jan 14, 2013
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
View 8 Replies
View Related
Feb 13, 2014
I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
View 1 Replies
View Related
Jan 29, 2014
I am trying to create an "if,then" formula that shows:
if cell E8 is greater than 171, then i'd like cell E9 to show the value of cell in E8.
View 6 Replies
View Related
Jan 21, 2010
I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.
Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.
I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.
View 10 Replies
View Related
Jan 26, 2006
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies
View Related
Jul 2, 2014
I need to pull information from a cell that is full of text and numbers and get it to creat a formular.
More information in the spreadsheet example
View 9 Replies
View Related
Sep 8, 2012
I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
View 1 Replies
View Related
May 23, 2014
When I enter data in the cells A1 and B1, C1 needs to calculate the result. (not copy and paste)
For examle: I have formula C1=(A1+B1)/2
C column for formula, but shows result only when there is a data in A and B
View 7 Replies
View Related
Mar 13, 2014
I'm using the VBA code below for a piece of code.
[Code] .....
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
Something like:
[Code] .....
View 3 Replies
View Related
Apr 20, 2012
I am trying to create an active cell formula based of variables from given cells.
With ActiveCell.FormulaR1C1 = "=CONCATENATE(""RU"",RC[-12],RC[-11],RC[-10],RC[-9])-CONCATENATE(""MU"",RC[-12],RC[-11],RC[-10],RC[-9])"
ActiveCell.Formula = ActiveCell.Value
But This does nothing.
View 5 Replies
View Related
Jan 11, 2010
i'm working on an excel file that consists of 31 sheets one for each day of the week, the information about how much we make each day is inputted on the end of each day, i've consolidated all the sheets into 1 big sheet and now i want to create charts over the production, so what i would like is a code that searches the huge sheet for Machine name (Found in Column A and Column I) then walk 5 cells to the right and find the number of litre's made by that machine on that shift.
The different shifts are
Formiddagsskift - Morningshift
Ettermiddagskift - Eveningshift
Nattskift - Nightshift
I will include an example of the data. The data should be pasted to the "Formler" spreadsheet. It doesn't matter if you don't wanna do this i can simply do it later i just need the code to search, and go 5 cells to the right and remember which cell it is, and make sure it is in the right "Shift" ie, all the 282 machines for morningshift are summed in c7 and c10 has eveningshift and c13 has nightshift.
Quite time consuming to search through 31 days of production to find the correct machines on the correct shifts. And the data changes for each month so this would be a great asset to me.
View 13 Replies
View Related
Aug 13, 2014
The below is an example of some data I have in a workbook ...
Basically I need a formula to look through the data and search for any fails within Column A then I want it to pull the Reason
The Pass / Fail could appear anywhere within the range and we will only get a reason for any fails
A B
1Result Reason
2Pass N/A
3Pass N/A
4Pass N/A
5Pass N/A
6Pass N/A
7Fail System Error
8Pass N/A
9Pass N/A
10Pass N/A
11Pass N/A
View 2 Replies
View Related
Dec 11, 2008
I had posted this earlier but it was moved. I think this post will explain it better. I put data into a worksheet called "Budget" each week. When I put the data in I type a date in Cell "BL7". In another sheet called "EV Report" row 8 has week ending dates (i.e. Sunday of each week). I want to run a macro after I change the date ("BL7") and data in the "Budget" sheet that will search row 8 of the "EV Report" sheet to find the date in cell "BL7" of the "Budget" sheet. Once it finds which column that date is in I want it to put a formula in row 13 of the same column (the one found based on the date of BL7) that will add the value in row 12 of the same column with the value in row 13 of the previous column.
I don't want the formula to be there until the macro is run. I'm doing this because I have a graph of the formula and I don't want the graph to show the other cells.
View 2 Replies
View Related
Aug 27, 2012
I need to find the last row of a table I'm manipulating with VBA. The last row contains only the subtotal of a specific column. I am currently using the following code to find the last row. (NB:'j' is the the specific column, the formula in the last cell is shown in the Do While conditional, but the value 109 can change)
Code:
Do While Cells(i, j).Formula "=SUBTOTAL(109,[BID PRICE '#1])"
i = i + 1
Loop
However, this code will not work if the user adds a row to the table. I've tried using a partial string search, but the do while conditional is always true. I assume this is because the cell contents are a formula and not a string.
Can I change my conditional to search for just the "=SUBTOTAL" portion of the formula?
View 1 Replies
View Related
Apr 24, 2006
I have a spreadsheet that is pulled automatically from an access database.
The information that is pulled is invoices that have been paid for each of the temps working for our company.
Each month I have to give a figure per cost code on how much they are accruing so that they can see how much is still outstanding.
Right so what I am trying to do is have a macro take the temps charge rate then * 40 hours and place the amount into the empty cell.
As you will see in the attachement some of the cells are coloured. The purpose of the colouring is show that a invoice has been processed and paid for that week so no value is needed in that cell.
Then total all the cells at the end.
View 9 Replies
View Related
May 30, 2007
i need to find the last populated cell in the row say I9:CN9 and return the description for that column the resides in I5:CN5 and then fill the formula down for about 500 rows, just not sure even where to begin, do i need a custom formula in VBA? as i can't seem to find a combination that works built-in...
View 5 Replies
View Related
Dec 22, 2009
I have two columns of names of contiguos names. I want to copy all of them in a new column using a formula that copies the first column and when it reaches a blank cell only then move to the second column.
So basically, a formula that copies a whole column up to a blank cell and then copy the second column.
View 14 Replies
View Related
Jun 26, 2012
I have a formula in cell J2:
='H:Folder[CI10001G.xlsm]Sheet'!$C$5
I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:
='H:Folder[CI10004D.xlsm]Sheet'!$C$5
And then this formula will then successfully look in the newly directly file path.
View 9 Replies
View Related
Feb 4, 2014
Is there a formula that can find a keyword within a cell then replace it with a formula that will add the adjacent cell value up.
A1=103 B1=104
A2=104 B2=105
A3=105 B3=Out-1p-(1)
A4=110 B4=111
A5=111 B5=112
A6=112 B6=113
A7=113 B7=114
A8=114 B8=Out-3p-(23)
I am looking for a formula that can automatically calc (B3) to 106 (based upon cell A3) if the cell has a value of (*out*).
View 1 Replies
View Related
Sep 6, 2007
First one needs to look through a single row and find where the first cell with a value (text) in it, then finds the date that that cell is under.
Second one needs to look through the same row to find the last cell that has a value (text), then finds the date again that that cell is under.
Example workbook is attached
View 2 Replies
View Related
Jun 11, 2008
I want the macro to:
1. search A1:AZ1 to find the cell that has the text "VBA Test" in the cell. There could be other text in the cell as well - this is not an exact match - but these two words are the common text.
2. go to that cell
3. go to one cell below that
4. enter a formula (I've got it from here ....)
View 4 Replies
View Related
Sep 6, 2013
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies
View Related
Feb 2, 2014
I am making a form with drop down boxes and auto fill to make things easier. I have one Box for the Company selection another box for the occupation selection and I need a formula to find a $ value based on the selections made in these two cells. If I could establish the actual cell reference of the selected data in the second drop down list. The data will be much larger than this example and will live on a separate sheet.
View 2 Replies
View Related
May 15, 2008
I have a cell with data such as: a0001-0004, a12, C8AF7-8, b17, j35-40 and i want it to output in the same cell(basically write-over the data): a0001, a0002, a0003, a0004, a12, C8AF7,C8AF8, b17, j35, j36, j37, j38, j39, j40.
View 4 Replies
View Related
Jul 24, 2014
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
View 9 Replies
View Related
Dec 3, 2006
what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example:
On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.
View 6 Replies
View Related
Jun 28, 2013
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
View 6 Replies
View Related
