|
Thanks. I'm a novice at best, and running into a few errors when trying the macro. Please forgive me for any idiocy because I had to make slight edits to some lines to get them to stop producing errors. But I'm getting an error now I can't fix. I set things up as follows into a module, and I'm getting "Compile error: Method or data member not found" on ws2.cell(i, 1).Value = ws1.cell(rows(i), 1).Value code:
|
# ? Jun 4, 2014 17:42 |
|
|
# ? May 25, 2024 14:31 |
|
me your dad posted:I'm getting "Compile error: Method or data member not found" on ws2.cell(i, 1).Value = ws1.cell(rows(i), 1).Value The error message means that something after a "." doesn't exist. In this case, there is no "cell" property on the Worksheet object. You're looking for "Cells".
|
# ? Jun 4, 2014 23:24 |
|
At work, I've started playing around with the macros that they've had for years to try to improve them. Started out just for myself, but it was really popular, and now my whole department is using my version. My only programming experience before was a semester of C++ in my freshman year of college, but I'm learning a lot about VBA in doing this. I've got an idea for something else I want to work on, but I'm not quite sure how to go about it: I want to start with a blank workbook, with several blank sheets, and I want to write a macro that, when run, will open up another worksheet whose name changes every day with the date, so today it will be, let's say, C:\work\LP-060514.xlsm. I want to open that as read only, since it is shared, or I want to know how to handle the "This worksheet is currently locked by John Smith" popup. Then, I want to navigate to a tab called AUDIT, filter Column C so that it just shows rows whose column C is "1," and then run a macro that's on LP-060514.xlsm that removes superfluous columns, called Sub RemoveToPrint(). Finally, I want to take everything that's on that sheet and copy it to a specific worksheet on my initial blank workbook. So, as bullet points, I am hoping to write a macro to: -Open a shared workbook as read-only -Navigate to a specific sheet on that book -Filter a column of that sheet -Run a macro that is on that workbook -Copy the result into my original worksheet I have done variations on a lot of this before, but the things that I am not sure how to do are how to make it open read only, or how to handle it if someone else is in the workbook, I don't know how to filter a column, and I don't know how to run a macro that is in another workbook than the one I started in. I hope this makes sense, can anyone direct me where to start?
|
# ? Jun 5, 2014 12:04 |
|
Zaffy posted:I get a report every week that looks like this: Here's how I did this: Use this first - =IF(B2=B1,CONCATENATE(G1,",",CONCATENATE(C2,",",E2,",",F2)),CONCATENATE(C2,",",E2,",",F2)) That gives you a result of this: Then I'd use another formula to get the last (i.e. the full line) of that - =B2=B3 Copy/Paste Special to remove formulas, sort by the true/false line and delete everything that's not a FALSE. Those ones are the last lines, the ones you want to keep. Then just do a text to columns on the new cell, delimited by comma.
|
# ? Jun 5, 2014 12:57 |
|
Talby posted:I have done variations on a lot of this before, but the things that I am not sure how to do are how to make it open read only, or how to handle it if someone else is in the workbook, I don't know how to filter a column, and I don't know how to run a macro that is in another workbook than the one I started in. - Workbooks.Open has an optional parameter called "ReadOnly". Set it to true. - The Range object has some properties for filtering (AutoFilter? I never use it). Protip: if you can't find something in the documentation but you know how to do it by hand, use the macro recorder, record yourself doing it (e.g. filter a column), then check the produced code to figure out the name of the property/method that is called. - Application.Run will let you run a macro from a string representing its name (I think you'll have to qualify it with the macro file's name also, so "'C:\work\LP-060514.xlsm'!RemoveToPrint" or something like that...sometimes it doesn't work without single quotes, even if the filepath has no spaces). If the file that contains the macro is not already open, Application.Run will open it. It will not close it afterwards. Also, it will lock the file if it can. So you should open it as read-only yourself, then Application.Run it, then close it yourself. Question: Is there really a different "RemoveToPrint" every day, one in each daily version of "LP-060514.xlsm"? Otherwise, wouldn't it be easier to move this routine to your main workbook and just pass it a reference to the workbook you want it to act on? You probably want to avoid copying the code over and over unless you really need to (e.g. you want to make sure that LP-060514.xlsm will always run with the version of the code as it was on that date, not whatever the current version is).
|
# ? Jun 6, 2014 00:10 |
|
Smithersnz posted:Here's how I did this: Thanks, that's a huge improvement over how I've been dealing with it so far
|
# ? Jun 6, 2014 00:45 |
|
ShimaTetsuo posted:- Workbooks.Open has an optional parameter called "ReadOnly". Set it to true. I managed to test opening it with Workbooks.Open while not a lot of people were in the office, so I can easily add the ReadOnly paramater tomorrow. Thank you! And actually I used the Macro recorder to get the code for that filter. The RemoveToPrint function is not different each day, the code from 060514.xlsm will work on 060614 will work on 103015 will work on everything, and I hadn't even thought of just copying it to my main one. It's not a huge chunk of code, and it would be easier to make it do what I want it to do without affecting the macro that everyone else uses. Basically, LP-date.xlsm is generated every morning and contains more or less my entire job. Each day when I get in, I have to go to about 8 different tabs, filter out my area on each one and print each one, so that I can reference it without keeping the shared file in use. What I want to do instead is just automatically have all of my area filtered out and copied to a workbook of my own. From there, I have more big ideas, but getting this done will be the first big step. Thank you for your help!
|
# ? Jun 6, 2014 02:14 |
|
I thought this problem would be easy to solve, but it's not. Help? I have two sets of data. The third table is what I want. code:
|
# ? Jun 9, 2014 20:31 |
|
Index match?
|
# ? Jun 9, 2014 22:15 |
|
Index match works: =INDEX($A$2:$A$8,MATCH(B10,$B$2:$B$8,0)) Vlookup: Alternatively, you can swap the two columns and use VLookup(). Swapping the columns can be done off to the side. Match: =MATCH(B10,$B$1:$B$8,0) Since your first column is sequential you could just use a match statement.
|
# ? Jun 10, 2014 03:11 |
|
Ended up using VLOOKUP since it was the first suggestion and it worked marvelously. Thanks!
|
# ? Jun 10, 2014 16:41 |
|
I'm trying to get a list of unique values from a selection of multiple columns/ranges on a new sheet using a macro. I have it kind of working, but I'm relatively new VBA and hitting my limit. If anyone could take a look, I'd really appreciate it. What's broken: Needs error handling if a pivot table/chart, etc. is selected. If multiple selections are in the same column, they're going to different output columns, while I'd like them to be in the same output column. The output destination should be a range with reasonable dimensions, not the entire column. I have the output columns increment by one, but I'd like to use the next blank column. Only the values should be copied, not the formatting. I can't get sort ascending to work. code:
|
# ? Jun 14, 2014 00:53 |
|
Do you mind taking a quick screenshot of what you are starting with and how you want the results displayed?
|
# ? Jun 15, 2014 07:19 |
|
Thanks for checking this out, Old James. Color added for emphasis. Edit: and if I were to run it again on column C, on the output sheet, F and M should be in column D. Here's my favorite macro to create a pivot table (with a custom pivot table style) from a selection, or if no selection is made, it uses the current region, and sets the layout to classic. code:
schmagekie fucked around with this message at 09:21 on Jun 15, 2014 |
# ? Jun 15, 2014 08:37 |
|
Is it just me or does Excel 2013 just seem really buggy? I've had problems with it randomly "freezing", UI elements will disappear, opening new files will effectively open a new Excel windows that's 100% transparent, etc?
|
# ? Jun 16, 2014 01:22 |
|
Does anyone use the OneStream XF Excel AddIn? I use it for work and am trying to use VBA to code a macro in Excel 2010. I can't figure out how to get the macro to go into the OneStream XF tab and select Refresh XF Data. When I try to record one of clicking it, it just gives me Application.CalculateFull but this alone just recalculates and doesn't refresh the retrieve from OneStream? Anyone have any experience with this?
|
# ? Jun 16, 2014 15:45 |
|
I've been tasked to update some monstrosity of an Excel document filled with macros. I open it up and there is zero code in the VBA editor. Nothing. If I open up the macro screen and click to edit one of the macros, it redirects me to a worksheet like this: What the gently caress is this? How is a macro running from code inside a cell? This Excel document is old as gently caress, like back in 1998ish.
|
# ? Jun 18, 2014 19:53 |
|
kiwid posted:I've been tasked to update some monstrosity of an Excel document filled with macros. I open it up and there is zero code in the VBA editor. Nothing. I'd never heard of this, looks like it's from Excel 4.0. Wish I could help, but your best bet is probably to try to take each of those individual cells and convert them into whatever their modern equivalent is, probably Functions within Modules.
|
# ? Jun 18, 2014 22:24 |
|
DukAmok posted:I'd never heard of this, looks like it's from Excel 4.0. Wish I could help, but your best bet is probably to try to take each of those individual cells and convert them into whatever their modern equivalent is, probably Functions within Modules. Oh nice find, I wasn't able to Google search what it was which was why I asked here. Though, the boss doesn't want me to update it for VBA, so gently caress my life.
|
# ? Jun 18, 2014 23:59 |
|
Yeah, the Excel 4 style macros are the ancestor to having VBA in Office. You can execute a legacy Excel 4 macro from within VBA by using Application.ExecuteExcel4Macro, if that helps. I wouldn't worry about them disappearing from later versions of Excel, specifically because dumb old people who have been running the same spreadsheet for 20 years like your boss are Office's main customers. But it would probably just be easier to convert it to VBA, unless it's much more complicated than what you've shown here. Related: Last year I received a spreadsheet from a data provider that seemed to be cut short at 16384 rows. The latest version of Excel to have such a limitation was released in 1995.
|
# ? Jun 19, 2014 00:45 |
|
Is it possible to write a script that will go through a spreadsheet, and if a row has a value, hide that row? I've started a new job and am trying to teach myself python scripting at the same time. Today I was tasked with reading through a spreadsheet, and if a cell had a certain string, I wanted to hide that entire row. Tedious stuff, and I'm guessing there is a way to automate it. I think i can fuddle my way through writing a basic script but I'm unsure how to issue a "hide row" command.
|
# ? Jun 27, 2014 23:47 |
|
Hughmoris posted:Is it possible to write a script that will go through a spreadsheet, and if a row has a value, hide that row? I've started a new job and am trying to teach myself python scripting at the same time. Today I was tasked with reading through a spreadsheet, and if a cell had a certain string, I wanted to hide that entire row. Tedious stuff, and I'm guessing there is a way to automate it. code:
|
# ? Jun 28, 2014 00:37 |
|
Old James posted:
Thanks for this. A couple of dumb questions. What language is this, VB? How would I adjust it if the keyword I'm looking for is either "nausea" or "vomiting". The cell may have both.
|
# ? Jun 28, 2014 01:20 |
|
Hughmoris posted:Thanks for this. A couple of dumb questions. What language is this, VB? How would I adjust it if the keyword I'm looking for is either "nausea" or "vomiting". The cell may have both. It's VBA, the standard scripting language for Excel, as is probably everything in this thread. It is probably the easiest way to script Excel (although if you just need to read Excel documents it is not much more difficult in other languages). If you feel like using Python you may have better luck in the Python thread because AFAIK there are several different Python/Excel libraries and I doubt many people in this thread are very familiar with (all of) them. However: if the Excel library you are using in Python is built in a way that reflects the Excel object model, you should look for objects in something that should look like the collection hierarchy Application->Workbooks->Worksheets->Cells. Cells is a collection of Range objects, each of which should have a Value property which holds its contents, and a Hidden property that can make it invisible when set to true (although it only works if a Range spans an entire row or column). Build a list of Range objects however you need then iterate over them, making each corresponding row hidden. Also, unless there is more to your problem, can't you just use a filter in the spreadsheet, without any coding?
|
# ? Jun 28, 2014 03:39 |
|
Hey guys, I'm having trouble understanding VLOOKUPS and was wondering if I could get some help. My tables has the following information (in this order). Employee Employee ID Manager I need to figure out how to VLOOKUP all the people under a certain manager. I've spent the last hour and a half racking my brain trying to figure this out but I've come up on a bunch of errors and no actual results. Thanks.
|
# ? Jun 29, 2014 00:20 |
|
VLookup only returns values to the right of the column you are matching on and that is your problem. To get around this people commonly use a combination of the INDEX() and MATCH() functions. I throw an IFERROR() around it all so I don't have messy error codes when there is no match. =IFERROR(INDEX([Employee],MATCH("John Smith",[Manager],0)),"")
|
# ? Jun 29, 2014 02:38 |
|
Elucidarius posted:Hey guys, I'm having trouble understanding VLOOKUPS and was wondering if I could get some help. Vlookup is useful when one unique index refers to one item in the table. Don't forget when using Vlookup to include the FALSE statement as the fourth praram when your data isn't already sorted. In your case, an auto filter will quickly give you all the employee's under a specific manager. Likewise sorting might help group the Employee's by manager. Another way might be to use Pivot tables, but they are likely more challenging than needed for this problem Edit: Old James is correct about the index/match. WhatsInaMojito fucked around with this message at 02:48 on Jun 29, 2014 |
# ? Jun 29, 2014 02:42 |
|
Each row in Column D contains it's own value, for example cell D25 contains the value "Apple". In Column B, I need to use the info in the corresponding cell in Column A to pull the corresponding value from Column D. For example, I would want cell B1 to read "Apple" since A1 points to D25 which has the value "Apple". Is there a formula to do this considering I have thousands of rows I need to apply this to? Thanks! Edit: Figured it out via the "indirect" function. Thanks though! Tortilla Maker fucked around with this message at 18:35 on Jun 30, 2014 |
# ? Jun 30, 2014 17:50 |
|
Looking for a way to pull some monthly numbers for a team of folks I manage and I'm running into a problem with the way our business system handles account adjustments with taxes. For example, I'm attempting to determine the total number of adjustments (meaning any time we manually issued a credit or debit to an account) each of my folks has completed in the month of June. When I credit/debit any charge that has a tax associated with it, the following occurs: Customer Service Fee/Courtesy Credit: -$10.00 Tax on: Customer Service Fee: -$0.70 The second entry is not a 2nd adjustment manually done, it is an automatic calculation, but because it is itemized separately, when I just run a COUNTIF to sum up the numbers for each user, anything that has a tax gets counted twice (and we do some business in Texas, and certain charges there actually have 3 different taxes, so 1 adjustment shows up as 4 different ones). I want to remove these 'Tax On:' line items from my calculations in terms of the # of adjustments completed, however I don't want to actually remove the line items entirely because I'd also like to run something that sums up total credits issued, which should include the tax dollars credited or assessed. The columns in my spreadsheet look like this: code:
Also looking for advice as to how to get the total credit dollars each user gave - what I would normally do is just use a filter to filter by user, remove any positive numbers (debits) via the filter, and sum what remains, but I'd like something more automated so I don't have to do this manually every month. I tried reasoning my way through this trying to use VLOOKUP somehow but I'm not sure that's the right direction.
|
# ? Jul 2, 2014 23:01 |
|
I hope you meant SUMIF and not COUNTIF in your question. Also you can just do this with a wildcard condition. =SUMIFS(E:E, F:F, "Kumba", C:C, "<>Tax on*") will give you total for Kumba not including rows marked "Tax on".
|
# ? Jul 2, 2014 23:11 |
|
ZerodotJander posted:I hope you meant SUMIF and not COUNTIF in your question. I meant COUNTIF, cause in that example I was just counting the number of individual adjustments completed, not how much the actual value of the adjustment is (for comparative purposes, so if Person A did 10 adjustments and Person B did 735, I know there's a lazy person problem). I did not know this =SUMIFS function existed and I think it will do exactly what I needed. Thank you a ton!
|
# ? Jul 2, 2014 23:37 |
|
I have a sheet with a 4 option pulldown in one cell. I want a range of cells to display one of four numbers based upon what is selected in the pulldown cell. Please help!
|
# ? Jul 8, 2014 20:10 |
|
ScarletBrother posted:I have a sheet with a 4 option pulldown in one cell. I want a range of cells to display one of four numbers based upon what is selected in the pulldown cell. Please help! Make another sheet and store a table mapping the key (your pulldown value) to a value, then use vlookup in the cells that you want to display the values based on the keys.
|
# ? Jul 8, 2014 20:53 |
|
I got it to work using IF(Cell='foo','blah',IF(...
|
# ? Jul 8, 2014 20:56 |
|
If you want to get clever with it try this (assuming your drop down is in cell A1 and has values A, B, C, & D). =choose(match(A1,{"A","B","C","D"},0),"foo","bar","rab","oof")
|
# ? Jul 8, 2014 21:29 |
|
I have a bunch of pdfs that I'll be converting to .xlsx. The excel files are going to look like garbage of course (columns all crazy, etc... if you've ever used Acrobat's .pdf to .xls converter, you know what I'm talking about), so I'm trying to extract the useful data from them to insert into a nice new spreadsheet that can actually be made sense of. I have a template that's going to be used to dump each sheet into. The data are financial, 1 year of data for one department within the organization is contained on two different sheets (one for original budget at the beginning of the fiscal year, one for actual budget at the end of the fiscal year). There are about 70 departments and five years of data for each of them, so a total of about 700 sheets. My converted pdf files look something like this (the whitespace below may denote entirely blank columns, or whitespace within a cell): code:
code:
By itself I don't think this would be too challenging. However, there are two things here that make it more complicated: 1) Not every PDF I'm converting has entries for all of the same accounts. Due to stupid reporting practices, if an account reports $0 in revenue or expenses, it doesn't actually show $0, it just doesn't show the account at all. The template will actually list all of the accounts, so I need to come up with code that does some kind of "scan and match" thing, and defaults to $0 if it can't find the account in the garbled sheet. 2) Some of the accounts have subaccounts that are listed along a second axis, which is annoying by itself, but this also causes them to spill over onto extra pages. For example: code:
code:
|
# ? Jul 9, 2014 14:36 |
|
Ronald McReagan posted:Is there any hope? Assuming you're somewhat handy with VBA you can loop through the cells and try something like. If Empty delete cell, move cells left. If not empty trim contents. Trim(String) will work for that. Maybe Trim(Cstr('Cell Contents')) can't remember if it demands a string or not. It should end up formatting things somewhat better for you assuming it doesn't start mashing up multiple values into one cell. If it does that you've got a whole different problem. [Edit - Did you edit your post or am I terrible at reading? This probably doesn't solve your problem at all sorry I'll take another look when I have a spare few minutes] Cast_No_Shadow fucked around with this message at 11:07 on Jul 10, 2014 |
# ? Jul 10, 2014 11:04 |
|
Ok taking another look you should break this down into the problems you want to solve. Step one, clean up your whitespace. Step two, get rid of unwanted cells Step three, move the sub columns into the new Parent Child Child Child Parent Type structure. My confusion is about the $0 entries and overall structure of the sheets. If with the sub accounts you have $0 entries how do you know in the garbled mess? You can pull them all across so they line up with the method in my previous post but otherwise there is no way to know the difference between a blank cell because its garbled and a blank cell representing $0. All I can suggest would be to pull everything left and then manually check. If we're talking huge amounts here have it run through again and any Child account number without a figure under it highlight a color to help check. Once you get to there though, it should be nicely formatted and moving data from the horizontal axis to the vertical should be pretty simple.
|
# ? Jul 10, 2014 11:20 |
|
Here are a couple subs to shift everything you select to the left, and delete the entire row if column B = 0.code:
code:
schmagekie fucked around with this message at 06:48 on Jul 11, 2014 |
# ? Jul 11, 2014 06:26 |
|
|
# ? May 25, 2024 14:31 |
|
Ronald McReagan posted:
I copied your post code for the raw data you're working with, pasted into word and then again to Excel, used Data tab > Text to Columns (spaces as the delimiter). I don't work with PDFs so I'm guess it's similar for you? Anyway The getting stuff in line part is pretty easy I think. I just used 2D arrays, since you can basically do whatever through however much you want and it's almost instant (you're basically asking excel to memorize the sheet and do everything in it's head as opposed to forcing it to write down each step as it goes into the cells). code:
For the one to fix the split rows you need to make sure to adjust Set ReDim Preserve Mess(1 To UBound(Mess), 1 To 12) to whatever your actual range will be after you paste your stuff. If you don't tell an array to make room for things that don't exist yet, it won't be able to write to anything outside of the original "usedrange." code:
Trust me, it's fast as heck and I use it when dealing with ~200,000+ cells at a time--it's almost instant for row/column removal etc. (which you do similar to the second hunk of code I posted, by writing from one array to another and skipping/keeping track of skipped blanks). I didn't get to your $0 thing because I wasn't sure exactly what you meant (sorry, it's late and I didn't have time to skate through your post again, but I will try tomorrow evening if I get the chance).
|
# ? Jul 11, 2014 07:27 |