top of page

VLookup

​

=VLOOKUP(A7, A2:B5, 2, FALSE).

​

Tip 1. Make sure the main column you want to match data to is in Ascending order.  Same with the main column you are retrieving data from.  In this example it would be A2

Tip 2. Better to have a separate spreadsheet with the data you want to get the information from rather than have it all on one sheet.

Tip 3. False means an exact match.  True means an approximate match.

Tip 4.  Of Course, when using this formula, make sure you change the column letter(s) to reflect your column

​

​

​

Formula to change a date to the month

​

=TEXT(C2, "mmmm")

​

Tip 1. This formula changes the month inside a formula to the text month of that date .

Tip 2. For example: 10/13/2020 just becomes "October"

Tip 3. Just drag the formula down the right side of the column with the dates you want to change.

​

Formula to calculate difference in years between dates​

​

=DATEDIF (A2,B2, "Y")

​

Tip 1. Put this formula in a third column for the results

Tip 2. An example of how this formula works: If the Start date of 01/01/2015 is in A2 and the end date of 05/04/2017 then the result would be 2 (for 2 years).

​

​

Formula to calculate date difference in months

​

=DATEDIF (A2,B2, "m")

​

Tip 1. Put this formula in a third column for the results

Tip 2. This formula is similar to the "Y" formula above.  However, if the Start date of 01/01/2015 is in A2 and the end date of 05/04/2017 then the result would be 28 (for 28 months).

​

​

Formula to calculate years, months and days between dates

​

=DATEDIF(A1,B1,"y")&" years"&", "&DATEDIF(A1,B1,"ym")&" months"&", "&DATEDIF(A1,B1,"md")&" days""

​

Tip 1. Put this formula in a third column for the results

Tip 2. This formula is basically a combination of the formulas from above.  However, if the Start date of 01/01/2015 is in A1 and the end date of 05/04/2017 is in B1, then the result would be 2 years, 4 months, 3 days.

​

​

Formula to Find Duplicates

​

=(COUNTIFS($N$2:N2,N2)>=2)+0

​

Tip 1. This formula searches for duplicate values based on a row you select.

Tip 2.  Add this value to the second row next to the column you want to find dups.

Tip 3.  Change the alphabet to their respective columns you want to search

Tip 4.  Right click and drag the handle of the cell with the formula down the sheet until all rows have been captured.

​

Concatenate Formula to Add Text cells together

​

=A1&” another cell”

​

For example: If A1 has "John" and D1 has "Doe", then the formula =A1 & D1 will result in "John Doe"

​

​

MID Function retrieves the middle contents of a cell  =MID(A1,6,3)

​

From a starting point within a cell, MID returns text left to right

For example, "John Doe Jr" in cell A1 will retrieve "Doe" in whatever cell the formula is placed.

​

​

RIGHT Function retrieves the right contents of a cell =RIGHT(A1,2)

​

From a starting point within a cell, RIGHT retrieves the contents after a comma

For example, "San Fran, CA" in cell A1 will retrieve "CA" in whatever cell the formula is placed.

​

​

Left Function to remove the left most word in a cell  =Left(A1,4)

​

For example: "John Doe" in cell A1 will move just the "John" with this function: =Left (A1, 4) in another cell

​

​

IF   =IF(C2=”Yes”,1,2)

​

Tip 1. Checks the value of what you have in a cell.  In this example if the cell says "Yes" then another cell would pop up with "1" as it's value.  If cell C2 doesn't have "Yes" then another cell would show "2".

​

Highlight Blank Cells  (Put this code in a macro then highlight the range of the data.  Then run the macro)

 

Sub Highlight_Blank_Cells()

  Dim DataSet As Range
  Set DataSet = Selection

  DataSet.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue

End Sub

Insert a blank row after every other row

(Put this code in a macro then select the range)

​

   Sub Insert_Row_After_Every_Other_Row()

      Dim rng As Range
       Dim CountRow As Integer
       Dim i As Integer

       Set rng = Selection
      CountRow = rng.EntireRow.Count

       For i = 1 To CountRow
      ActiveCell.EntireRow.Insert
      ActiveCell.Offset(2, 0).Select
    Next i
  End Sub

This Code loops through all the recipients stored in Excel sheet and sends an email to each one via Outlook

  Sub SendMassEmailsWithAttachments()

    Dim LastRow As Long

    Dim MailSubject As String

    Dim MailBody As String

    Dim RecipientEmail As String

    Dim RecipientName As String

    Dim AttachmentPath As String

    Dim EmailCount As Long

    Dim Result As String

 

 

    ' Determine the operating system

    Dim isMacOS As Boolean

    isMacOS = (Application.OperatingSystem Like "*Mac*")

 

 

    ' Get the common details from the second row (headers)

    With ThisWorkbook.Sheets("Sheet1")

        MailSubject = .Cells(2, 3).Value

        MailBody = .Cells(2, 4).Value

        AttachmentPath = .Cells(2, 5).Value

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    End With

 

 

    ' Loop through each row in your Excel sheet, starting from the third row

    For Each cell In Sheets("Sheet1").Range("A3:A" & LastRow)

        RecipientName = cell.Offset(0, 1).Value

        RecipientEmail = cell.Value

 

 

        If isMacOS Then

            ' Use AppleScript for macOS

            Dim Parameters As String

            Parameters = RecipientEmail & "|" & MailSubject & "|" & MailBody & "|" & AttachmentPath

            Result = RunMacScript("SendOutlookEmail.scpt", "sendEmail", Parameters)

 

 

            If Result = "Done" Then

                EmailCount = EmailCount + 1

            End If

        Else

            ' Use Windows-based method

            Dim OutlookApp As Object

            Dim OutlookMail As Object

            Set OutlookApp = CreateObject("Outlook.Application")

            Set OutlookMail = OutlookApp.CreateItem(0)

 

 

            ' Fill in email details

            With OutlookMail

                .To = RecipientEmail

                .Subject = MailSubject

                .Body = MailBody

                If AttachmentPath <> "" Then

                    .Attachments.Add AttachmentPath

                End If

                .Send

            End With

 

 

            ' Release the email object and Outlook application object

            Set OutlookMail = Nothing

            Set OutlookApp = Nothing

 

 

            EmailCount = EmailCount + 1

        End If

    Next cell

 

 

    ' Display the final result message

    MsgBox "Done" & vbCrLf & EmailCount & " emails sent"

  End Sub

 

 

  Function RunMacScript(scriptName As String, handler As String, params As String) As String

    On Error Resume Next

    RunMacScript = AppleScriptTask(scriptName, handler, params)

    On Error GoTo 0

  End Function

Code to select cells (https://wellsr.com/)

Sub SelectDemo() Range("A1:B3").Select End Sub

Code to color a cell or cells (https://wellsr.com/)

Sub SelectionDemo()
Range("A1").Select
Selection.Interior.Color = vbYellow
End Sub

Code to check for positive of negative numbers (https://wellsr.com/)

Sub SgnFunctionDemo()
myval = Range("A1")
If IsNumeric(myval) Then
    If Sgn(myval) = 1 Then
        MsgBox ("Cell A1 is positive")
    ElseIf Sgn(myval) = 0 Then
        MsgBox ("Cell A1 is 0 or blank")
    ElseIf Sgn(myval) = -1 Then
        MsgBox ("Cell A1 is negative")
    End If
Else
    MsgBox ("Cell A1 is not numeric")
End If
End Sub

Code to copy and paste to a range (https://wellsr.com/)

Sub RangeDemo()
Range("A1:B3") = 7
Range("A1:B3").Copy Destination:=Range("a5")
End Sub

Code to print a range (https://wellsr.com/)

Sub RangeDemo()
Range("A1:B3") = 7
Range("A1:B3").PrintOut
End Sub

Code to create a splash screen - First put this behind a userfform

​

 https://wellsr.com/vba/2016/excel/create-awesome-excel-splash-screen-for-your-spreadsheet/

'PLACE IN YOUR USERFORM CODE
Private Sub UserForm_Activate()
    Application.Wait (Now + TimeValue("00:00:01"))
    SplashUserForm.Label1.Caption = "Loading Data..."
    SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:01"))
    SplashUserForm.Label1.Caption = "Creating Forms..."
    SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:01"))
    SplashUserForm.Label1.Caption = "Opening..."
    SplashUserForm.Repaint
    Application.Wait (Now + TimeValue("00:00:01"))
    Unload SplashUserForm
End Sub

Code to create a splash screen - Next,  put this in a standard module

'PLACE IN A STANDARD MODULE
Option Explicit
Option Private Module

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
Public Declare Function GetWindowLong _
                       Lib "user32" Alias "GetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong _
                       Lib "user32" Alias "SetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long, _
                       ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar _
                       Lib "user32" ( _
                       ByVal hWnd As Long) As Long
Public Declare Function FindWindowA _
                       Lib "user32" (ByVal lpClassName As String, _
                       ByVal lpWindowName As String) As Long

Sub HideTitleBar(frm As Object)
    Dim lngWindow As Long
    Dim lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

Code to create a splash screen - Finally go to THIS WORKBOOK and paste this in the PROJECT EXPLORER PANE

'PLACE IN ThisWorkbook SHEET MODULE
Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    ActiveWindow.Visible = False
    SplashUserForm.Show
    Windows(ThisWorkbook.Name).Visible = True
    Application.ScreenUpdating = True
End Sub

bottom of page