I need a simple macro to use for conditional autofill of cells below the active cell (to the end of the sheet) with the text that is being entered in the active cell.
DESCRIPTION: I have two columns of data, the first (call it Col1), has a word number which is unique to each specific word in a foreign language, every cell in this column has a number in it from 1 to 30000. Each number reoccurs many times in cells over the length of this column.
The other column (call it Col2) is blank. As I type words in english in each cell of this column, I need the macro to autofill the text that I have just entered, into every cell in Col2 where it's Col1 number is a match with the Col1 number where I am typing.
For example, in one row, Col1 has the number "21" and I type in Col2 the word "run". I need the macro to find every row where the number 21 is found in Col1 and automatically enter "run" into Col2 at that location.
The macro must only autofill cells which have no data in them. Preferably from that cell down, although if that is not possible, all cells would work seeing we are only replacing blank cells with data.
I have two or more fields of data, where parts of them are auto populated based on inputs from elsewhere. That part i have working fine, however the problem arises in taking that information and displaying it as a Bill of Materials list on another tab in my excel file. I want to take the information, and only display it in the BOM tab if that piece of information is being used (ie item count field not being 0). I know how to do that part in a rather simple format, however i'd prefer to make it so that when i have the list, and a certain item isn't used then its not included in the BOM, and an empty space is not left behind. I would prefer this to be all be done automatically by a formula if possible instead of just having to resort to sorting the table every time the tool is being used.
Is this even possible in excel, A quick example doc is attached, shows 2 example fields. i'd like to take the information from those 2 fields. and somehow create a single list, with a single heading along the top, with all row containing a 0 "Unit Count" to be omitted entirely from the list, and not have a blank space where the item should be. Keep in mind these fields may be greater than 2, and may not be position one after the other in a strait down line.
I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
I want a Macro to be able to copy a formula down from cell A2 down the entire column within the data range. But, the problem I've got is that the spreadsheets I'll be using the Macro on will be different sizes. Currently, the VBA looks like this:
So the "Destination:=Range("A2:A17340")" part copies the formula into all cells from A2 to A17340. Other spreadsheets might only have data up to cell A200, or Awhatever - is there a way of getting the macro to populate just the cells in column A but only where there is data adjacent in other columns?
Also, I want the Macro to be able to delete all rows wherever "DELETE" appears in a certain column - I had a look at the "Delete Entire Row Based on Criteria" Macro but I'm looking for a fully automated solution, rather than the question boxes coming up and asking which cell/criteria, I need to run this macro on multiple sheets and the criteria/column position will always be the same -
Is there anyone who can suggest a solution to my problem below ? Basically, what I need is a macro which would ideally work like this:
if D32 = 1, then the background colour of D4 should be red if D32 = 2, then the background colour of D4 should be orange if D32 = 3, then the background colour of D4 should be yellow if D32 = 4, then the background colour of D4 should be green
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.
For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date ------------------------------------------------------------- John , 100 , $150 , 2011-04-01 17:07:50.0 John , 100 , $250 , 2011-04-01 17:07:50.0 Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase ----------------------------------------------------------- John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
I have a Pivot table that pulls the Avg of two fields for two months, see example below.
Avg Gross $ Avg Net $ Jan 2014 20 10 Feb 2014 30 20
sample 1.png See sample attached.
The Avg Gross and Net is shown by going into the values and selecting "Summarized value by -> Average".
On the right side of this Pivot, what I wanted to do is to show a Avg Gross to Net $ in this pivot. So the formula should take "Avg Gross $" - "Avg Net $" = Avg Gross to Net $.
I am having trouble calculting this new field in the pivot table using a calculated field because the Calculated field pulls the variables from the existing field list and there isn't a field called "Avg Gross/Net"....I need to find a way to calculate the Avg Gross to Net into the Pivot table so I can pull a pivot graph out of it.
In the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.
I use a system which produces excel spreadsheets similar to this example (although much larger than this!). This makes it extremely difficult to carry out sorts and to tidy up the spreadsheet - generally I have to do this manually. I am hoping that there is a way that I can easily add in the data as per document 2 in red.
Is it possible to fill an embedded pdf (AcroPDF) fields with data from excel?
For example: I have an embedded pdf file, it has a field name "NAMEFIELD", on the click of a commandbutton, I would like Sheets("Sheet1").Range("A1").Value to be input into the PDF file text field "NAMEFIELD".
I am having a really difficult time with this code. I am trying to fill in some fields on an webpage in an effort to eventually retrieve some data. I have read lots of posts on the topic and somehow I'm still confused. My code crashes on this line and gives an Automation error, Unspecified Error message: With appIE.document.forms(0). No matter what I try, I can't get past this line. I have also tried: With appIE.document.all
Sub PropInfo() Dim appIE As SHDocVw.InternetExplorer Set appIE = CreateObject("INTERNETEXPLORER.APPLICATION") appIE.Visible = True appIE.navigate "http://gisims2.miamidade.gov/MyHome/proptext.asp" Do While appIE.Busy: DoEvents: Loop Do While appIE.readyState <> complete: DoEvents: Loop
With appIE.document.all .Item("cmd").Value = "FINDADDR" .Item("cmdTemp").Value = "FINDADDR" .Item("searchtool").Value = "ADDR" 'Search by' dropdown .Item("stnum").Value = "2417" 'house #' field .Item("stdir").Value = "" .Item("stname").Value = "ponce de leon" 'street name' field .Item("sttype").Value = "BLVD" 'street type' field .submit End With End Sub
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
I am currently building a sheet that requires a drop down box that will auto-populate two separate fields. I have tried to the point of being suicidal to make this work but to no avail.
A detailed explanation:
The worksheet containing the drop down list and fields that need to auto-populate. sheet example.png
The raw data, located on a separate tab in the same workbook. (Note: there are over 1500 rows of data) sheet example 2.png
The drop down box will be in the "Description" column and will contain a list of services my company provides. Once selected it will auto-populate the "Rate" column and the "Item ID" column. The raw data that the list is pulling from is located on a separate tab.
I can make the list just fine, it's easy enough to just go to the Data Validation feature and point to where you want the list and where you want the data to come from, but getting the list to pull the data from different columns and go into the columns on the work sheet is apparently beyond my knowledge of excel. I figured it would just be a simple formula setup through the VLOOKUP formula wizard but I've tried it at least 20 different ways, all of which fail. I've tried HLOOKUP too but it also failed.
I have the catalog #s of some products in column A, and I have the brand in column B, Product name in Column C, and the model # in Column D. I want to create a formula that if I select the catalog # it should autopopulate the brand in column B, product name in column C and the model # in column D!!
And I have the brand/product & model # in a different sheet!!
i have two fields with dates - one field A1 for date authorised (for a data request) and one field B1 for date actioned (data request)
i need a formula to populate in C1 the following:
if A1 is blank then C1 is 'not actioned' if B1 is blank then C1 is 'not complete' if both contain dates then C1 to calculate the number of working days between the dates eg. A1 10.08.09, B1 11.08.09...C1 = 1 working day