How to get the path of current worksheet in VBA?
Use Application.ActiveWorkbook.Path for just the path itself (without the workbook name) or Application.ActiveWorkbook.FullName for the path with the workbook name.
excel
Best way to do Version Control for MS Excel [closed]
I’ve just setup a spreadsheet that uses Bazaar, with manual checkin/out via TortiseBZR. Given that the topic helped me with the save portion, I wanted to post my solution here. The solution for me was to create a spreadsheet that exports all modules on save, and removes and re-imports the modules on open. Yes, this … Read more
How can I perform a reverse string search in Excel without using VBA?
This one is tested and does work (based on Brad’s original post): =RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,” “,”|”, LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))) If your original strings could contain a pipe “|” character, then replace both in the above with some other character that won’t appear in your source. (I suspect Brad’s original was broken because an unprintable character was removed in … Read more
Find last used cell in Excel VBA
NOTE: I intend to make this a “one stop post” where you can use the Correct way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information. Unreliable ways of finding … Read more
How to correctly display .csv files within Excel 2013?
Open the CSV file with a decent text editor like Notepad++ and add the following text in the first line: sep=, Now open it with excel again. This will set the separator as a comma, or you can change it to whatever you need.
Microsoft Excel mangles Diacritics in .csv files?
A correctly formatted UTF8 file can have a Byte Order Mark as its first three octets. These are the hex values 0xEF, 0xBB, 0xBF. These octets serve to mark the file as UTF8 (since they are not relevant as “byte order” information).1 If this BOM does not exist, the consumer/reader is left to infer the … Read more
How to keep one variable constant with other one changing with row in excel
Use this form: =(B0+4)/$A$0 The $ tells excel not to adjust that address while pasting the formula into new cells. Since you are dragging across rows, you really only need to freeze the row part: =(B0+4)/A$0 Keyboard Shortcuts Commenters helpfully pointed out that you can toggle relative addressing for a formula in the currently selected … Read more
Excel VBA – exit for loop
To exit your loop early you can use Exit For If [condition] Then Exit For
Excel: last character/string match in a string
I think I get what you mean. Let’s say for example you want the right-most \ in the following string (which is stored in cell A1): Drive:\Folder\SubFolder\Filename.ext To get the position of the last \, you would use this formula: =FIND(“@”,SUBSTITUTE(A1,”\”,”@”,(LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””)))/LEN(“\”))) That tells us the right-most \ is at character 24. It does this by … Read more
What is the difference between .text, .value, and .value2?
.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get #### .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean) .Value gives you the same as .Value2 except if the … Read more