VBA To Autofill Formula Over Column Data Calculate And Replace Original Data
Nov 14, 2008
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
I have the macro to insert a formula and copy it in a range("A2:A14", but I want the end of the range in column A to be the last row with data in column B. How to add that in?
Sub vlookup() Range("A2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[1],'[Old.xlsx]Total Student Count Data'!R2C2:R1000C2,1,FALSE)" Range("A2").Select Selection.autofill Destination:=Range("A2:A14"), Type:=xlFillDefault Range("A2:A14").Select End Sub
I'm trying to autofill data in a column from one sheet to another in the same workbook. My goal is to be able to paste data in one sheet and have it appear in another sheet.
Here is my test example I am trying to figure this out with.
Sub a() Dim rg As Range 'set formulas to be filled Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF('sheet2'!RC="""","""",'sheet2'!RC)" Selection.AutoFill Destination:=Range("A1:L1"), Type:=xlFillDefault 'now get autofill to populate until no more data on sheet 2 Set rg = [a2] Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)) rg.Cells(2, 2).AutoFill Destination:=rg, Type:=xlFillDefault End Sub
Without VBA, once I set the formulas for A1:L1, I can use the mouse to autofill all columns until Im around row 27,000.
Id like to be able to run a check for the end of data in sheet 2 so that if the sheet2 changes, it will all be included.
The resulting sheet1 cannot have any null values, so a perfect end of data check is necessary.
I want to Autofill a Column C to repeat a set of data in Column A, if Column B contains an integer, as shown below. Being able to Autofill this data allows me to have a dynamic table for use on multiple projects that may have a different amount of rows in Column B.
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
As you should be able to see in the attachment I have 3 columns
The first column is the data I wont to search and replace.
What I want it to do is for every value in A, loop through B and if it finds the Value in A matches that of B then replace the value in A with the value that is in C.
So basically B is like a reference column.
I hope this makes sense. My algorythm is something like this I guess.
I was wondering if it is possible to perform a find and replace which would replace data from another column. We have a lot of data fields of our inventory that we exported which we are trying to condense for another program.
Example: I have a description column (AV) that has [[Manufacturer]] within the description. We want to find [[Manufacturer]] and replace with our data column (DD).
This is the data that has our manufacturers name in it. We have several find and replaces to do within the description column besides the above field.
Need to sort my data in column A which contains a long list of numerical data. To do this manually using copy and paste would be easy, but not viable because of the large amount of data.
The attached PDF file illustrates the data. I wish to replace each date of dirth entry in column A (one D.O.B. in each cell / row, expressed as YYYYMMDD) with the client identifier number (format 937XXXX) a couple of cells above. And at the same time delete the row containing the client reference (or its contents). The data in column A should retain the same relationship to the data in the other columns (B,C). it may require a short piece of VBA code.
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,
My main data tab is collecting gallons pumped for a particular piece of equipment. I have a drop down box to populate the piece of equipment and VLookup to identify the unit number associated with the equipment. I would like to create a separate tab for each piece of equipment that will track the number of gallons pumped during the calendar year. Here is the format for the main tab:
DateBeginning Meter ReadEnding Meter Read GallonsEquipment IDEquipment Description
1/2/2014565443565625 18212006 ford f250 1/10/2014565625565675 5022006 Chevy 2500 1/11/2014565675565750 754Ford Taurus 1/12/2014565750565830 8012006 ford f250 1/13/2014565830565900 7012006 ford f250 1/14/2014565900566000 10012006 ford f250 1/15/2014566000566125 12512006 ford f250 1/16/2014566125566215 9012006 ford f250
The formula I used in Cell A4 is =INDEX(Gasoline!A4:A23,MATCH(1,Gasoline!F4:F19,FALSE),1)
Cells A4 and B4 have the correct values. My problem comes in A5,A6 and B5,B6. These should not have a result because they are different pieces of equipment. Throughout the year, each piece of equipment is going be used on the main tab. How do I keep the individual tabs from picking up the same entry multiple times.
The code below inserts a formula into E2, copies it across to column O and then down to the last row. It works fine but if more columns are added obviously it would need to be changed to go up to column P for example.
How can I change this so that the formula will be copied across to the last column automatically and therefore the code will not need to be changed?
Code: Dim LR As Long LR = Range("A" & Rows.Count).End(xlUp).Row Range("E2").Select
East Item # Cases in East Dropped 1 Pending Wave 1 - Qty Dropped 2 Wave - 2 Qty Dropped 3
[Code]...
I have above data where in I need to formula to calculate the 428 in dropped 1 should get calculated in Dropped 2 & dropped 3 as per the plan in wave 1 & wave 2
I have a tracking log, stored on a network drive, that tracks open purchase orders on about 13 different buyers. Each buyer has access to this data, and would be interested in looking at it in different ways. I have an idea for protecting my original data in this workbook, but allowing others to view and edit the data anyway they want. I thought I'd run it past the pros first, to see if they knew a better way, before I started trying to code it.
My idea was to put a macro in the workbook open event that opens an input box, "Enter password or select okay." If they enter the correct password, the macro exits, and they are in the workbook. Any changes made after entering the correct password will be made to the original data. If they enter anything OTHER than the correct password, the code would save the workbook AS another name, so now any changes made would be only made to a COPY of the original data.
This way, when I do my daily updates, I can open the file using the password, and save the file after editing. Anybody else would only get to edit a copy of my original. The other question is..., is it possible to put a macro in the workbook exit event that requires a password to overwrite the original? Otherwise, the user could edit the data in the "saved as" version, then just overwrite the original file, either accidentally or maliciously. I know they could still overwrite it by editing the macro, but I don't think they're that savvy.
I have a range b2:g37 filled with numbers. I use max function to get the max number in this range in cell C41. For example, the number is 20400. Now I want to know where the 20400 is originally located in the spreadsheet (ie. in column B, C, D, ...? in row 2, 3, 4, ...?).
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
1, How do I calculate the original value of something if it has dropped say 9.3% Ex Stock value 5.56 down 7.4%. How do I calculate its original value.
2, I have copied some numerical data from a website, on one computer when I drop that data into an excel sheet it falls into separate cells allowing me to manipulate it, on another computer I drop it into an excel spreadsheet and all the data goes into one cell not allowing me to manipulate it.
I was wondering is there a setting on one excel spreadsheet that I am inadvertantly not using on the other.
I need a method of updating a set of data held on tab1 from tab2 where details of one record is shown using VLOOKUP, possibly via a macro? For example:
Sheet 1 holds info on various companies - (Column headers: Name, address1, address2, postcode, " DATE LETTER SENT")
Sheet 2 Uses a drop down menu to select the desired company and some VLOOKUP formulas show the info from sheet1 on that one particular company. Sheet 3 There is a printable standard template letter which draws info from the record selected on sheet 2 (using standard =Sheet1!A1 formula) formated so the letter is addressed to the specific company selected. Back to: Sheet 2 - At the bottom there is a button which says 'Print' This runs a macro which selects sheet3, prints it then returns to sheet2. I need this macro to also go back to sheet1 and update the relevant record with ideally todays date (the date the letter was printed) in the "DATE LETTER SENT" column mentioned before but can just be an 'X' to show a letter has been printed for this company/record at some point. So in the future, by looking at sheet1 you can easil determine which companys have had letters printed against, and which havn't.
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah Carl Carlisle-------CD---------PTA------3----------------------------------------------0 Carl Carlisle-------AB---------SELF-----2----------------------------------------------0 Donny Delta-------DE---------PT--------1---------------------------------------------0 Ernie Elephant-----EF---------PTA-------2---------------------------------------------0 Alice Alpha-----------------------------------------------1 Carl Carlisle--------------------------------------1--------------1---------------1 Ernie Elephant-------------------------------------------1---------------1
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3 Carl Carlisle-------AB---------SELF-----2----------------------------------------------0 Donny Delta-------DE---------PT--------1---------------------------------------------0 Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
Instead of Excel creating a new sheet when I double click on the results of a pivot table, is it possible to just filter the results of the original source data's sheet?
Here is my situation, I have source data that needs to be updated after someone double clicks on the pivot table data. They won't be able to update the source data if it just creates a new sheet (since it's a copy).
I run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
