Uses Three Different Variable To Poll Up People's Names That Are Available For The Task
Dec 3, 2009
I've created a file that uses three different variable to poll up people's names that are available for the task. I've got it to work using long formulas, containing if, index, match, etc. It's working well, but I think that the spreedsheet will become difficult to maintain (delete or add people to). I'm looking for a way to simplify my code, so that it won't be as time consuming to edit.
I am trying to find the first and last names of people in a cell. I have a userform to get their first and last names separately. I am trying to run a VBA macro to determine if each cell in a column contains both first and last names, and then put a "Y" in an adjacent column. The Code I have is as follows:
I have 10 columns that are as follows. Name, rank 04, name, rank 05, name, rank 06, name, rank 07, name, rank 08. The longest name and rank list is over 1200 names long. What I need to do is create 1, 3, and 5 year averages based on rank.
My question is, is there a way to sort alphabetically that will automatically align column a,c,e,g,and i alphabetically that will also align each row by name.
example: name 04 05 06 joe 75 72 paul 82 carl 72 48
Joe isn't present in 05, Paul isn't present in 04 or 06, and Carl isn't present in 06.
Basically I need excel to add blank cells for people not on each years list so all names align.
i want to create an voting poll in Excel, where it will be uploaded in the class room, so that every student can access and vote their favorite stars. Voting category as follows:
a: Best Student b: Best Professor c: Best Staff
Create a userform and automize this.
1st Userform should contain a: Register / Roll No b: student Name to register themselves in the database.
and the 2nd userform should contain category a: Best Student b: Best Professor C: Best Staff
by selecting 1 by 1, 3rd userform should popup in which list of all student/professor/staff names will appear and student will select one name in each category. and the selected name will be updated in the excel sheet towards their register/roll no... Once regiestered sudent cannot register again.
In cells A4 to A54, their names are displayed. In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
The Board looks full of some interesting issues today.
I'm working on a workbook that hides and unhides a lot of columns depending on the month the user has selected. No problems here.
The sheets are named regionally, ie National 2008, California 2008 etc. The user is able to select the month and the year from separate combo boxes.
So when 2009 comes around I will need to rewrite many lines of code to point the VBA to the new worksheet names like National 2009, California 2009. My question is, is there a way to reference either variable worksheet names in the VBA or some other manner in which to write the code so I don't need to rewrite every year change?
I currently reference the sheets something like Sheets ("National 2008").Range("H:H").EntireColumn.Hidden=True
I am working on a macro for excel which has a userform where the user puts in the number of samples they have, then takes them to a new userform to input the weights of the samples. I want to be able to set the sample mass variable dynamically based on the number of samples they have…
i.e.
spl1 spl2 spl3 . . . splx
x=number of samples
This number will be different each time so I am stumped at how to do this since I have just started in vba and do not know too much yet.
Is there a way to use formula (Sum in this particular case) across multiple sheets with sheet names having a particular criteria?
The sheet names are 5 digits but not sequential (cost centre numbers). I'm trying to find the total if the sheet name (number) falls within a particular range (e.g. 150xx-152xx, 153xx-168xx), with around 60 or so sheets in total.
I know it's possible to specify the range myself, or to add a blank sheet as a marker/guide to create a "sandwich" of the required sheets (e.g. =SUM(Start:End!A1)), but the cost centre ranges may change if they're changed so I'm trying to find a way to identify them as variables rather than requiring maintenance.
Although the layout of the sheets should not change (and so a macro might work..?) I think I'd prefer to find a formula (if possible).
Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:
I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.
Is there a command line I can use, that will not prompt the user to save?
I want VBA to take an equation that has been entered into an worksheet cell as text and evaluate it. Here's a very simplified example that shows the concept:
Cell A1 may contain the text: "A * B + C"
I would like to do something on the order of this in VBA:
Sub Test() Dim A As Integer, B As Integer, C As Integer
Obviously, this doesn't work, but I want it to show a result of 14. The actual situation is much more complex, but it all comes down to being able to evaluate the text in a worksheet and recognize the variable names that are in it.
I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.
Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.
This is what I have so far
For J = 31 To 49 ResultSht = SheetJ.Name Sheet1.Activate ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select
It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?
I have data spanning many files which are named with the date on which they were created (so there are 31 files for August). For example: PL080107, PL080207, PL080307, PL080407...)
I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.
I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.
I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!
I have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.
I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.
The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)
Sub Copy_Visible_Sheets() Dim arr() As String Dim i As Integer Dim WB As Workbook Dim WS As Worksheet Set WB = ActiveWorkbook Application. ScreenUpdating = False
'Make sure template is saved as .xls If Not ThisWorkbook.Saved Then MsgBox "Please save this workbook before generating a Client Copy." Else
I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
I am working on a customer report template that generates our customers reports and will send them out automatically.
This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.
What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.
Here is a quick example of what data we get raw from the system:
Customer Name: John Build Johns Buildings Johns Ltd Building Johns Plumbing Department Glass Doors Ltd A Department of Johns Buildings Johns Building Corporation Hole In One Golf Range
This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.
You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.
So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code: Private Sub CommandButton1_Click() If TextBox1.Value = blank Then 'Need name for processing MsgBox ("Name must not be blank.") Exit Sub End If If Len(TextBox1.Value) > 12 Then
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12 2/12/12 6/4/12 18/12 24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
It runs fine for me, but when others (who are using Excel 2003) try to run it, it won't fire. The second macro entitled "Delete Foothill" runs fine though. "Run Foothill won't fire.
Sub Run_Foothill() Sheets("Foothill Plaza (Weekly Call)").Unprotect Password:="Foothill" ' ' Run_Foothill Macro ' ' Sheets("OC").Select Range("B37:C44").Select Selection.Copy Sheets("Foothill Plaza (Weekly Call)").Select Range("B4").Select ActiveSheet.Paste Range("A12").Select Application.CutCopyMode = False Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With
See attached.PMS 2.xlsx. I have a table with data on PMS sheet and would like to have something like the example on the Example sheet using data from the PMS sheet.
I have a list of people in column A and in B to E there are 4 numbers from 1 to 13. (Each person has 4 different numbers). In the next 24 columns are concatenations of these numbers in every order possible. On a separate sheet I have a list of concatenations of all possible combinations of numers 1 to 13 in groups of 4. e.g. 1234, 1235 etc. I have then counted how many times each of the concatnations from the first sheet appear. What I want to do is put the names of the people whoes concatentaions match the ones on the third sheet.
I am trying to do is create a document that will be available to sevaral people. The document will be edited by each person as needed. The completed document will then be used to order materials and supplies. What I want to hapen is each time the document is edited an alert is sent to the person responsible for the purchasing. I'm sure this is possible I just don't know how to do it.
They are voters for 3 separate occasions. I would like to filter out how many people have voted 2 times or 3. I have sorted the information with 2 columns "LASTNAME" and "FIRSTNAME"
I have a sheet in which it is separated by zone area, name (about 200 names), and email. I am trying to add people to an email based on zone. For instance:
Nevada Billy Billy@gmail.com California Sal Theemail@gmail.com New York Jim Jim@gmail.com Nevada JOe Joe@gmail.com Arizona Alex Alex@gmail.com
So how can add all the people in X zone to a single outlook email (in this case Billy and Joe)?
I have an excel workbook which is a result of a sports survey I conducted. It basically shows who likes which sport, what level they're at, preferred time of play, etc, and their email address.
A colleague has created a type of form (using formulas, not VBA) wherein you select your criteria (Sport, Gender, Level etc) and it displays the matching names.
What I'd like is to be able to select, via a check box or similar, 1 or more names from the list of results for that sport, click a button, and their addresses automatically open in a new Outlook mail window for the person to send a mail to those people.
how can you set up a workbook to display different sheets for different users depending upon what password they input to open the workbook? The manager should see all sheets but employees only data entry sheets.
I want to do is to count the number of people under each class, eg. 2300 - primary, 3 ppl, but i gt many many classes in my spreadsheet, do u all have solution for me using VBA
