|
Hey excel thread - is there any way to export the formatting of a .xlsx to VBA code (".Range("A5").Font.Bold = True" etc)? Basically I have an intricately formatted report that I am automating in Access, but manually specifying every single piece of this spreadsheet will take hours - any way to automate that process would be incredibly helpful.
|
#
?
Dec 6, 2018 22:11
|
|
|
|
| # ? May 13, 2024 11:33 |
|
|
Remora posted:Hey excel thread - is there any way to export the formatting of a .xlsx to VBA code (".Range("A5").Font.Bold = True" etc)? Basically I have an intricately formatted report that I am automating in Access, but manually specifying every single piece of this spreadsheet will take hours - any way to automate that process would be incredibly helpful. It might not be exactly what you want (I have no idea on that, not a VBA user) but couldn't you do the HTML format to at least get font/colour/etc information and then do something with that? I could see regex being helpful there.
|
|
#
?
Dec 6, 2018 22:24
|
|
|
There's probably not going to be a good way but what are you trying to do with the formatting specifically?
mystes fucked around with this message at 03:47 on Dec 7, 2018 |
|
#
?
Dec 7, 2018 03:42
|
|
|
I need to exactly match the appearance of this legacy report that was previously done by hand from an Excel template, because people are scared of change. I have an Access database that can do all the number crap, but if it looks different people won't use it, because people are scared of change (and dumb). It's got merged cells and panes and tons of color-coding and crap. I can just code it all into VBA by hand, but that is going to suuuuuuuuck.
|
|
#
?
Dec 7, 2018 04:09
|
|
|
Remora posted:I need to exactly match the appearance of this legacy report that was previously done by hand from an Excel template, because people are scared of change. I have an Access database that can do all the number crap, but if it looks different people won't use it, because people are scared of change (and dumb). It's got merged cells and panes and tons of color-coding and crap. I can just code it all into VBA by hand, but that is going to suuuuuuuuck.
|
|
#
?
Dec 7, 2018 04:13
|
|
|
Generating an excel file using VBA. I have to bake it all into the database itself, I can't rely on the end user to have the template on hand as a long term solution - is there a way to stuff an excel template into Access? I can probably limp along with that for the next few weeks, though, I suppose.
|
|
#
?
Dec 7, 2018 10:24
|
|
|
Remora posted:Generating an excel file using VBA. I have to bake it all into the database itself, I can't rely on the end user to have the template on hand as a long term solution - is there a way to stuff an excel template into Access? I can probably limp along with that for the next few weeks, though, I suppose.
|
|
#
?
Dec 7, 2018 15:12
|
|
|
AzureSkys posted:I have a spreadsheet my group uses to keep track of a product number, the date it starts, the date it finishes, and the location it’s made at. We use this to keep track of who’s assigned which product number at which location. There are multiple sources for the start and finish dates along with location and we’ve been looking at those (usually a PDF) and manually putting those dates and names in our spreadsheet, which gets tedious and easy to mistype. The dates and location can change daily or get assigned for the 100 or so product numbers that are needed on the assignment sheet. If I'm understanding you right, you want a sheet that reads the .csv and updates the start date and end date? So if you have product A, if you paste in the .csv somewhere, the formula knows to check that row for product A and list the most recent date as the end, and the oldest as start?
|
|
#
?
Dec 16, 2018 20:39
|
|
|
Testikles posted:If I'm understanding you right, you want a sheet that reads the .csv and updates the start date and end date? Yes. I have them linked by the product number that both my schedule and the .csv will already have listed. What I'd like is for my schedule sheet to read the .csv and pull the start date for the applicable product number (which is the first date in the .csv data extract) into one cell and the finish date (which is the last date in the extract) as well as the location which is easy to do. But, if the dates are older than today they end up blank since the .csv only has dates from today and on. There may be a product that started a few days prior and just finished, but still needs to be on the assignment sheet for other purposes. I'd like them to stay with whatever the last date was if it is prior to today, if that makes sense. That would fully automate the process so others who are less savvy can just grab the daily .csv extract, update the data link and everything will go from there. The .csv looks like this more or less: code:code:So if today is 11/23/2018, product 1053 would still be in process but the start date listed on my assignment sheet would show 11/23/2018 instead of it's actual start date of 11/21/2018 . If it was after the finish date for that product, like 11/27/18 and 1053 was still needed on the assignment sheet both dates would then be blank instead of remaining 11/21/2018 and 11/26/2018 since it would no longer show it on the .csv schedule. I have no clue if that's even possible to do, though.
|
|
#
?
Dec 18, 2018 09:05
|
|
|
What would really be the best imo is writing a macro in VBA that will read the csv and print the most recent date and oldest date to your sheet. The problem might be too complicated for formulas as is. Do you have any experience writing stuff like that or is it all just formulas?
|
|
#
?
Dec 25, 2018 05:44
|
|
|
I haven't done anything much with VBA other then edit some macros. Things will be slower at work so I'll more time to try to learn some.
|
|
#
?
Dec 25, 2018 16:24
|
|
|
Hi Excel thread, I don’t have a question because the shame and annoyance of having to come here and ask a question gave me the motivation to figure out the incredibly obvious solution I was overlooking while I was typing the question. Thanks Excel thread!
|
|
#
?
Dec 29, 2018 01:18
|
|
|
Ok, I lied, I do have a question though one that's about convenience more than anything else. I have a a worksheet that contains a master list of file names and their corresponding name in our accounting software arranged as such: code:On another worksheet I'm creating another list that some third party software will look at and export data from those customers' files into the accounting software. The convenience issue I'm running into is that for the third party software to work every account to be exported to the accounting software needs its own row (10 accounts, so 10 row each), like this: code:code:code:I'm just wondering if there's a way to just... not do either of those? Keep the master list worksheet as it is (one customer per row, no repeating or blank cells), keep the export worksheet as it is (10 rows for each customer), and be able to auto-fill the Vendor Name column on the export worksheet? Edit: In short, have the auto fill on the export worksheet only move down one row reference to the master list worksheet despite the export worksheet moving down 10 rows before referencing the next master list row. Kibayasu fucked around with this message at 00:18 on Dec 30, 2018 |
|
#
?
Dec 30, 2018 00:07
|
|
|
If you want more control over which rows in one range match to rows (or columns) in another, generally you use the INDEX function. The ROW function will give you your current row number and to compress 10 rows into one just divide by 10 and take the FLOOR or CEILING. Adding and subtracting before the division and after the ceiling allows you to offset the rows in the source and target ranges. In this case using ceiling is easier than floor because it will map 1 - 10 > 1 rather than 0 - 9 > 0 and is generally easier to reason about.code:
|
|
#
?
Dec 30, 2018 05:13
|
|
|
Ninja.Bob posted:If you want more control over which rows in one range match to rows (or columns) in another, generally you use the INDEX function. The ROW function will give you your current row number and to compress 10 rows into one just divide by 10 and take the FLOOR or CEILING. Adding and subtracting before the division and after the ceiling allows you to offset the rows in the source and target ranges. In this case using ceiling is easier than floor because it will map 1 - 10 > 1 rather than 0 - 9 > 0 and is generally easier to reason about. Thanks a lot. I'm still not sure if I understand exactly what this is doing but once I changed your ROW()-1 to ROW()-10 it worked exactly as I needed it to. By header rows did you mean any rows before the start of the actual list on either sheet? In my case the master list sheet started on Row 10 and the export sheet starts on Row 20 so that may have something to do with it? And just to note an oddity I took note of after your solution worked, I accidentally stopped autofill one row further down than I should have when I was testing it. Since I need 10 rows for every customer I select Rows 20-29 and fill down to Row 39. This worked perfectly, but on the next test (Row 40-49) I stopped on Row 50 by mistake. Row 50 filled out with the next name on the list it should have but when I continued filling down the next row (51) immediately switched to the next name in the master list, the customer that was below the one in Row 50. I hadn't reselected the autofill range or anything either. Now I should be always stopping on a Row ending with 9 anyways so this isn't really a problem that needs to be fixed (and "filling" back up to the row I should have stopped on fixes the issue if I needed to continue to fill down) but I just thought I'd mention it. Anyways as long as use it on the correct number of rows it works great  Thanks!
Kibayasu fucked around with this message at 19:36 on Dec 30, 2018 |
|
#
?
Dec 30, 2018 19:32
|
|
|
If you aren't exactly sure how the formula works, you can use the Evaluate formula tool under Formulas > Formula Auditing to see it broken down step by step. You understood correctly what I meant by header rows. If the first row of actual data in the export sheet is row 20 then you would want ROW()-19, as ROW()-19 = 1. If the data in the 'Master List' sheet starts at row 10 then the index range should be 'Master List'!$A10$A$.... So the final formula would be =INDEX('Master List'!$A$10:$A$1000,CEILING((ROW()-19)/10,1)). This will then get the customer name from row 10 in the master list for export rows 20 - 29, 11 > 30 - 39, etc. If you are getting different results from auto filling up vs down then there is definitely an error. The formula should be identical in each row so make sure that you are using an absolute range that doesn't change as you copy it. The other common source of error is changing the first row but forgetting to update the rest and then copying them as a block to end up with a different formula every 10 rows. This can be hidden in this scenario because it might take a few customers before the error grows large enough for the result to be wrong.
|
|
#
?
Dec 31, 2018 05:26
|
|
|
I mean I autofilled all 288 (so 2880 cells) without an error (except if I autofilled/pasted to the wrong cell) so...  I’ll look at it again next chance I get.
|
|
#
?
Dec 31, 2018 05:32
|
|
|
Ninja.Bob posted:If you aren't exactly sure how the formula works, you can use the Evaluate formula tool under Formulas > Formula Auditing to see it broken down step by step. You understood correctly what I meant by header rows. Right, so to satisfy whatever curiosity this might have generated there was indeed an error but I was also mindlessly repeating what I was doing to test the results of my previous attempts despite saying I was only using autofill. But because I repeated what I was doing to test the results before using autofill on the rest of the sheet it resulted in the correct answer. CEILING((ROW()-10)/10,1) worked because when I was trying various other methods myself I had set the 9 cells below each first cell to equal the cell above it until the start of the next 10 rows. So after putting in your code it looked like: code:code:CEILING(ROW()-19)/10,1) of course works for every cell without the need to =A20, =A21, etc. Thank you again.
|
|
#
?
Jan 2, 2019 18:14
|
|
|
never mind
nickhimself fucked around with this message at 21:14 on Jan 22, 2019 |
|
|
#
?
Jan 22, 2019 20:14
|
|
|
Sorry for the double post, but it turns out I actually do need help. Here's what I'm doing. In this leasing worksheet I've coded a button that will import an old deal and convert it all to the newer model. However, I'm stumped with the last piece of the conversion puzzle. Here's a clip of where I'm working inside of:  Those yellow lines often contain things like "Delivery $100.00" between columns A and B. However, the salesmen don't *always* add items. When they do, sometimes they'll add an entire row, other times they'll remove rows. This is what I need: Any lines in A11-13 that equal "Initial CC" need to be cleared, along with the associated cost in the next cell. Any ideas? edit: lol, I'm dumb. I thought about it for more than two seconds and figured it out. Range("A1:B34").Select Cells.Find(What:="Initial CC", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.ClearContents ActiveCell.Offset(0, 1).ClearContents nickhimself fucked around with this message at 15:55 on Jan 25, 2019 |
|
|
#
?
Jan 24, 2019 18:02
|
|
|
Just received a new laptop at work with the latest version of Microsoft Office 365. My previous laptop had Microsoft Office 2013 installed and I had no problems opening CSV files with all the data formatted in the correct way – columns were all separated correctly. However, I’m having issues now on my new laptop right when I open the CSV file for some reason – all the columns are stuck together. A correct fix that I found is unchecking “Use system separators” in the Advanced section of Excel options. When I uncheck that, all the columns and data is separated correctly but it also messes up the format of some of the variables such as the currency amount. The CSV columns are formatted like this “data 1, data 2, data 3, data 4” etc. How can I fix this? It doesn't fix it if I save the CSV as an Excel Workbook after opening it and I do not have an option to save the CSV files as an Excel Workbook when I download the workbook.
|
|
#
?
Jan 29, 2019 12:16
|
|
|
Windows is probably configured to use comma as decimal separator, and period as thousands separator. (European style.) Change that in the international settings page where ever they have put it now.
|
|
#
?
Jan 29, 2019 15:16
|
|
|
nielsm posted:Windows is probably configured to use comma as decimal separator, and period as thousands separator. (European style.) Change that in the international settings page where ever they have put it now. That worked, thank you!
|
|
#
?
Jan 29, 2019 16:38
|
|
|
I have a Customer Name search box that looks through a Last Name/Business Name list and returns the first matching result, plus the next subsequent seven below it in alphabetical order. However, a bug has been found and I don't know how to google the answer. The macro currently begins by first selecting the top cell in the column, then combing the list for the first matching term. If I don't do that I run into other sorting issues unrelated to this. If I search for "Smith" it will return something like "Jeff Smith Corp, Inc." instead of finding the first "Smith" name down in the S range. How can I make it search from the beginning of the word first? Is that possible?
|
|
|
#
?
Jan 30, 2019 16:20
|
|
|
Without being sure how the macro is working, something like LEFT(cell, LEN(search_string)) maybe. So if it’s doing a loop through the cells like: Find(search_string, cell) You could change this to be: Find(search_string, Left(cell, Len(search_string))) This would only find where Smith is at the start of the entry. You may have issues with entries starting with “The” though, depending on your data and your searches.
|
|
#
?
Jan 30, 2019 19:47
|
|
|
I'll give that a shot and see if it does what I need. If not, I'll be back with more problems, lol Thank you!! nickhimself fucked around with this message at 12:57 on Jan 31, 2019 |
|
|
#
?
Jan 31, 2019 12:53
|
|
|
I have a few users that work in duplicate worksheets but different data sets. For example : Order number | date worked | note Is it feasible to import their data to a central worksheet? I'd eventually like to pull and push data to this central worksheet. Will I run into massive slowdown from accessing the worksheet frequently to pull and push this data?
|
|
#
?
Feb 14, 2019 02:33
|
|
|
I think this is a stupid question but I can't figure this out. I have a list of data that I need to convert to a Dimension & Measurement format. Right now it looks like:code:code:Edit: need to unpivot from power query, I think I got it. Lockback fucked around with this message at 19:17 on Feb 19, 2019 |
|
#
?
Feb 19, 2019 17:27
|
|
|
Is there a good way to make a "best fit" lookup in a table? Scenario: I have a collection of network latency measurements from a large number of computers across multiple days, and several measurements each day. In another table, I have collected the active network interfaces around the time of each latency measurement. I want to select the most likely network interface used for the measurement for each measurement, based on same date, proximity in time, and network interface with the lowest routing metric value. Is this a job for VBA?
|
|
#
?
Mar 20, 2019 12:31
|
|
|
I think I almost found a solution. If I make a composite key column I can (almost) use VLOOKUP with the range_lookup True. The only issue is that I want the first value greater than the key, rather that the last value smaller than the key. Maybe I can do some trickery with inverting one or more of the parts of the key.
|
|
#
?
Mar 22, 2019 08:41
|
|
|
Switch to INDEX(MATCH()) instead of VLOOKUP. The last argument of MATCH() is a -1,0,1 value that specifies how the lookup value is compared in the lookup array; a value of -1 will find the smallest value that is greater than or equal to the lookup.
|
|
#
?
Mar 22, 2019 16:28
|
|
|
Basically, I'm building an overview of my books, and I'm thinking of a system where for a specific book, I put 1s in the correct categories - say fiction, historical, crime - and have one cell that lists the header for each cell with 1s in it. I'm partially there through the following: if(isblank(B13),"", $B$1&", ") Will return the column title for B. But doing it this way for all columns would take a lot of imputing each column, I'd like to avoid this. Essentially I want to get to this - with C2 in this case being the programmed content and the rest manual inputs : code:double nine fucked around with this message at 23:31 on Mar 23, 2019 |
|
#
?
Mar 23, 2019 23:05
|
|
|
Here is a function:code:
|
|
#
?
Mar 24, 2019 07:24
|
|
|
thanks. I added an iferror to make it less ugly at the start but it looks exactly like I need to. one of these days I'm gonna learn vb, I swear.
|
|
#
?
Mar 24, 2019 18:21
|
|
|
Someone else used to do this, they did it manually each time for some reason. https://imgur.com/a/onFO0Ps How can I reference the cell value as part of the formula to reference another sheet's data? The "t" in the picture can ideally be set by the cell above "Sales Person"' I decided to take one last stab at it, and I got it working. Figured I'll still post. Maybe this can help someone else out. =indirect("'2019Mirror'!$"&T$2&row())
|
|
#
?
Apr 2, 2019 16:33
|
|
|
A student gets a level 9, their below target is 1-4, good is 5-6 and excellent is 7-9. The spreadsheet should see this and say "Excellent". I've come up with 2 solutions for this so far. Solution 1. I managed to solve this using 3 bonus columns that just use LEFT() to get the 1/5/7 from those target ranges and then an INDEX(MATCH()) to look it up. See attached pic. Solution 2. I took an average of the good column (so 5.5 in the above example). I then created a table combined with a Vlookup to see if they were above/on/below target. Is there a better way that doesn't involve creating those extra columns? I asked some people and they just keep saying "Use a VLookup" but don't explain it. I'm pretty sure that Excel doesn't know that 1-4 means the numbers 1, 2, 3, 4 inclusive which would mean that a VLookup without my bonus columns from the attached picture wouldn't work.
|
|
#
?
Apr 7, 2019 14:11
|
|
|
Sad Panda posted:A student gets a level 9, their below target is 1-4, good is 5-6 and excellent is 7-9. The spreadsheet should see this and say "Excellent". I've come up with 2 solutions for this so far. If you use a vlookup with "true" instead of "false" then it will match the highest value that doesn't exceed your lookup number. So a table like: 0 Below Target 5 Good 7 Excellent A vlookup will take a numeric score like 5.25, and will look it up on the table to output the words. You could also do this via an index(match()) with a match type of 1, sort of like how you did. But generally just having a table in a hidden column off to the right is the best move here. esquilax fucked around with this message at 18:58 on Apr 7, 2019 |
|
#
?
Apr 7, 2019 18:54
|
|
|
There's a third way, although it's (arguably) not as clean as using a lookup table.
|
|
#
?
Apr 8, 2019 19:04
|
|
|
I have the latest version of Excel and whenever I do a Find and Replace -> Replace All -> Dialog pops up saying they made XX,XXX replacements and I click okay -> My Excel hangs and freezes for at least a minute and I can't do anything. What's going on here and how can I fix this?
|
|
#
?
Apr 9, 2019 11:10
|
|
|
|
| # ? May 13, 2024 11:33 |
|
|
you have a bunch of volatile equations that are all recalculating on every replacement. If it is that much of a big deal, go to formulas, calculation options, set to manual until you're done with your find/replace.
|
|
#
?
Apr 11, 2019 05:53
|
|
