I require is a macro that will check the heading title of each weeks data e.g. WK01, WK02 etc, work out what week it is and input the week number in a new column (Week No) corresponding to that weeks data.
View 2 RepliesEvery week I get sent a spreadsheet with the hours booked against specific codes, however, each weeks data is in a separate column (from weeks 1-52).
What I require is a macro that will check the heading title of each weeks data e.g. WK01, WK02 etc, work out what week it is and input the week number in a new column (Week No) corresponding to that weeks data and also total the time for that week and enter in the Total Hours column
Please see attached a sample spreadsheet for clarity.
Please also note that I have asked this question before (http://www.excelforum.com/excel-gene...o-columns.html) and DonkeyOte kindly supplied me with the code to work out the week number i.e.
=IF(COUNT($D2:$BC2),SUBSTITUTE(LOOKUP(9.99999999999999E+307,$D2:$BC2,$D$1:$BC$1),"Hrs WK",""),0)+0
however, as the weeks progress I have to manually copy the week number down and add the totals up so I believe a macro would be the best option as I am currently repeating the same task every week.
The macro needs to:-
(a) work out the week number and enter in ‘Week No’ column
(b) macro to copy each weeks data into Total Hours column
I would be most grateful for any assistance in this matter
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.
******** language="JavaScript" ************************************************************************>
Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
(A)boutA1=ABCD1This WeekLast WeekMissingAdded
21002103110311002310271032103210274103310331034104051035103410361055610371036Â
165571038Â Â Â 81040Â Â Â 91055Â Â Â 101655Â Â Â Sheet1Â [HtmlMaker 2.42]
I have to add up how many A's, S's, ST's and L's I have in a column how do I
do that?
Column 1Column 2
AA
A
AA
STA
AA
AA
AA
A
A
AA
AA
AA
AA
A
LL
AA
AA
A
AA
SS
SS
AA
I have a table with 36 numbers - 6 x 6 that means 6 rows and 6 columns. Numbers 1 – 45.
I want Excel to shuffle the numbers (random shuffle) so they will come in different combinations on each row and column BUT the numbers must not repeat (no duplicates) in the same row or column.
what I'm trying to do.
Background:
I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games.
What I would like to accomplish:
I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play.
I tried to make the question as clear as possible, if anyone needs clarification,
Originally Posted by shg
Welcome to Oz, Basca
spreadsheet with a representative sample of your data?
Games Played
Player 1 20
Player 2 7
Player 3 13
Player 4 20
Player 5 13
Player 6 13
Player 7 7
Player 8 7
Total Games Played20
I know the randbetween portion of my code will not work - but how can I make this work with VBA? I need four random numbers added after the 4 string characters.
For Each R In MyNewR
If IsEmpty(R) Then
R.Value = Right(R.Offset(0, -1), 4) & _
Application.WorksheetFunction.randbetween(1, 9)
End If
Next R
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
I am also using the following formula to return the date in Cell BE1:
=TODAY()
I am using the following formula to give me the ISO week number in Excel in Cell BE2:
=1+INT((BE1-DATE(YEAR(BE1+4-WEEKDAY(BE1,2)),1,5)+WEEKDAY(DATE(YEAR(BE1+4-WEEKDAY(BE1,2)),1,4),2))/7)
and the year in Cell BG2:
=YEAR(BE1)
I then use the following to give me the wk and year in this format "wk-yr", i.e "6-09" in Cell BF4.
=CONCATENATE(BE2,"-",RIGHT(BG2,2))
My question (finally) is that I am using this in a sheet that tracks issues that have deadlines. I manually enter in the dealine in the format "wk-yr". I have conditional formating that then colours a cell amber if it equals the current week and year, which is given in Cell BF4.
I want to use a traffic light system that will flag all issues in the weeks before the current week red, and the weeks after the current week in green, i.e. red = late!! I can't get my head round how I use a formual in teh conditional formating to correctly identify the relevent weeks? Any one any ideas? I could have the following situation for example (assuming that the current week is wk6 2009) and the formating should apply the colours as per my brackets:
10-08 (RED)
5-09 (RED)
6-09 (AMBER)
6-09 (AMBER)
7-09 (GREEN)
10-09 (GREEN)
6-09 (GREEN)
2-09 (RED)
How can one determine a week number reliably with date as an input?
I can imagine that the function needs some kind of input as well, what is the first day of the year...
I'm trying to write a macro for extreme value analysis. I need a macro which will do a number of things! the attachment should explain it.
View 2 Replies View RelatedI am trying to write a macro that will sort 2 columns of real numbers in ascending order, then merge them in ascending order into a new 3rd column.
I wanted to work backwards from a weeknumber (as defined in Excel). In other words, given a week number (i.e. 5) what is the first date and last date of that weeknumber. These are the formulas that I came up with and they work fine.
Formula: [Code] ......
Ffor 1st of the week and
Formula: [Code] .......
For the last of the week.
Attached File : Weeknumber.xlsx‎
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
sortbyDates.xlsx‎
I have to examine a random number of cases every week. I used to have a little app that had a spot to ask me how many random numbers I need. So lets say this week I need to examine 15 cases, I'd enter in 15. Then it would ask me a range for the random numbers. So, lets say there are 250 cases, so I'd type in 250. Then it would spit 15 unique random numbers in sequential order. So, then I could look at me list of 250 case and review those 15 that the random number generator spit out.
Well, we just upgraded our computers to Win 7 and that little app does not work anymore. I've been playing around in Excel, and I've gotten pretty close to getting something thrown together, but I can't get it to work just right...
I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?
View 2 Replies View RelatedI have a list of entrys (41 in total so far) from cell A1 down starting with the value 1. This entry represents the week ending 04/06/06. ie each entry represents a date for the week ending. So cell A2 will contain the no 2 and represent the week ending 11/06/06. Cell A3 will contain the no 3 and represent the week ending 18/06/06. It goes in order right down to number 41. I have a userform, what I want to do is have a combobox load all the entrys in as dates, not as the entry no. Then when I choose a week in the combobox it will load the appropriate entry no into another textbox? I dont want to add the dates to the spreadsheet.
View 4 Replies View RelatedIn one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.
View 9 Replies View RelatedI have a user form that has one combo box on it that right now references one column of data.
Now I am being asked to have three columns of data and the combo box to show one of the three when a certain criteria is met.
I believe it would be easiest to have an additional combox with a change event when the box is populated with "whatever" in the field
So S:3 to S:5 have A, B, C
And EC:1-EC:59, ED:1-ED:59, EE:1-EE:59 contain the data that should show when S:3, S:4, or S:5 is selected.
If S:3 is selected then the list in EC:1 - EC59 would show and so on.
I have recorded the following macro and wish to add unprotect (with password), run the macro - which formats the worksheet and then password protect all cells with the exception of range E15:H413 which I want to leave editable by users.
View 9 Replies View RelatedI have code in the NewSheet event of ThisWorkbook which tracks new sheets being added. But when a worksheet is added by copying an existing worksheet this event doesn't seem to be triggered. Buy logically a new sheet has been added to the workbook so the event should be triggered. Is this a design flaw or am I missing something?
View 7 Replies View RelatedI want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
I finally managed to create my very own macro calendar BUT it does not fulfill the function that I need it to neither do I no whether it is able to at all.
Basically, I have created a form that I distribute to Line Managers to complete and one of the fields is the date so the first problem with me macro is that it only appears on my PC and secondly I cannot seem to embed it in my workbook as a button. Is this possible?
Iam just trying to format an raw data(which has morethan 40,000 rows) which is converted from notepad to excel.
The major problem the token number which is below the name and quantity i need to bring the number to left side of the row.
i think this has not given an clear picture so iam attaching an sample sheet
I am trying make a macro required a password to run.
I found this code below but not sure how to use it. I would like to be able to run it from a command button.
Sub MPW()
Dim My Password
My Password = InputBox("Please enter password", "Password Prompt", "********")
'Hardcode password
If MyPassword = "password" Then
MsgBox "Access Granted", vbInformation, "Access"
'call macro
Exit Sub
Else
[code]...
Trying to write a macro so that my summary worksheet will auto populate when new sheets are added and filled out. I want the PO#, Quanity, Date ordered, Vendor, Subtotal, Tax and Total all to transfer from worksheets like F001 to the summary sheet. Yes there is only one F001 sheet filled out but that number will rise into the hundreds as the project progresses. No idea where to begin when writing this macro.
View 1 Replies View RelatedSo I'm trying to create a macro that will simplify dealing with a file we receive at work pretty often. I've enclosed a spreadsheet that shows a very basic example of the files we receive. What I need to happen is this: On the rows that have only the Company # in column A and Total $ in column D (ie. rows 5 and 6), I need the Total $ amount moved up one row and then for the row it was previously in to be deleted (at that point that row should only have the Company $ and no other data in it so it's basically useless). The problem I'm having is that if any rows are added to the file (for instance if an row was added between rows four and five) it would throw off my macro.
View 3 Replies View RelatedI am needing to write a line of VB code for a macro that will insert a VLookup formula into a cell where the "named" table_array can be a variable. Example of what I am looking at below.
Worksheets("active Worksheet").("active cell").Formula =VLOOKUP(G2,variable,6,False)"
I need it to be imputed in the active cell of the active sheet with the variable able to be gathered possibly from a cell reference. Say the cell c3 on the active sheet says V080606, the formula imputed would be =VLOOKUP(g2,v0806,6,false).