Test or check if sheet exists
Some folk dislike this approach because of an “inappropriate” use of error handling, but I think it’s considered acceptable in VBA… An alternative approach is to loop though all the sheets until you find a match.
excel
(Excel) Conditional Formatting based on Adjacent Cell Value
You need to take out the $ signs before the row numbers in the formula….and the row number used in the formula should correspond to the first row of data, so if you are applying this to the (“applies to”) range $B$2:$B$5 it must be this formula =$B2>$C2 by using that “relative” version rather than … Read more
How to fill color in a cell in VBA?
Non VBA Solution: Use Conditional Formatting rule with formula: =ISNA(A1) (to highlight cells with all errors – not only #N/A, use =ISERROR(A1)) VBA Solution: Your code loops through 50 mln cells. To reduce number of cells, I use .SpecialCells(xlCellTypeFormulas, 16) and .SpecialCells(xlCellTypeConstants, 16)to return only cells with errors (note, I’m using If cell.Text = “#N/A” … Read more
How to install Date Picker form control in Excel 2016
I use Excel from Office 365 and want to use date picker form. Unfortunately, it is not listed in the list of additional controls (More Controls). How to include it into spreadsheet that I am developing? Is there something that I need to download and install?
How to install Date Picker form control in Excel 2016
I use Excel from Office 365 and want to use date picker form. Unfortunately, it is not listed in the list of additional controls (More Controls). How to include it into spreadsheet that I am developing? Is there something that I need to download and install?
Row count where data exists
If you need VBA, you could do something quick like this: This will print the number of the last row with data in it. Obviously don’t need MsgBox in there if you’re using it for some other purpose, but lastRow will become that value nonetheless.
Loop without Do error
You need an: in your code.
VB: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)
First of all, try to run Visual Studio with the /ResetUserData command line argument. Read more about that in the Error “Unable to cast COM object…” when exporting to Microsoft Excel from Team Explorer 2008 article. Obviously you are trying to connect to a wrong Excel version. Looks like you have some extra windows registry … Read more
Adding a newline character within a cell (CSV)
This question was answered well at Can you encode CR/LF in into CSV files?. Consider also reverse engineering multiple lines in Excel. To embed a newline in an Excel cell, press Alt+Enter. Then save the file as a .csv. You’ll see that the double-quotes start on one line and each new line in the file … Read more
Error: “Could Not Find Installable ISAM”
Place single quotes around the Extended Properties: Try it, it really works.