I have a worksheet that I use on a track and field day to enter times and distances and then can allot points based on performance. That is not the problem.
My question is: Is there a function or formula to then sort through the data and rank the participants in order of their finish in a different worksheet? I imagine I would somehow need to link the data to each name. I have attached a copy of the actual worksheet.
So, I have a table of scores from players for a category I have. It looks like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------Michael-----------Germany-------------364
2nd--------------Tim---------------USA-----------------364
4th---------------John--------------Norway-------------355
if one of the guys, for example, John were to improve his score to a 369, I'd change the score in Excel, then use the sort feature to put him above Michael and Tim. Only problem is, it would say his place is still 4th place, and it doesn't change to 2nd place.
I want it so the "Place" column automatically changes to the correct rank based on the score the person has. So if I changed John's 355 score to 369 and hit sort by A-Z, Excel would automatically change the "Place" he's in appropriately so it would look like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------John--------------Norway--------------369
3rd---------------Michael-----------Germany-------------364
3rd--------------Tim----------------USA-----------------364
I have a spreadsheet with a list of ID's in column A. In Column B I have a list of ratings. Each ID has more than one rating. For example there may be 8 rows with ID 1, 12 rows with ID 2, 6 rows with ID 3 etc......
I want to rank the ratings in Column B with highest being 1 but only when the ID's are the same. For example there would be 8 ratings ranked for ID 1, 12 for ID 2 etc.....
The problem is I am running a monte carlo simulation on the rating of around 10,000 simulations. Every simulation the rank for each row will of course change because the rating has changed. The spreadsheet has around 70,000 rows and over 3000 ID's.
How can I do this without either the computer becoming a crawl or freezing up and it not taking 30 minutes for each step of the simulation?
I have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
Example:
20
18
18
15
12
11
11
9
The ranking I am looking for is:
1
2
2
3
4
5
5
6
I am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
some times i choice (Initialize , active, open, click
Workbook_BeforeSave)
and so on
tell me as the differences and usage of these strings in order to improve our(my) programming skills.
I am trying to write a code where user has to respond to input box option depending on yes or no selection. There are two types of responses to different questions. One needs to respond between 1 and 100 (if yes) and for some other questions needs between 1 and 4 (if yes). A "no" will enter zeo value. But it is not working. I am using two named ranges "VALIDCELLS" (for 1 to 100) and "FREQVALIDCELLSS" (for 1 to 4). Here is the code;
View 6 Replies View RelatedI have early made a macro that will change some of the cells before printing and then changing back again to initional state after the print out.
http://www.excelforum.com/excel-misc...-printing.html
But now I have to do let people also print a copy a regular way, without the macro I made before. But if they go through the regular way iof printing I would like to insert at header in red color, to notify them that this is just a preview and not the way to print. And here we come to the problem ....
why am I unable to create events on my worksheets? They were usually available to me when I selected "Worksheet" on the dropdown in vba.
When I open Excel, only "General" comes up in the drop down menu. What am I missing?
I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).
I have created a class module with the following code (exactly as the Excel help):
Public WithEvents App As Application
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub
"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."
When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".
I have a spreadsheet,3 columns are shown in the attached.The first column gives the date of the event,the second column the time and the third a rating.I want to put the average rating for each event in each cell in column 4.
View 9 Replies View RelatedIn both instances I want to put a date stamp of the column immediately after the defined columns below.
The first issue is that when it runs the 1st change event, it only puts the date stamp in the top row of the column. The change in the "Inportedshapeall" column would almost always be brought about from range being copied from another column, however, I would still like a date stamp in every cell of the column.
The second issue comes about when I try to run the second change event. It simply seems to get stuck. I have tried Else, Else if, removing End ifs. I am not sure how best to define the events in order to ensure it loops through both.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("IMPORTEDSHAPEALL")) Is Nothing Then
With Target(1, 2)
[Code] ......
I am trying to add the total time of 643 events. I have the start time subtracted from the end time to give me each individual events total time, at the bottom I am trying to put a total time for all events but it keeps displaying 14 hours and 13 minutes even though it should be somewhere around 500 hours.
View 4 Replies View RelatedI'm trying to trap the events of a user form in a class of mine. At the top of my class I have the standard:
View 2 Replies View RelatedHow can I get EXCEL to display recurring events on one line? In the example below, I want all events of type PLAN to be on the same line.
Phase
Time
Duration
End
[Code].....
I have been working for 8-1/2 hours to get the focus to move to a specified control on my user form if criteria are met in two other controls on the same user form. It's not responding as I expect. I think I know what is happening; I just don't know why.
Situation: I have a user form with the following relevant fields in this tab order - LMonthDay, tbMonthDay, SBMonthDay, LYear, tbYear, SBYear, LTime, and tbTime. I used the designations L, tb, and SB in my control names to represent label, text box, and spin button respectively.
Problem: Once the SBMonthDay control has the focus, if I press TAB without changing the control's value, it does not execute the Exit event statement the way I anticipated. I want the focus to move to the tbTime control when the tbMonthDay & tbYear controls are already properly populated. (They will already be populated for 99% of the entries in this case.)
Private Sub SBMonthDay_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Debug.Print "SBMonthDay_Exit Begin"
If ISLIKE(tbMonthDay.text, "####") _
And ISLIKE(tbYear.text, "####") Then
tbTime.SetFocus
Else: tbYear.SetFocus
End If
Debug.Print "SBMonthDay_Exit End"
End Sub ...........................................
2 Woksheet Change Events? I have a question similar to one that was posted about 2 worksheet change events which I found out is not possible and so I have to merge the codes together and I don't know how. I'm facing a problem in the advanced filter when I remove the last item to be filtered and takes forever to calculate.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$F$2" Or _
Target.Address = "$F$3" Or _
Target.Address = "$F$4" Or _
Target.Address = "$F$5" Or _
Target.Address = "$F$6" Or _
Target.Address = "$F$7" Or _
Target.Address = "$F$8" Or _
Target.Address = "$F$9" Then
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
Exit Sub
End If
If Target.Column = 3 Then
If Target.Value = "YES" Then........................
I have a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" in a worksheetsheet code.
I would like to turn this procedure off while I run another routine and then turn it back on after the routine has run.
Can you create user-defined events in excel? I ask because the worksheet_change event does not capture pastes or undo/redo.
View 4 Replies View RelatedIs it possible, that while running code that the code can say initiate the click event on a command button on another sheet.
Say that I have a button on Sheet1 called "wkscmd_DisplayDEI"
Behind that button is obviously some code. I want to know is it possible that while some code is running ( code does not reside on the module page for Sheet1 that it can send a pseudo click to the button?
The boolean Cancel suddenly stopped working for me in all workbook and worksheet events. Consider a simple example:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub
does not cancel right click, like it has before. Same with BeforeDoubleClick. I can only assume it is some kind of a setting or similar, as there is absolutely no change in the code.
Is there a way to set up a cell selection event trigger through an addin when it is installed so that I can respond to selections made on user's sheets?
View 3 Replies View RelatedI have code the following code that is working great:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C2:C" & Rows.Count)) Is Nothing Then
Application.EnableEvents = False
If IsDate(Target) Then
Target.Offset(0, -1).Value = Month(Target)
ElseIf IsEmpty(Target) Then
Target.Offset(0, -1).ClearContents
End If
Application.EnableEvents = True
End If
End Sub
But I also need to create the same type of code that will copy the cell entry from column E and paste that data into column A along the data from column B that was created from the above code. This data is a sort of key for a vlookup formula on another worksheet. How do I "nest" this second bit of code into the original code?
event of one workbook from another workbook using VBA.
I am able to call the method using call <method name>. eg. call start_method.
But i want to know how to call a event using VBA.
What i exactly want is:
1) Based on some if condition in workbook1 , i want to call the workbook 2 (button123_click) event.So that its code will start executing.
2) I cannot do anything in workbook 2 , VBA code , as its a client file.
I receive input for events from three regions (East, Central, West) and have to match those against a calendar. Multiple events can happen at the same time. I want to be able to read the input into an area under the calendar dates, create a merged cell with those start-end dates (inclusive), then input the region and event (concatenated) into the merged cell. It would look like this (with example of the input data below the calendar), but over the course of a year or more. There can be as many as twelve events occurring simultaneously.
Example.xlsx
Input from the regions can happen sporadically, so it is a "living" spreadsheet that can change dynamically.
I'm pretty good with formulas, but this seems like it would need a VBA solution - which I'm not good at!
I have some code in the BeforeClose event of the workbook - it worked the first time I tried to shut down the wb, but never since. I opened a new wb and copied in the code and again, it worked first time, but not since. The code is just calling a function, nothing heavy. I've tried the Deactivate event as well. I'm more of an Access VBA kind of gal, so don't know if I'm missing something big here.
View 5 Replies View RelatedI am trying to have multiple change events work in one worksheet and can not get it to work at all.
The simple setup is, I have a column for each month of the year.
I have several rows of numbers beneath each monthly column.
What I want to do is if I enter a date in say January (my change field), I want it to "ClearContents" out all of the data in the August column.
This works fine currently.
But say the next month I enter a date in February, I want to "ClearContents" in the September column. And so on for each month.
My various attempts at this does not recognize the second change agent, so nothing occurs.
Should I try to "call" each section, or use "Else If", or some other idea?
I've tried attaching the worksheet in case that helps to see what I am attempting.
There are extra "items" on the side and below the table that will be removed if I get this "change event" working. So this worksheet is a rough draft so far.
How can i manage that my code in the Workbook_Open() event is run when i open that workbook with some code from an other workbook?
When i manually open that workbook, excel runs all events, including my Workbook_Open() event, but i need to open this one out of an event of an other workbook.
