|
935 posted:I think this is an easy question. Say I have a workbook and on sheet1 I have a list of students. You would start by adding a column on sheet 1 to define which desk that student sits at. My code assumes you just put the desk number in column F (i.e. not Desk 1, just: 1) Press Alt+F11 to open the Visual Basic editor. On the left under VBAProject, double click on "Sheet2 (Sheet2)" to open a white page. Paste this code: code:
|
# ? Feb 11, 2012 18:11 |
|
|
# ? May 11, 2024 13:56 |
I have a list of birthdays that are in columns like this What would be a formula I can use in another column to turn this data into YYYY-MM-DD format? The leading zeros would be necessary.
|
|
# ? Feb 17, 2012 22:03 |
|
az jan jananam posted:I have a list of birthdays that are in columns like this Check the help for Text and Date functions. You'd use Date to turn H,I and J into a valid date format, then Text to format it how you want.
|
# ? Feb 17, 2012 22:30 |
|
Cancer Wad posted:Still working on this pricing table (original summary of project) and learning a ton about Excel and enjoying wading through this stuff. Couple of (hopefully) straightforward questions: I'm not 100% on what you are getting at but yes, you can use named ranges in VBA. I think you probably have access to ThisWorkbook.Range("NamedRangeName") and there is also the .Names collection where you might be able to use .Names("NamedRangeName") however that may be more about definition of named ranges than using the values. I am currently installing Excel so I can't say for sure.
|
# ? Feb 18, 2012 10:57 |
gwar3k1 posted:You would start by I forgot to come back and tell you how perfectly this worked. Even better, it introduced me to VBA that until now I only had a passing familiarity with. Thanks a lot! I know Excel isn't ideal for floorplans but I can think of a few situations where this code could be applied.
|
|
# ? Feb 18, 2012 17:07 |
|
A coworker asked me to help her fix a spreadsheet she updates which was created by her predecessor. Looking at it I saw some strange syntax in the formulas that I do not understand and Google was not very helpful.code:
|
# ? Feb 22, 2012 15:36 |
|
Old James posted:A coworker asked me to help her fix a spreadsheet she updates which was created by her predecessor. Looking at it I saw some strange syntax in the formulas that I do not understand and Google was not very helpful. The stuff inside each parentheses evaluates to TRUE or FALSE. When you put a -- in front of it it multiplies by -1 twice, which turns the TRUE and FALSE into 1 and 0 respectively, which is the necessary syntax for the sumproduct() function. There are other ways to do the same thing but -- works.
|
# ? Feb 22, 2012 15:50 |
|
esquilax posted:The stuff inside each parentheses evaluates to TRUE or FALSE. When you put a -- in front of it it multiplies by -1 twice, which turns the TRUE and FALSE into 1 and 0 respectively, which is the necessary syntax for the sumproduct() function. There are other ways to do the same thing but -- works. Thanks, that makes some sense. Now that excel has countifs() I think I will stick with that instead, but good to know in case I run into it again.
|
# ? Feb 22, 2012 15:57 |
|
Short question: does anyone have a good resource for learning how to put if then statements into macros, specifically looking for certain strings in a cell and deleting that row? I am building a vba macro to reformat a .csv dump from our internal project tracking system into a more user friendly format. Its all very simple formatting stuff until this part. I have zero VBA experience and no idea how to get logic in there. I looked for tutorials online but they all seem too high level for what I'm trying to do.
|
# ? Feb 22, 2012 22:56 |
|
Xguard86 posted:Short question: does anyone have a good resource for learning how to put if then statements into macros, specifically looking for certain strings in a cell and deleting that row? http://www.techonthenet.com/excel/formulas/if_then.php This should do what you want. Just change the range("A1") to the actual column with the cells you are looking and and change "xyz" to the criteria. code:
|
# ? Feb 22, 2012 23:04 |
|
sweet thanks, that looks like it will do the trick. It iterates through the whole column starting at A, right? So I just need (whatever column)1 and it will step through until it hits a blank cell? Second newbie question, can I just do the same for loop with a different column and string but not change variables for other fields? Xguard86 fucked around with this message at 19:42 on Feb 23, 2012 |
# ? Feb 23, 2012 18:37 |
|
Xguard86 posted:sweet thanks, that looks like it will do the trick. It runs till it reaches the last used row on the spreadsheet. So if column A stops at row 5, but column D has 200 rows it will continue to run through 150 blank cells in column A. Xguard86 posted:Second newbie question, can I just do the same for loop with a different column and string but not change variables for other fields? Yes, change the range("A1") lines to reference the column you want to check and change "xyz" to your search sting (this is case sensitive). This same process can be done with the .Find and .Findnext properties, which can ignore case and will run faster. However, if you are looking to teach yourself VBA wait on that until you feel a little more comfortable.
|
# ? Feb 24, 2012 15:28 |
|
Here's what I've got, three loops on the same column but different strings. Is there any low hanging fruit style things I can do to optimize this? Its only 1200 or so rows so it can run like a dog and not matter, but I like doing things right.code:
|
# ? Feb 24, 2012 17:56 |
|
You can do all 3 checks on the same pass through the text like so.code:
|
# ? Feb 24, 2012 18:26 |
|
Excellent, I knew I could close that up. ...and another stumbling block. My most recent code, I've tried many different versions: code:
Xguard86 fucked around with this message at 18:36 on Feb 24, 2012 |
# ? Feb 24, 2012 18:33 |
|
Looks like you are using older syntax to sort. The way you are doing it limits you to 3 keys, but it is compatible with older versions of Excel. You need to include the entire range in your sort, not just A2. Also, you were missing the order for each key otherwise Excel doesn't know if you want A->Z or Z->A. Finally, you are limited to the options xlYes/xlNo/xlGuess for the header. code:
code:
code:
Old James fucked around with this message at 20:16 on Feb 24, 2012 |
# ? Feb 24, 2012 19:26 |
|
Alright almost got this done, you've been a great help Old James. One more question: I want to insert a new row and then number it sequentially(so row 1 has cell A1 with a '1' row two's A2 has a '2' etc.) I can do the insert and I've figured out how to get it to count sequentially but I only want it to assign a number to rows with occupied cells, not just count down until it hits the page limit. I'm guessing some kind of 'If' statement will do it, but I don't know how to write it where it will check for anything at all in the adjacent cell.
|
# ? Feb 24, 2012 22:36 |
|
It sounds like you want to number the rows instead of actually inserting new blank rows. So...code:
|
# ? Feb 25, 2012 00:02 |
|
running that returns:quote:Unable to get the large property of the WorksheetFunction class I'm not sure why, I've been googling around but I don't see anything to explain why it would error out. Maybe something to do with my insert for column A? code:
|
# ? Feb 25, 2012 01:19 |
|
It looks like the error is occurring when it tries to find the largest existing value in column A while column A is blank. Which is odd, because I told it if the large function returned an error to make the value 1. The version above would have picked up numbering after any existing value in column A. But the version below is simpler and just starts at 1.code:
|
# ? Feb 27, 2012 15:51 |
|
I'm trying to generate a number of reports for our customers at work. Right now I have a list of 81 customers. I also have a workbook which has 81 sheets that each have a blank sales template in them. What I need to do is place one name from the list of customers into cell A1 of each of the 81 sheets which will then use a VLookup to generate the sales data in each sheet. At the end I will then have a workbook that has an individual sheet for each customer. As a bonus it would also be good if it could also rename the sheet to whatever the customers name is. I'm pretty bad at macros so I'm not too sure how to automate this process rather than doing it individually but I dont think its too hard to write up.
|
# ? Mar 13, 2012 06:45 |
|
Do you need to add the VLookup using code as well? Excel has a macro recorder which you might be interested in to learn how macros are constructed for the tasks you want. For example, you could record a macro where you rename a sheet and enter a value in to cell A1 of that sheet. You use the code generated and put it into a loop and add any extra code you need to perform. Assuming you use a list thats comma delimited ("Steve, Graham, Ken,...") then this will do what you need, or is at least a starting point if you need to add any more code in. Untested. code:
|
# ? Mar 13, 2012 19:55 |
|
The Vlookup stuff is already in there. Thanks for that. I'll give it a go now.
|
# ? Mar 14, 2012 01:12 |
|
When saving a file as .csv, why does it ask if you want to save the changes to file you just saved? For example, I open a new workbook and populate a worksheet. I save it as csv. First, it tells me the format doesn't accept multiple sheets. I say ok. Next it tells me some features may not be compatible with csv. I say ok. Then, after the file has saved and I try to close the window, I get "Do you want to save the changes you made to [filename]?" I always click no, because otherwise it leads to a loop, and I've confirmed the changes have been made after saving. Just curious.
|
# ? Mar 14, 2012 20:47 |
|
I have noticed this but it has worn me down enough to always Ctrl-S, close, 'No'. It would be interesting to find out why. I might investigate tomorrow.
|
# ? Mar 14, 2012 22:57 |
|
Old James posted:It looks like the error is occurring when it tries to find the largest existing value in column A while column A is blank. Which is odd, because I told it if the large function returned an error to make the value 1. The version above would have picked up numbering after any existing value in column A. But the version below is simpler and just starts at 1. I know its been like 3 weeks but thanks, I actually ended up with pretty much that after playing with it over the weekend. VBA seems so simple and then things just... don't work. Very frustrating.
|
# ? Mar 14, 2012 23:11 |
|
Xguard86 posted:I know its been like 3 weeks but thanks, I actually ended up with pretty much that after playing with it over the weekend. VBA seems so simple and then things just... don't work. Very frustrating. You're welcome.
|
# ? Mar 15, 2012 14:26 |
|
I'm really bored, and just playing around with Excel. My current goal is to make out a spreadsheet for the NHL, where I have every team listed. I have 82 columns wide, 1 for each game. At the beginning of the season, it just says their opponent, and I type in the result of the game for that team in the field: W, L, SOW, SOL, OTW, OTL I already have the code set up so that my standings page links to the "results" page, and the Wins, Losses, Overtime Losses, Points, ROW, and Games Played columns all update themselves. I don't know how to do macros. I did use the sort function to sort the divisions by points, highest first. Second criteria is games played, with lowest games played being at the top, and the 3rd criteria being ROW, with the highest. So it sorts out just like the standings are supposed to. Here is where I have some quesitons: 1) Is there a way to set it up to auto sort, so when I enter in a change in the "results" tab, it will automatically update the standings tab, without me having to manually go in and tell it to sort itself(it saves the sorting criteria, but I would like it to update any time a value is changed). 2) NHL is a bit different than other sports, in that each division winner is ranked 1/2/3 between the 3 division winners, then 4-8 for playoff spots are ranked by points between everyone else in the conference. I'm trying to have it rank each division based on the sorted criteria, so I can link from the divisions over to the conference standings I want to set up. I hope this all makes sense. Basically, auto sort and a way to rank using more than 1 criteria. I can't use just points as the rank because of the other criteria involved(Games Played and Regulation/Overtime wins).
|
# ? Mar 17, 2012 01:23 |
|
Since your standings tab probably has fixed ranges that don't change, you'll be able to get by this way: 1) Open the VBA editor and double-click the "results" tab in the tree nav on the left. 2) There are two drop-down boxes above the right pane. On the left, select "Worksheet". On the right, select "Change". 3) This will create two Subroutines. Delete the one called Worksheet_SelectionChange. In the other (Worksheet_Change) enter this code (modifying for your purposes: code:
code:
|
# ? Mar 17, 2012 04:12 |
|
Let's pretend that I contract with various authors to write short articles for a weekly magazine. My bookkeeper has been great about creating electronic copies of our contracts and maintaining a spreadsheet detailing basic information. The spreadsheet consists of three columns titled: Author, Subject, Contract Number The same author may have drafted multiple articles on the same subject but has a unique contract number for each of those projects. The contract number itself consists of "#####-####" such as "12345-6789". Unfortunately, the bookkeeper failed to have a consistent method of naming the electronic copies of the contracts but each filename does have the contract number in it. For example: 02-20-2010_Smith_Puppies - Contract_12345-9989.pdf Feb_20_2010 - John Smith - Puppies - Contract 12300-5555.pdf John Smith - Cats - 12299-2211.pdf 12112-7890.pdf 13333-0012 - Obama_Barak - June 2011.pdf Let's throw in that there are over 400 of these files and the file names really have no rhyme or reason to their formatting. I'm essentially trying to create hyperlinks to these PDF documents using the information found in the Contract Number cells. In attempting to find an answer online, I found the following but it would only work if the text in the cells was the full file name of the document without the pdf extension: code:
I also found something similar to the following but it doesn't work either (I don't have my work computer infront of me so it may be off a bit, but it was essentially pieced together using the above code and that suggested at http://www.mrexcel.com/forum/showthread.php?t=502374: code:
I hope my scenario is easy to follow. Any advice would be great appreciated.
|
# ? Mar 17, 2012 04:27 |
|
Sub Par posted:A bunch of excel code I didn't want to quote to take up a bunch of page space. Wow, you really know your stuff. I was hoping there was a way I could set it up to do it without using macro codes, like a function I could type in that would be able to do it. I really don't want to be using something that I don't know how to code, but it works. Testing it up, changing my results tab, if a team would be passing up another team based on points, it automatically sorts it in the standings tab. Couple questions: 1) I'm doing each division seperately, can I use that code multiple times, where it will change according to the specified fields? Like, I have the results tab, with every team, or would I have to make 2 different sheets for each division? No big deal if I would have to do that, would just be easier to have them all on 1 tab. 2) Is there a way to rank a list according to multiple criteria? I know there is a rank that if there is a tie(If you go by points, let's say 2 teams have 70 points each), it will come back with the same rank. I ask this because of the way standings are formulated with the NHL. I'm trying to rank the 3 division leaders, to place into the top 1-3 spots, then fill in the rest with the remaining teams. I figure a ranking system would be easiest to pull the data? 3) Maybe I'm doing it the wrong way, and should just try to make the conference standings without the division standings? But is there a way to copy over an entire row of data(Like A1:A5), and pull it into another table. For example: Pacific Division standings, 4 teams. The top team would be either 1,2,3 in the conference. Then of the other 3 teams, they are below 1,2,3. I want to be able to have the conference standings seperate of the division standings, and if possible for all of them to update just by me entering in the Win/Loss information in the results tab. My whole goal out of all of this is just to be able to toy around with possible scenarios, if x team wins what happens, etc. Extra stats, and a bunch of other "worthless" information that you have to do research for.
|
# ? Mar 17, 2012 20:58 |
|
Sub Par, and anyone else. Here is a link to the excel file I have been working on. http://www.mediafire.com/?73uau3rcqn72fc8 I have the Pacific Division lined up with the results tab to auto update, thanks to Sub Par's code, it sorts automatically. I just typed in the numbers manually for the other division's. I then just copied the cells over from the divisions to the conference. I guess I need to add the $ sign for each cell from the results tab so that the numbers are showing correctly(the Pacific shows 0's since the cells referenced changed in the results tab). Or I could always do =A2,=B2, etc. for the Conference standing, and then use the sort function to sort it accordingly? Hope this makes sense, I just have to make the divisions, then sort out which team gets the #1-#3 seed(each division leader sorted accordingly), then fill in the rest.
|
# ? Mar 17, 2012 21:43 |
|
BTW, if it's just functions that I can enter to do something, just point me in the right direction and I can toy with it to figure it out. I want to be able to do this on my own. I just wish I was more knowledgable with all of this.
|
# ? Mar 17, 2012 21:57 |
|
Edit: I found the power of the record macro function. I now have 2 different macros sorting 2 different divisions on the same worksheet. I think I can figure this out slowly but surely!
Bulls Hit fucked around with this message at 23:02 on Mar 17, 2012 |
# ? Mar 17, 2012 22:53 |
|
Tortilla Maker posted:Words about contracts The problem here isn't with Excel but with the naming convention (or lack thereof) within your contract files. I would personally be renaming the files to contract number only.pdf. Excel can be used to do this. If you don't want to rename the files then there is still a solution using the same principal. You want to look in to a function called Dir(). If you do something like the below you will get the full name of the file - it's up to you if you then just want to launch it or you want to run a one-time script to rename them all. This is done direct in the reply so excuse any syntax errors. code:
|
# ? Mar 18, 2012 14:46 |
|
Tortilla Maker, this should parse the file names for contract numbers and then if it finds a match will add the file name and a hyperlink to the file in a column to the right on the sheet with the contract numbers. I haven't tested it out, so it might need some tweaking.code:
|
# ? Mar 19, 2012 02:44 |
|
what is wrong with this vba? I put a comment next to what it is flagging as an error. It says "object required". I'm sure its simple but I am bad at this. Can I not do " " for blank cell? Do I need something else?code:
|
# ? Mar 19, 2012 16:09 |
|
Xguard86 posted:what is wrong with this vba? I put a comment next to what it is flagging as an error. It says "object required". I'm sure its simple but I am bad at this. Can I not do " " for blank cell? Do I need something else? You are setting rng1 to cell C1 and then deleting that cell, so I am guessing it is complaining because rng1 is now no longer set to a range value. But there are other things in your code that make me think it will not work the way you intended. First, because you never reset rng1 to any other cell (looks like you are intending to with the i value, but you don't quite get there) each time it loops it is always checking the exact same value. But since you are deleting rows in the process your loop will end up skipping cells you want to check. Example, cell C3 = "Dallas Office", on the first loop it checks C1 it leaves the row and sets i = 1. On the second loop it checks C2, leaves the row and sets i = 2. Third pass it deletes row 3 and sets i=3. However, row 4 is now row 3 and when it loops again it looks at the value which was in C5 but now is in C4. So every time the loop deletes a row it misses out on checking the next row's value. Here's how I'd do it, though someone else might have a better solution. code:
|
# ? Mar 19, 2012 17:06 |
|
ah I see. I never even thought about how deleting rows would screw up the loop.
|
# ? Mar 19, 2012 17:10 |
|
|
# ? May 11, 2024 13:56 |
|
Xguard86 posted:ah I see. I never even thought about how deleting rows would screw up the loop. Yeah, I learned that the hard way. With the code above I am checking the values from the bottom of the list going up. That way when I delete a row, the values below it which are shifted up have already passed the test while the untested rows above remain unchanged. Old James fucked around with this message at 17:15 on Mar 19, 2012 |
# ? Mar 19, 2012 17:13 |