You just have to change to a Custom format – right click and select format and at the bottom of the list is custom. 0.00##\%;[Red](0.00##\%) The first part of custom format is your defined format you posted. Everything after the semicolon is for negative numbers. [RED] tells Excel to make the negative numbers red and … Read more
I think your problem is that it’s closing the document that calls the macro before sending the command to quit the application. Your solution in that case is to not send a command to close the workbook. Instead, you could set the “Saved” state of the workbook to true, which would circumvent any messages about … Read more
Given sheet 2: ColumnA ——- apple orange You can flag the rows in sheet 1 where a value exists in sheet 2: ColumnA ColumnB ——- ————– pear =IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),”Keep”,”Delete”) apple =IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),”Keep”,”Delete”) cherry =IF(ISERROR(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),”Keep”,”Delete”) orange =IF(ISERROR(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),”Keep”,”Delete”) plum =IF(ISERROR(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),”Keep”,”Delete”) The resulting data looks like this: ColumnA ColumnB ——- ————– pear Keep apple Delete cherry Keep orange Delete plum … Read more
You can convert the value to a date using a formula like this, next to the cell: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Where A1 is the field you need to convert. Alternatively, you could use this code in VBA: Sub ConvertYYYYMMDDToDate() Dim c As Range For Each c In Selection.Cells c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2)) … Read more
See if this works for you. All your data is in column A (beginning at row 1). In column B, row 1, enter =A1&”,” This will make cell B1 equal A1 with a comma appended. Now select cell B1 and drag from the bottom right of cell down through all your rows (this copies the … Read more
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
.xlsx loads 4 times longer than .xlsb and saves 2 times longer and has 1.5 times a bigger file. I tested this on a generated worksheet with 10’000 rows * 1’000 columns = 10’000’000 (10^7) cells of simple chained =…+1 formulas: ╭──────────────╥────────┬────────╮ │ ║ .xlsx │ .xlsb │ ╞══════════════╬════════╪════════╡ │ loading time ║ 165s │ … Read more
AFAIK you cannot use VBA Excel to create custom tab in the Excel ribbon. You can however hide/make visible a ribbon component using VBA. Additionally, the link that you mentioned above is for MS Project and not MS Excel. I create tabs for my Excel Applications/Add-Ins using this free utility called Custom UI Editor. Edit: … Read more
Excel (at least in Office 2007 on XP) can behave differently depending on whether a CSV file is imported by opening it from the File->Open menu or by double-clicking on the file in Explorer. I have a CSV file that is in UTF-8 encoding and contains newlines in some cells. If I open this file … Read more
“External table is not in the expected format.” typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0 Using the following connection string seems to fix most problems. public static string path = @”C:\src\RedirectApplication\RedirectApplication\301s.xlsx”; public static string connStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + … Read more