Find Column That Has Value And Put Formula In Cell
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.
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.
The simple table (attached) represents our problem.
We want the letter in Column-B that corresponds to the last non-zero entry in Column-A. So in the example provided, the answer will be cell B12 which is "L". Also, if there are blanks (rather than zeros) below the last non-zero entry, it still needs to work.
I have a a spreadsheet with content (text) in one cell pr line. The content can be in the range column 1 to 8. I want to make a formula where I get the number of the column where there is content. I know how I can make this by using nested IF's, but are there any better proposal?
I have 13 columns (C-O) of data that will span up to 1000+ rows. I want to put a single row of cells at the top of the sheet that will display the last row of data from all 13 columns. I want the row at the top to always represent the last row of data, which will always be the most current data.
I have a simple list of data on a worksheet. It is appended occasionally by the program user. The new data is always added to the bottom of the list so the list grows increasingly longer day after day.
There are a couple of formulas at the top of the worksheet that calculate statistics from the list. I'd like to make the formulas apply only to the last 25 rows of data. Basically, I need a way to make the ranges in the formulas automatically adjust themselves each time the list grows so they always use the last 25 rows of data.
I can do it using VB but I'd rather just build this into the formula on the worksheet if possible.
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB: Sub Macro1() ' ' Macro1 Macro ' Range("B10").Select Cells.Find(What:="as % of Revenue").Activate Range("B20").Select ActiveCell.FormulaR1C1 = "=R[-1]C/R10C" Range("B21").Select End Sub
I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section B = description C = size D = region E = cases ordered Row 2 = fruit, apples, medium, north, 25 Row 45 = fruit, apples, medium, north,
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()?
i have the following code, what it does is, it locate those empty cells in column M and insert the formula "=TODAY()". What i need the code to do is only insert to the empty cells in column M if there is a value(as long as is not empty) in the reference cell of column E.
VB: VB: Private Sub CommandButton3_Click() Dim wks As Worksheet Dim rng As Range
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match 2) In column "G", sum up all the values in "F" that go with the matching values in column "A" 3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
3. All the other cells in that row are shaded blue. Look at these.
4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.
5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.
6. For example, we begin in C1 with a count of 12 days so far - this was manually entered
7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.
8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.
9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.
P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.
how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!
the macro will look at the value I entered in cell A1, then find all instances of it in Column A in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
I'm trying to find the last cell in a column where blanks might exist in the data and as such I can't use the x1down method to find it.
To add to the complexity I am finding the column so I can't hard code the column into the code. How to find the last cell correctly? I am passing these variable to a select case statement thus why I am storing the values.
Code: Dim PayrollStart As String Dim PayrollEnd As String Dim PayrollRange as String
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.
1) Take cell A1 and copy adjacent cell B1 in new sheet. 2) Then search cell B1 to find a match in column A. 3) If there is a match, I want to copy that adjacent cell in B and into the new sheet. 4) If there is no match, then leave as is from before and move onto the cell. 5) Continue for the rest of the data.
Here is an example of what i am trying to achieve This is in the first sheet:
Column A Column B Car Saw Dew Jacket Pen Key Saw Screen Hand Shoe Jacket Window Screen Sock
Now I want this in the next sheet:
Column A Column B Column C Column D Car Saw Screen Sock Dew Jacket Window Pen Key Saw Screen Sock Hand Shoe Jacket Window Screen SockData 1.xlsx
I have a worksheet (Sheet1) with column A labeled MissionNumber. I have also created a Form which I can use to enter the next mission number into that column. However, instead of manually entering the number...when opening the form I would like it to check column A for the last entry...and automatically increment the mission number by 1 and have it displayed on the form.
I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z So for example B would be P1W1 and C would be P2W2, etc
what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ?
I am looking for a way to find the first blank cell in a column.
Range("A2").End(xlDown).Offset(1, 0).Select
The problem is that there are no 'blank cells because they have a formula in them that checks a different sheet for data. If there is data then it simply copies that data. If there is no data then the value of the cell is "". So the cell shows blank but in fact it isn't.
So how do I find the first cell that don't show data because of the formula that resides in the cell? Here is the cells formula..
I am running this macro or some variation of it, depending on the column I need the time entered into:
Sub MacroD() Dim LR As Long LR = Range("D" & Rows.Count).End(xlUp).Row ActiveSheet.Unprotect Range("D18:D" & LR).Value = Now ActiveCell.Offset(1, 0).Select UserForm1.ListBox1.Text = "Time" UserForm1.ListBox1.SetFocus ' UserForm1.Show End Sub
When I need to run the same macro again (say I've run Macro D once, and now I need to run it again to get the next time), the forumla overrides the previous timestamp. I need the macro to find the next empty cell in column D and enter the timestamp there.
I have a code which looks at sheet2, useing a date value, and returns the Vent value and the Tanker value to sheet1. Because of the file size I have pasted a small section of Sheet2 in the hopes you can see what I am doing. As can be seen my code is not going to work with Offset as soon as the Data fills further down the sheet(every 2 weeks). I need a way to reference the active cells column name (it is a named range) or header name. Both are the same. That is the header name is the same as the name of the range. there is a way to perhaps find the first cell of the column,which I guess would work,but have reached a stale mate with what I've tried so far.
Vent 01Vent 02Vent 03Vent 04 Date3/06/063/06/06n1/06/06 Tanker8248824617606 WhoJ AshJ AshEmptyHarry Date Tanker Who
Private Sub DueCommandButton_Click() Dim strRecordID As Range Dim rngData As Range Dim Vents2 As Range, Tanker_Result As Range Dim wsheet1 As Worksheet, wsheet2 As Worksheet Dim Column_Name As Range Dim ActiveCell As Range Set wsheet1 = Worksheets("Sheet1") Set wsheet2 = Worksheets("Sheet2")....................
