Borrowing from [Excel VBA How to detect if something was pasted in a Worksheet][SO]. The `Workbook_SheetChange` event will fire for any change event on the page, including a paste.

From inside this event, you can then check if the last change was a paste by looking at the most recent entry in the Undo List History:



    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Dim lastAction As String
      
      ' Get the last action performed by user
      lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
    
      ' Check if the last action was a paste
      If Left(lastAction, 5) = "Paste" Then
        
        ' Do Stuff Here
        
      End If
    End Sub

[SO]: https://stackoverflow.com/q/27818152/1366033

I need to detect paste command of excel. Is there any work around which can tell us when user click the paste on the menu pop up from the left mosue button click. This is required me to execute a procedure if user click the paste menu item. Any help would be appreciated.

Regards,
Amit

how to detect paste event in excel

It's not particularly elegant, but this will most likely do the trick for you.  Just edit some of the hardcoded values like sheetname and make sure that you don't have more than 99 columns or 999 rows.  




    Public Sub FormatRowsInDateColumns()
    Dim header As String
    
    Worksheets("Sheet1").Activate
    
    'loop through columns
    For col = 1 To 99
    
        'check if header cell contains word date
        header = Cells(1, col).Value
        If InStr(1, header, "date", vbTextCompare) <> 0 Then
        
            'convert cell values to string
            For Row = 1 To 999
                'Formats value and perserves as text with an apostrophe
                Cells(Row, col).Value = "'" & Format(Cells(Row, col).Value, "yyyy/mm/dd")
            Next Row
          
            'set column format as text
            Columns(col).NumberFormat = "@"
        End If
    Next col
    
    End Sub

I am trying to create a macro that can find a particular string in a header, then format the cells in that column.  For an example, I have a header called "Purchase Date", "Cap Date", and "Exp Date".  I want to be able to find the first instance of "Date" used, format them as text, then find the next occurance and format, etc.

I have created one that will find only the first instance, then not look for any further.  Any idea?  I have looked up "Find" and "After", but cannot get them to function correctly.

Thanks for any help.

Excel VBA Macro Find and Format

Try This:

Open up your excel document and hit <kbd>Alt</kbd>+<kbd>F11</kbd> to bring up the VBA editor, insert a new module (if one doesn't already exist), open it up, and paste in the following code (they're custom user defined functions that we'll use in a little bit)


```vb
Function LEFTDELIMIT(ByVal text As String, ByVal delimiter As String)
    Dim position As Integer
    Dim leftText As String
    position = InStr(1, text, delimiter, vbTextCompare) - 1
    leftText = Left(text, position)
    LEFTDELIMIT = leftText
End Function

Function RIGHTDELIMIT(text, delimiter)
    Dim position As Integer
    Dim rightText As String
    position = Len(text) - Len(delimiter) - InStr(1, text, delimiter, vbTextCompare) + 1
    rightText = Right(text, position)
    RIGHTDELIMIT = rightText
End Function

Function NOERROR(text)
    If IsError(text) Then
        NOERROR = ""
    Else
        NOERROR = text
    End If
End Function
```

I'm guessing at this point that all of the text for your quizzes is in A1.  Go ahead and delimit that cell by commas as I specified earlier to get each question in its own column.  Since we want each of those questions to occupy its own row, highlight all of row 1 and copy and then paste special into A2 and select the option to transpose the values.  Now each question has it's own row.  Now what we'd like to do it give each answer choice its own column.  We can use the custom functions from before which allow you to get all the text to the left or right of a custom delimiter.

If the value of A2 is

* 1.Government as an art of governing refers to the process of A.ruling people in the society b.establishing political parties C. providing free education D. acquiring social skills

Then we'll fill out the other columns with the following code:

* B2: `=LEFTDELIMIT(A2,"A.")`
* C2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"A."),"B."))))`
* D2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"B."),"C."))))`
* E2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"C."),"D."))))`
* F2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"C."),"D."))))`
* G2: `=NOERROR(PROPER(TRIM(LEFTDELIMIT(RIGHTDELIMIT(A2,"D."),"E."))))`

This is making certain assumptions about how the incoming data is formatted. If this doesn't work, please show all of you work in an excel file that you can upload and distribute through any online file sharing host to get a better look at what particular errors might be tripping you up

I've got text file containing MCQ quizzes. Currently I have to edit all the quiz questions with delimiters (e.g TABS) before importing them into excel.  

I need an automated way to format the imported file into these columns:

```none
QuestionId, ExamType, Year, Subject, Question, Answer1, Answer2, Answer3, Answer4, Answer5, CorrectAnswer, Image 
```

without having to manually edit the text first.

Here's an example of the text I'm currently importing into excel.

```none
1.Government as an art of governing refers to the process of A.ruling people in the society b.establishing political parties C. providing free education D. acquiring social skills, 2. An essential feature of a State is A. availability of mineral resources B. developed infrastructure. C an organized system of laws D. developed markets.
```

I'd like to fit example 1 and 2 into the columns above. I have zipped up what I've been doing so that you can have a look.  I also included the raw quiz data so that you can have an idea what it is that i'm trying to force format 



force formatting imported text file in excel

I'd like to make sure Reminders in Outlook 2010 actually pop up when they are fired.

I'm working on a cleaner implementation than the one discussed in the SE question [How to make Outlook Calendar reminders stay on top in Windows 7](https://superuser.com/questions/251963/how-to-make-outlook-calendar-reminders-stay-on-top-in-windows-7/462453#462453)

I'm having finicky results when calling the [`Activate`](http://msdn.microsoft.com/en-us/library/ff869361(v=office.14).aspx) method, which should *activate an inspector window by bringing it to the foreground and setting keyboard focus.*

I'm calling `Activate` on what should be the Reminder Inspector item.  This is **not** an issue with calling [`Application.ActiveWindow`](http://msdn.microsoft.com/en-us/library/ff865654(v=office.14).aspx), which returns *nothing if no Outlook explorer or inspector is open*, because I'm handling for nulls and displaying a message box in the event it can't find anything.



    'ensures all reminder notices receive focus
    Private Sub Application_Reminder(ByVal Item As Object)
        If TypeOf Item Is AppointmentItem Then
            If Not (Application.ActiveWindow Is Nothing) Then
                Dim myOutlook As Object
                Set myOutlook = GetObject(, "Outlook.Application")
                If myOutlook.ActiveWindow.WindowState = olMinimized Then
                    'This works fine
                    Application.ActiveExplorer.Activate
                Else
                    'This call won't set window ontop
                    Application.ActiveExplorer.Activate
                End If
            End If
        End If
    End Sub

When the code calls `Activate`, if outlook is minimized the activated window will be pulled to the front and receive focus, but if it's in open in the background it won't.

Do I really have no other option than to call user32 in order to set the z-index of the alert window?

Outlook Inspector.Activate Method Not Bringing Window To Foreground

You can `Index` all of your data and then `Match` rows to the subset names and pull over each column.

For example, it would look something like this:

    =INDEX(All!A1:F4,MATCH(Subset!A2,All!A1:A4,0),2)

Let's break this down:

The index function takes the following arguments:

    Index(Array, Row_Number, Column_Number)

The array will be all the data you have, the row number we'll use a match function (below) to find which row to get data from, and the column number represents which column from the index to pull information from (we'll keep incrementing this to get all the columns).

To return the correct row, we'll use match

    Match(Lookup_Value, Lookup_Array, Match_Type)

For lookup value, we want to point it to the current row of the subset list, the lookup array will be all the names available from the index we defined previously in the master list, and the match type will equal 0 (for exact match)

Take a look at this [excel document](https://skydrive.live.com/redir?resid=9935281CD314F220!25984&authkey=!AIwnjuwPr8gGloQ)


So I have an excel dataset of participants that are possibly eligible to partake in a study  that consists of their names, age, telephone number, etc... like:

Name | Age | Phone | Email | Address | Studies Participated in 

I also have a column of names of participants that are eligible for a new study; it is a subset of the data in the name column.

What I need to do is select all of the participants whose names are in the subset column, and copy and paste all of their information in all of the columns into new columns. 

I have tried something like, selecting an empty column and using =IF($Name-column = $Subset-column, First-Column:Last-Column, "") But this only checks if the name in each cell of the Name Column matches the name in the Subset column. What I need is something that checks if any of the names in the Name column match those in the Subset Column, and if they do, then to copy that row to a new worksheet.

Filtering Dataset by values in one column

Sort normally by highlighting the column and Hit <kbd>Alt</kbd> + <kbd>A</kbd> + <kbd>SA</kbd>

-----

## Update:

[`'Excel sorts alphanumeric text left to right, character by character'`][AlphaNumericSort], so it may not properly order your numbers if combined into a single cell.  You should create an extra column with just the two letter code that you want to use for sorting by using `=LEFT(A2,2)` and copying all the way down.  Then do a multilayered sort by clicking the sort button, sorting first on the two digit code alphabetically and second on the number column:

![example](http://i.imgur.com/5M4WSCr.png)

[AlphaNumericSort]: http://support.microsoft.com/kb/322067#MT0

I have two columns I want to sort.  I want it to look exactly like below with Column A first sorted by its first two letters, then by Column B.


    Column A     -    Column B
    AB - Info         3339876
    AB - Data         3339877  
    AB - Data         3339878 
    AC - Info         3339123
    AC - Data         3339124
    AC - Info         3339125
    AD - Info         3339456
    AD - Info         3339457
    AD - Data         3339458

The first two letters of Column A are the MOST important and must be sorted by them first.  The information after the first two letters of Column A is irrelevant and does not matter. It is much more important that the Column B # data to be sorted in ascending order second (after the first two letters of column A)

Sorry for the confusion. Hopefully that clears things up.

Excel VBA - How to sort by first two letters of cell?

Yep, it is possible.

Using `MacroButton` with `NoMacro` merely serves to conveniently highlight the entire text area with a single click.  As with any highlighted object in word, when you start typing, it deletes the selected portion and formats the new text based on the style of the leftmost element (which in this case is the field itself - even though it's not displayed).  

To 'change' the styles, simply start off by highlighting the entire field and formatting however you'd like the result to end up. Then highlight just the text portion and format however you'd like the prompt to display.

When in edit mode, it should look like this:

![screenshot][screenshot]

[screenshot]: http://i.imgur.com/s0q5CPU.png

In MS word you can create a [placeholder field][placeholder field] by: 

* Inserting a new field (<kbd>Ctrl</kbd> + <kbd>F9</kbd>)   
* Typing <code>MacroButton NoMacro [<i>Enter Text Here</i>]</code> inside the brackets {}
* Then toggling it out of edit mode (<kbd>Alt</kbd> + <kbd>F9</kbd>) 

It will do this:

![screenshot][screenshot]

In my template, empty fields are highlighted in *blue*, but should turn *black* once filled in.

**Q**: Is there a way to change the font once someone has filled in a value?


[screenshot]: http://i.imgur.com/kEQD2cF.png
[placeholder field]: http://word.mvps.org/FAQs/TblsFldsFms/UsingMacroButton.htm

Change font when using MacroButton placeholder in MS Word

There are certain conventions in order to force a literal to a specific type from a generic (default) type: http://msdn.microsoft.com/en-us/library/dzy06xhf.aspx

Which leads to this code:

    Dim secondsInAWorkWeek As Long
    
    Let secondsInAWorkWeek = 60& * 60& * 8& * 5&

or:

    Const DAYS_IN_WEEK = 5&
    Const HOURS_IN_DAY = 8&
    Const MINUTES_IN_HOUR = 60&
    Const SECONDS_IN_MINUTE = 60&

    Dim secondsInAWorkWeek As Long

    Let secondsInAWorkWeek = _
         DAYS_IN_WEEK _
       * HOURS_IN_DAY _
       * MINUTES_IN_HOUR _
       * SECONDS_IN_MINUTE

A lot more to write, true, but is type safe, barely needs any more explanations/comments, and it will be easy to change when they'll vote for 10 hours workdays. :-)

Simple question that I can't find anywhere.  When I'm doing arithmetic, it seems the natural type is to treat value as 16Bit Integers.  

I'm trying to save the result of `60 * 60 * 8 * 5` into a long, but I get an `Overflow` error  before it even has a chance to save the number as a `Long`:



    Dim secondsInAWorkWeek As Long
    secondsInAWorkWeek = 60 * 60 * 8 * 5

Long should happily store anything up to 2<sup>31</sup> = 2,147,483,647

How can I perform the multiplication safely to convert into a long

Set Long Variable in VBA

### HTML Code

I'd try working out the code that manipulates the Document Object Model (DOM) in javascript in a web browser so you can make use of better web based debugging tools.  

There are **several issues here** that a console or debugger could help out with:

* You want to get the element ID `myPoints` but in HTML it's just called `points`
* You want to get the element by ID, but you've only set the name property - 
* As long as `name` is unique to the element, you don't need to search for a `td` first
* As you can see from `<input></input>`, input elements do not have `innerText` (the text inside the `><`). Instead they have a `value` attribute
* The element exposes it's attributes and other data through the properties on the object itself.  So you can check the input's value by just looking at `.value`

Here's a javascript example of what you're trying to do:





    var value = document.getElementsByName("points")[0].value;
    console.log(value);



    <input type="text" disabled="disabled" value="300" name="points" />
        



Open the console (<kbd>F12</kbd>), and you should see `300`

### VBA

To convert it to VBA code for Excel, just make sure you uses parentheses `()` for VB arrays instead of square brackets `[]` for JS arrays:



    myPoints = Trim(Doc.getElementsByName("points")(0).Value)

That should work just fine.

### References

Since I'm not sure at what point you're failing in VB, also make sure you have all the proper web references in place in your VBA script.

Go to Tools > References > and add "Microsoft HTML Object Library" and "Microsoft Internet Controls":

![References][1]


### Demo

I created a [**demo in plunker**][2] so there would be a live site to go against instead of example.com.

Paste the following code into excel and everything should work fine:



    Public Sub GetValueFromBrowser()
        Dim ie As Object
        Dim url As String
        Dim myPoints As String
        
        url = "http://run.plnkr.co/plunks/6UTb9kHRZ363Ivhh2BPE/"
        Set ie = CreateObject("InternetExplorer.Application")
        
        With ie
          .Visible = 0
          .navigate url
           While .Busy Or .readyState <> 4
             DoEvents
           Wend
        End With
        
        Dim Doc As HTMLDocument
        Set Doc = ie.document
        
        myPoints = Trim(Doc.getElementsByName("points")(0).Value)
        Range("A1").Value = myPoints
    
    End Sub

**Output**:

![output](http://i.imgur.com/d0VU3RV.png)

[1]: http://i.imgur.com/3INl86i.png
[2]: http://plnkr.co/edit/6UTb9kHRZ363Ivhh2BPE?p=preview

I am having difficult to retrieve value `300` from the input named `points`.

Here's my HTML and VBA code.

**HTML**:



    <td id="myPower_val_9" style="visibility: visible;">
        <input type="text" disabled="disabled" value="300" name="points"></input>
    </td>

**VBA**:



    Dim ie As Object
    Dim myPoints As String

    Set ie = CreateObject("InternetExplorer.Application")
  
    With ie
      .Visible = 0
      .navigate "www.example.com"
       
       While .Busy Or .readyState <> 4 
       DoEvents
       Wend

    End With
    
    Dim Doc As HTMLDocument
    Set Doc = ie.document

    myPoints = Trim(Doc.getElementsByTagName("td")(0).getElementById("myPoints").innerText)
    Range("A1").Value = myPoints

Get value from web document input element with VBA

From the article [Getting the 2nd matching value from a list using VLOOKUP formula][1],  you can create a helper column to affix the instance number of each value, to create unique id's.

For example, in Column C, add the following function:

    =A1&"-"&COUNTIF($A$1:A1,A1)

> **Note**: The relative reference on the count range will cause the applicable range to grow as it is dragged down.  The count of the items matching that cell in a range containing only that cell should always be one.  As it gets dragged down to include other cells, it will increment accordingly.

Then add the same thing in Column D to get the instances of cells in Column B:

    =B1&"-"&COUNTIF($B$1:B1,B1)

Finally, do the math you want to do in Column E like this:

    =IFERROR(MATCH(C1,D:D,0),0)

![Screenshot][2]


  [1]: http://chandoo.org/wp/2010/11/10/vlookup-second-value/
  [2]: http://i.stack.imgur.com/0tYef.png

I'm using excel and have two columns (A & B) with values

I want to search for each value in Column A and return the position in Column B.
I'm using this formula: 

    IFERROR(MATCH("Values in Column A";"Array = Column B";0);0)

The results are:  



    Column A  | Column B  | Column C
      1       |   4       |   2
      2       |   1       |   3
      3       |   2       |   4
      4       |   1       |   1
      1       |   2       |   2
              |   3       | 

It works fine if it doesn't encounter repeated values. However, I want it to encounter repeated values, so the formula should ignore the ones it was encountered before and go through the others. So the correct result should look like this:  



<pre><code>Column C  
  2  
  3  
  5  
  1  
  <b>4</b></code></pre>

Can you help me on this? Is there a VBA routine for this?