My worksheet looks like this: column A - ID, B - Group #, C - data. When data has been entered for all the IDs in a group I would like a message box to appear stating end of group #.
View 4 RepliesI have some code which i would like to search a range of cells to see if a value is already used in this range. if it finds the value it will return a message box saying 'value found' and exit the sub, but when it doesn't find the value it will run extra code using "else" function. The code does what i want it to do, but it still returns the msgbox even if the the code in the "else" section runs.
View 2 Replies View RelatedI may be misunderstanding the point of error.type() command. I'm wanting an error message to appear once a cell goes below 0. So far, I've only managed to have the error message appear all the time, or never.
View 4 Replies View RelatedI have a range (A5:DG5). Some of these cells are empty, some are populated.
I would like to display a vbYesNo box that shows the value of each populated cell on a different line, with no gaps for non-populated cells.
I have a football pool I am doing with my family. I would like a macro that displays a message box that tells me the leaders of the pool using the grand total number. So in my attachment, the message box would say something like:
Sue is in first place with 12 points,
Bob and Dave are in second place with 9 points,
Larry is in third place with 3 points
It doesn't need to be exactly like that, but you get the gist of what I am looking for. The catch here is that the grand total row changes each week as I add games in, so the row moves down every week. I need the macro to stay with the grand total row from week to week.
want a way to find out any special character in text file (.txt).
The .txt file I use is very large about 100 mb to 1GB. I need to find a way to write vba code that asks for input text file and the validates it and gives the message that following special characters are present in the file. Also, it gives their column number and row no's, where they are located.
The characters which needs not treated as special characters are numbers (0-9), alpha (A-Z) and special characters (@,-,%,$,+,=).
I am using Windows 7 and have a piece of code that brings up a warning message after 20 minutes to warn the user they have been in a workbook for too long.
If they have the Excel application window minimized, is there a way to get Excel to take the application out of minimized mode into max screen, before revealing the message.
Currently when the warning message activates, the excel icon just flashes in the start bar. Some users may not see this.
Here is the code I have so far:
[Code] ....
I have already tried simply adding a piece of code with maximize screen, however this doesn't seem to work.
I have a list of data in a column that has blanks along the way.However, I don't just want to fill it up with the one above, or the one below.
If the data is in column B, I want to fill it such that where the row above has the same criteria in column A, column B for the above row is returned.Otherwise, column B in the row below should be filled in instead.I have set up example data below to illustrate.
Initial Data Set:
A
B
1
Criteria
Number
2
a
1
I'm simply inputting zeroes and ones down Column A. Basically, I either put a "0" or a "1" in A1, then A2, then A3, etc. However, I want to create a Macro that allows me to simply type in a string of 1's and 0's and it automatically takes each 1 and 0 and fills it in down the column. This website has the basic idea (Automatically Moving from Cell to Cell when Entering Data (Microsoft Excel)) but I need only one number per cell and for it to move down the column, not across the row. Here's what I have so far:
Sub SAMPLE()
Dim str As String
Dim x As Integer
Dim y As Integer
str = InputBox("Enter string")
y = 0
For x = 1 To Len(str) Step 4
ActiveCell.Offset(y, 0) = "'" & Mid(str, x, 4)
y = y + 1
Next
End Sub
*I can't get it to only break down into one number per cell down Column A. I think the Mid function or something needs to change.
I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?
Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long
i = 1
Do While i
Here's the outline of my problem...I'm building a directory of hedge fund contacts and need help in cleaning data and automating the entry of fields that will remain the same. I have one workbook with 2 pages... One is for the Companies and the other is for the contacts that work at those companies.
ex. Company #551 is 1794 Management - on this page we have fields for address, floor, city, state, zip, phone, fax and website.
on worksheet 2 is where the contacts are kept... the identifier is #551... on the contacts page all contacts under a specific company number will correspond to that company.
Here's where it gets tricky.... for the contacts the addresses are all formatted differently - first I'd like to sort by management co and address and have excel copy the first listed address format and copy this to the other contacts for that company.... when the formula reaches the next contact with company #552 it will look for the new first address and use this one for all that companies contacts.
after the formula finishes the fomating I'd like the sheet to become intuitive( dont know if this can be done but what I'm looking for is say when I add a new row and enter #551 as a company and enter the new first name, last name, and job title - if the excel sheet noticing that all contacts under that format have the same address, phone, fax, company name etc...
I am working with monsterous excel sheets (named Data) and need to a column that is governed by the ZIP. I have a sheet named Source in my excel folder with all the matching information next to the excel sheet. The column my zip codes in Data starts on J6 to J290 and the zip codes in Source are from A2 to A2671. The information I am trying to pull from Source to Data are names and branches located in Source from B2:E2671.
What function will auto fill the columns with the correct information. If it can only be done by putting a function into each column, that is not a problem
As you can see, I have 2 worksheets. I want that all entries in columns B,D,E and F automaticly fill in in multiple tables in sheet2. Also, if possible that macro creates tables (for example: if I have 100 rows, I want to extract 100 separetly values from column B,D,E and F and to enable automatic creation for new table - for 101 entry )
here is the dropbox link of the file:
HTML Code:Â
[URL]
So, I want when I click on the button I want to automaticly import all mentioned values into sheet 2 (I have marked with RED where to put values from which column). In this example, I expect, when I clik on Print all tables button, to print 7 tables in sheet2. In sheet2 I have 2 tables as you can see.
I have a peptide sequence that is 4500 amino acids long. I have the numbers 1 - 4500 in column A and the corresponding letter code for the amino acid in that position in column B. I have done a bunch of other calculations and isolated small series of amino acids within the entire sequence (example: positions 25-42, 153-166, 381-297, etc).
I would like to fill column C with either a blank space or, if it is in one of the selected series, the letter code again. So that the first 24 rows of column C would be blank but rows 25-42 would mirror the letter shown in B. Then blank spaces again until we reach row 153, etc.
I've used a vlookup to paste the letters with the chosen parts of the sequence, but now I want to space them out along the entire 4500 so that I can see if there are overlapping areas between several sets of criteria for choosing series (these would go in columns D, E, etc).
I'm attempting to make a simple userform that inputs data onto an existing worksheet. I have the userform but would like to use a combo box to choose a "category" item of data, however I dont know what the categories are! I would like the combo box to, somehow, look at the spreadsheet and read off the already entered categories and offer those as choices.
A picture is worth a thousand words:
http://www.copestake.org/images/excel.png
Is there a simple way to fill the combo box (using the form initialize I assume) with the existing categories?
[Edited to link to image instead of displaying on board~admin]
I have previously used an Excel file - created by someone else - which had pre-entered data. It was set up in such a way that if I typed a person's ID number into a cell, their name popped into the following cell. I know that the names and IDs were pre-entered somewhere else - in another sheet? In a file linked some other way?
What is this called? How do I do it? I want to end up with a file where I can type a department code into one column and have the department NAME pop up in the following column. I am mostly doing this as a time- saving measure when completing long lists of supplies being distributed to different departments (long story.) If anyone can even tell me what this is called, so I can look it up correctly, that would be... sublime.
I have a userform and on it is a combo box with the weeks in each month in it. A user has to select a week to enter figures for and then enter the figures into text boxes on the same form. When the click submit (command button), based on what week they've already selected, my code will put the figures into the spreadsheet in the appropriate place.
Sub december1()
Worksheets("Dec").Activate
If Figures1.TextBox1.Value >= "1" Then Range("c16") = Figures1.TextBox1.Value
If Figures1.TextBox2.Value >= "1" Then Range("c17") = Val(Figures1.TextBox2.Value) / 100#
If Figures1.TextBox3.Value >= "1" Then Range("c18") = Figures1.TextBox3.Value
If Figures1.TextBox4.Value >= "1" Then Range("c22") = Figures1.TextBox4.Value
If Figures1.TextBox5.Value >= "1" Then Range("c23") = Figures1.TextBox5.Value
If Figures1.TextBox6.Value >= "1" Then Range("c28") = Figures1.TextBox6.Value
If Figures1.TextBox7.Value >= "1" Then Range("c29") = Val(Figures1.TextBox7.Value) / 100#...........................
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
View 3 Replies View RelatedStarting to struggle a bit here. Trying to create a user form, with help from this website I was able to create 2 dropdown lists based on data validation and information from another sheet in the same workbook. I have one cell B7 that uses data validation and sheet "Eq List" to create a drop down list of departments that the user can select from. Once B7 is filled in, it creates the 2nd dropdown list in B9 so the user can pick "Equip Desc".
Where I am trying to fill B11 which should automatically give me back the Mach ID that is related to B7 & B9. Am I going about this the right way? I have ttached a sample file to make it easier to understand.
I got my invoice and my journal. Once I finish my invoices I send all to the journal, but I am having a little problem.... Here is my code
View 2 Replies View RelatedI'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
- Etc.;
- Etc.;
I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2
On the 2nd tab I have the shift schedule, which tell which shift is working on particular days
Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am
Feb
1
Shift A
Shift C
[Code] ........
Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.
I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time
I've set myself a project to try and automate some repetitive filling in of word documents and would like a point in the right direction. I've done some research on the MSDN and some sites on the web. The way I was thinking of doing it would be,
make a template with either Fields or Bookmarks (which would be best?) for each piece of data on the paper work.Put the repeated data into an excel sheet (with a button for the macro to be assigned to).
write some VB script that will, define and "label" the data in the XL sheetopen the word templateenter the data into each Field/BookmarkSave as a new file in a "New" folder (name of file and folder taken from the Data)open the next template and repeat.
would this be the best way of approaching this problem?Whats the best way of defining points in a word template, Fields, Bookmarks?what objects would I need to use to rename the folder?
The below formula is in Col F6, how do i get this formula to adjust to data range without manually filling down, my data changes everyday therefore i need the formula to auto fill according to data range.
=IF(ISBLANK(E6),"",Summary!$I$2-E6)
I have a worksheet that contains data on products including prices and I want to use that worksheet to fill out an invoice for every sale of those products. I know how to make the list/table dynamic and varify the data to "list", then use VLOOKUP to get the price to appear, but what I'd like to know is how to overcome the problem that will arise when a product's price has to change. Eg. if on 1/1/10 the price of product A rises from £15 to £20, how do I ensure that 2009 invoices do not change when I update/edit the price, but that the current price goes up? So far, the only thing I can think of doing is to create another product, but there are several other bits of data about each product on the worksheet which will not change (weight, height, width, supplier etc), so I don't really want to have to duplicate the unchanging bits and also leaving the productA with the old price in the list may lead to accidentally selecting the old price. What do you think? Is there a way of maybe fixing the price to date or something?
View 9 Replies View RelatedI can't seem to find the answer to this question. I have a sorted list of data 3 cells wide, 120 cells long. I wish to have this data put into a scroll bar so I may scroll the data, select, and it will output to a chosen cell.
I assume the ScrollBar control is the choice but since I have no experience with any controls, I am at a loss. I have thus far only managed to insert the scrollbar, click on the properties and then I'm ?.
1. How do I specify the range of cells to display?
2. How do I specify an output cell after selecting from the list?
I've attached a spreadsheet to view the data.
is there any way to NOT Displaying Items with No Data when filtering data in my pivot table?
For instance, I have 2 report filters: Category and Subcategory, when I select a category in the first filter I want to see only the options of subcategories with data in the second filter, I mean display only the subcategories of the Category previously filter.
Same scenario I have with a report with Directors and Organizations, when I filter one Director it would be nice to see only the organizations of this director and not all options on the data.
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
I have an excel sheet which contains details of quantities supplied of a particular party by an agent! It ranges from a period of April to March In Some places the agent has supplied a party's material only from april to december and jan,feb , march is supplied by a different agent for the same party.
I want to create a report which is as follows: When i choose an agents name, It should show me all the parties to which he has supplied the material and all the months from april to march. If he has not supplied in a particular month then show zero. Same should be for all the agents.
I need to create this report and i was trying with pivot tables but that didnt work!
I guess it can be done using vba code!
I have attached a sample file : Data Filtering VBA.xlsx‎
I have been using a lot of sumproducting lately with multiple conditions to extract data. Lately I have noticed that though it is a good way to extract data there is a lot of calculation time involved in it. The Excel workbooks that i make are in a database like format where there is 1 sheet usually a data dump which has data from one column to the 200th column and rows being filled with data points till the 10000th row. Data headers in the columns are usually like Date(ColumnA), Tenure(ColumnB), Person, Type, etc and then from Column Z onwards there are columns which contain Data in the form of numbers like Number of cases, Number of this and number of that.
Now usually when creating a dashboard of this data for performance management I use the sumpoduct formula to retrieve data. It normally has conditions in it like for some given date ranges, Tenure ranges, People ranges extract x data for me. For Example something like this
=SUMPRODUCT((Sheet2!$A$4:$A$4898>=VALUE($E$3))*(Sheet2!$A$4:$A$4898