Macro To Apply Existing Formula To All Rows In A Sheet
Jan 26, 2009
I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.
These are the instructions I'm currently sending:
In cell n1 enter:
full phone number
in cell m2 enter:
=RIGHT("00000000" & J2,8)
Drag m2 to last record
In cell n2 enter:
=CONCATENATE(61,I2,M2)
Drag n2 to last record
In cell o1 enter:
Date of call
in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))
I am trying to apply named cell from another worksheet to existing formula.
For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message "Microsoft Office Excel cannot find any references to replace" when I try to apply the names using Excel menu "Insert - Name - Apply".
I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.
I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:
Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.
I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.
Code: Private Sub Worksheet_Activate() Range("B2:B7000").Formula = "=IF(COUNTIF($C$2:$C$7000,C2)=0,"""",COUNTIF($C$2:$C$7000,C2))" End Sub
Is it possible to amend this so that it only applies the formula to the used range? Although the current range goes up to 7000 this increases the file size so to keep it to a minimum I was wondering if there was a way to do it only to the used range.
I have an excel list of My Existing Customers and have recently purchased an excel list of all possible customers in my market that happens to have my existing customers listed in it as well. How can I remove my existing customers out of this purchased list so that I can import it into my Database as Prospects? I'm using Excel 2010. Deleting duplicates doesnt work for this. I want a function that looks at data in one spreadsheetA and if it finds it in the second spreadsheetB, it deletes the row out of SpreadsheetB.
I need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.
Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.
The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.
Code: Sub Macro1() On Error Resume Next Range("A5:A29").Rows.Ungroup Range("A5:A29").Rows.Ungroup Range("A5:A29").Rows.Ungroup Range("A5:A29").Rows.Ungroup Range("A5:A29").Rows.Ungroup
I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying:
"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."
The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting
I have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:
Option Explicit Sub CreateTimeChartData() Dim vTimeData As Variant Dim i As Integer Dim sRoom As String Dim vLastEndTime As Variant Dim oSeries As Series ' set up Application. ScreenUpdating = False Application.DisplayAlerts = False ' create chart data worksheet With Worksheets("TimeData"). Range("TimeList"). CurrentRegion .Sort Key1:="Room", Key2:="Start Time", Header:=xlYes vTimeData = .Value Worksheets.Add On Error Resume Next Worksheets("ChartData").Delete..........................
i m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136
the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.
I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
I have a file with many different row sizes and after calculating the figure, we will need to add up the total for a particular job. Due to the irregular data on each job (some with only 1 line item where sometimes the job have few line items).
The cell to sum already has a formula for the data.
For example, I already have a formula on Column L to get the figures. After few line items, I will need to add a total to the lines item, also on Column L. I think we can uses 2 sets of data from 2 different column as "pointers", Column D and the formula column itself (but I don't know how to). The columns that I need a sum function will be from Column L to X. After doing the sum, can we at the same time draw a sum line? (single top line and double bottom line).
Am I being "too greedy" ? Imagine if we have more than thousands lines of such data to be added manually and to draw these lines manually.
need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014 28/1/2014 28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
I am trying to prepare a Worksheet that has an initial sheet which is a summary of all sheets, information on individuals and totals etc. This summary sheet will be added to over time. I would like it so that when I enter the details of a new individual on the summary sheet a new sheet is opened following a template with the details already filled in for that individual, and for the new sheet to be named with the individual's name. Ideally the new sheet would open automatically once the info has been entered, either that or a button to open the new sheet,
I have a couple of spreadsheets that requires me to copy data from Sheet 1 that matches certain criteria to Sheet 2.
I have tried using posted VB codes that have been written for other people but they dont seem to work (this is probably to do with me not actually knowing what I am doing, as I am not sure what I should be replacing and what I need to be deleting etc out of the pre-written code )
I need to be able to search on column e in Sheet 1 for anything that has just h written in the cell and then copy all the rows that match into Sheet 2. I also would like to ensure that when it is copied it doesnt go in row 2 but maybe 4 or 5 as I have quite allot of headings.
I would like to fill in the same conditional format down to 51 other rows but the formatting for each row should be based on the contents of its own cell i.e. $D$3, $D$4, $D$5 etc. How can I do this with code?
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
I have a table with 2 columns and I want to create another table with the same values in these 2 columns but add a new column with values that are different. So for example, I have col A, Col B with values 256 and 14. I want to create a table that has Col A, Col B and Col C with 256 in Col A appearing 60 times, 14 in column B appearing 60 times and new Col C has values ranging from -4 to 55 for each new row. So new table now has 60 rows with same values for Columns A,B but different C value
I can drag or copy paste but when I have 200 unique records in first table, I want to create a second one with 200x60 rows and drag/copy paste is tedious.
apply a count to a sheet which adjusts when filtered. I have attached a sheet, but as you can see the filter is returning the incorrect count as it is including the rows which are not displyed
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
