Posts Tagged ‘ Excel ’

Macros to “Paste as Text” for Word and Excel

2017/11/07
By
Modified: 2017/11/05
Macros to “Paste as Text” for Word and Excel

These two macros will save you a lot of time.  They used, when you need to copy something from Internet page into a Word or Excel document, but  you do not need all the colors and formatting.   You just need the text. Usually you would do this with Paste Special, but this is several awkward clicks and many seconds lost.  Over the course of the day this is a huge inefficiency. Let’s write a macro for Word first. As of 2012-01-11 WED, I adopted this little macro as final version: Sub PasteText() On Error GoTo Unicode Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _ Placement:=wdInLine, DisplayAsIcon:=False GoTo Done Unicode: Selection.PasteAndFormat (wdFormatPlainText) Done: End Sub This macro…

Read more »


Share Digital Certificate for VBA Between Several PC(s)

2017/10/18
By
Modified: 2017/09/16
Excel-Install-Certificate

This article is about time saving, when it comes to enable your macros in Excel every time you open the file. You do not want to allow all macro to run.  But if you don’t, you have to constantly enable your macros over and over again, when you open your Excel files.  To eliminate this, you can create a self-signed digital certificate and this work fine on one PC. But what, if you need to open the same files with macros from several PC(s) on your network.  I think I found a solution on how to create a certificate on one PC and then install that certificate on other PC(s). This…

Read more »

Keyboard Shortcuts for Next, Previous, Euro, Degree Symbols and Moving Around Inside a Worksheet

2017/10/15
By
Modified: 2017/11/05

I never can't remember the Notepad equivalent of "F3" (Find Next) in Word and Excel. Here are the keyboard shortcuts, that are most useful, but almost impossible to remember. Also I constantly need Euro currency symbol (€) and Temperature Degree symbol (°), when writing my travel notes about Europe.

Read more »

Apply Color Scales Conditional Formatting to Multiple Rows in Excel

2017/07/24
By
Modified: 2017/06/25
Excel - Color Scale Conditional Formatting

When you might need to apply color sales Conditional Formatting to multiple rows in Excel using a Macro?  We found that is most useful, when you have a perfectly arrange row-by-row conditional formatting, and you are trying to insert a new row.  You can try to fix the resulting formatting rules mess manually, or use this macro to set all formats in the range. First you n first row need to select the range were your conditional formatting will apply and clear all formatting rules. Next, you need to setup proper color scale conditional formatting in the first row of the range. Now, you should create a name range to reference…

Read more »

Count Number of Cell with Errors in Excel

2017/06/17
By
Modified: 2017/06/15

This problem occurs, when you have a column with a formula that sometimes returns an error.  That is very typical for all kind of look-up situations .  Let’s say, you have a column E with formula that returns good values and errors.  You need to count how many errors are in that column. Enter formula =SUM(IF(ISERROR(E2:E10),1)) and press Ctrl+Shift+Enter while still in editing mode. You should see two things.  First, your formula turns into {=SUM(IF(ISERROR(E2:E10),1))} with square brackets around it, and second, you got you a correct cells with errors count.  This is it. If you need to count cell without errors, you can use this formula: =SUM(IF( NOT( ISERROR(E2:E10)),1 ))…

Read more »