Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

When I create an invoice for my customers, alot of my customers decide to pay me over time. Those that opt in to pay over time make a monthly payment to me. As I run some reports, I am trying to figure out two things: 1. How many months does it take for each invoice to get paid in full? 2. In how many months is 1/2 the invoice amount paid. I want to know this because when 1/2 the invoice is paid, I offer them other discounts. I attached a sample of my report I export. I added two columns (B and C). I put the answers in those columns that I am hoping you can help me with the formula. So, can you please help me with the formulas in columns B and C? Thank you in advance!!!
I am using this code to put time stamp in cell B5:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.

    With Me
        Set KeyCells = .Range("D5")
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            ' we will put time stamp in cell B5
            Application.EnableEvents = False
            .Cells(5, "B").Value = Now()
            .Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
            Application.EnableEvents = True
        End If

    End With

End Sub

It works ok however I need to concatenate  "Run at: "  to this time stamp.
Any idea.
We have a 700+ line excel sheet with our targeted sales locations by city and state. Is there a product that can import our excel data (sales person, city and state) and create an interactive US map of all of the excel data sales locations which can then be interactively used during presentations?

For example, should we present the west coast sales region, we can zoom into that map area and then click on any of the cities that we are targeting.

I posted this question before but still not get good answer.

I created excel file (Excel 2016) with 5 worksheet.
Each worksheet is based on  Microsoft query  which run store procedure on SQL Server 2012  as data  source.
Store procedures have one parameter -  date.

When I created first Microsoft Query in Excel I did it on next way:
Open new workbook > on menu go to Data >Get Data> From Other Source>From Microsoft Query > Chose Data Source.
Here I created new data source gave name “ReportsDS”  and selected Driver is SQL Server Native Client 11.0. I was prompted for password as I do not want to have hard coded password or password in connection string.
I went through prompted steps in creating Microsoft Query. I put in SQL box call to my store procedure with parameter using proper syntax:
{Call [MyDatawarehouse].[dbo].My_Store_Procedure_1(?)}

On Connection Property form at Definition tab > parameter button > Parameter Value is obtained  I selected location worksheet_1 cell D5 and clicked option
"Refresh automatically when cell value changes.

For each of these five worksheet I went through the same steps.
Only difference is that I used different store procedure for data source for each of them.
All store procedure are from the same database and all sp have one date parameter which value was provided from location  on worksheet_1 cell D5.
So when new date  on worksheet_1 D5 cell is entered  all 5 worksheet are updated at that moment.

My problem with …
The variables lRow and lCol will only show the correct information on the first loop, the following loops is a report on the first loop. A similar thing happens if I use LastRow variable.

Public Function ExportToExcel(ReportFilterType)
' Tools > References > Microsoft Excel Object Library

On Error GoTo Error_Message
'Requires Reference to Microsoft Excel Object Library
 Dim rsMainList As DAO.Recordset
 Dim rsExport01 As DAO.Recordset
 Dim rsExport02 As DAO.Recordset
 Dim rsExport03 As DAO.Recordset
 Dim sqlExport01 As String, iCol01
 Dim sqlExport02 As String, iCol02
 Dim sqlExport03 As String, iCol03
 Dim xlObj As Object
 Dim xSheet01 As Object 'Upload File
 Dim xSheet02 As Object 'Batch Summary
 Dim xSheet03 As Object 'Batch Detail
 Dim strFolder As String
 Dim LastRow As Long
 On Error Resume Next
 DoCmd.Hourglass True
 If rsMainList.EOF Then Exit Function
 strFolder = CurrentProject.Path & "\"
 Do Until rsMainList.EOF
    Set xlObj = CreateObject("Excel.Application")

    sqlExport01 = "SELECT Database, [Posting Date], [Account Combination], Co, Div, Fun, Rig, Job, AFE, Maj, Min, [I/C], [Debit Amount], " + _
    "[Credit Amount], [Total Amount],[GL Description], [Upload ID], [Batch ID], [Currency ID] " + _
I have a table with columns A,B,C,D,E,F,G.  Column A-F are categories and are sorted first on A, 2nd on B and so on through column F.  G is a price.

I want to count and total column G for rows that match on all columns A-F.

So for a table that contains columns like Country      State      County      City      Street House      Price(actual column names are different and make little sense to someone outside the company),
how do I count the number of houses on a street and subtotal the price?  The problem is that you could have two cities with the same street number.  These two cities could be adjacent in the sorted rows.  So I can't just count and subtotal when the street changes since sometimes the streets will be the  same while the cities are different.
I have issue with change event in Excel Worksheet.
Excel 2016.

In my workbook I have several worksheet.
On first one I have two cells that I am using as parameters for my data source query  at the same time when I change value in one of two cells I want to use that  as trigger to pull date and time when this happened and put that on spreadsheet.

Here is code of Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("G4:G5")

    If Not Application.Intersect(KeyCells, Range("G4:G5")) Is Nothing Then

        ' we will put time stamp in cell B5
        Cells(5, "B").Value = Date & " " & Time
        Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
    End If

End Sub

Event is fired only when I first time open workbook and change value in those two cells.
After that event is not fired data source is refreshed and I got new data in my spreadsheet but change event is not triggered for date time display.

What could be issue.
Hello, so I have this Excel table (facsimile):

Initial table
I've been asked to make it so that the table can be printed using the second column as a way to split it into more tables. So, the data should be grouped depending on the second column value.

So this table should be printed on 3 pages, and we'd get these 3 tables, one on each page:

3 tables, grouped by the value in the second column
Does Excel have some quick way to do this which I don't know of? Do you have any suggestions?


I have a workbook that nearly does what I want it to do but I think the formula corrupt.

In sheet called Tableau if data missing from Watford sheet if comes up "NO MATCH"  and when data missing from Tableau sheet but in watford sheet it should also show as "NO MATCH"  but at moment it is showing REF!

Im also trying to set it up so that I get rid of sheet called "new rec" and I dont want any references to this.

Any Ideas?

File attached with formulas
Hello experts,

The following procedure allows me to add string on multiple columns.

Sub Add_Specific_String_Multiple_Columns()
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
    Dim strToAdd As String
    On Error GoTo Error_Routine

    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")

    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    If IsNumeric(intWhich_temp) Then intWhich = intWhich_temp

    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
        & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    strToAdd = InputBox("Input the value that you want to add.", "String To Add!")
    If strToAdd = "" Then
        MsgBox "No input!", vbExclamation
        Exit Sub
    End If
    For Each strCol In Split(strColList, ";")

Open in new window

When I use the following equation it works great

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(2018,11,20)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(2018,11,21)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

But if I try to use this equation it breaks:

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(c5)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(h5)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

I am TRYING to refer to another cell to grab the date.  What am I doing wrong??
Is there a way to copy formulas from one workbook to another WITHOUT copying the file reference and if so how? Thank you
Hello experts,
The following procedure allows me to identify values which are in based on two different ranges.
Sub Is_In()
    Dim wsSource As Worksheet
    Dim wsComaparison As Worksheet
    Dim rngSource As Range
    Dim ComparisonRange As Range
    Dim rCl As Range
    Dim LRSource As Long
    Dim LRComparison As Long
    Dim colSource As Long
    Dim colComparison As Long
    Dim cntMatch As Long
    Application.DisplayAlerts = False
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Please Select any cell in your range source, in this range you will find the cells which are in your range to compare", Title:="Source Range Selection", Type:=8)
    Set ComparisonRange = Application.InputBox(Prompt:="Please Select any cell in the Range to compare", Title:="Select Range To Compare With", Type:=8)
    On Error GoTo 0
    If rngSource Is Nothing Then
        MsgBox "You didn't select any Source Range to compare.", vbExclamation
        Exit Sub
    ElseIf ComparisonRange Is Nothing Then
        MsgBox "You didn't select any Comparison Range to compare it with Source Range.", vbExclamation
        Exit Sub
    End If
    Set wsSource = rngSource.Parent
    Set wsComaparison = ComparisonRange.Parent
    colSource = rngSource.Column
    colComparison = ComparisonRange.Column
    LRSource = wsSource.Cells(Rows.Count, colSource).End(xlUp).Row
    LRComparison = wsComaparison.Cells(Rows.Count, 

Open in new window

I have a small table (another learning exercise for me),  In column A, I have Order Description.  In column B, I have Costs.
 I want to capture the order description using Xl Down, because the order may have 1 or 50 entries.. and that works fine.. Once captured, I am having a little trouble with moving it to the OrderDataBase page, (page 2).
  I want to use on Page 2, column A, XlUp to get to the next free row, and I can, but I don't know how to paste the information from the clipboard, nor do I know if that is the best method.
I know this is very simple for you guys but after hours of searching, I am lost..

Excel Search Cells by Format [Fill Color] follows no discernible directional pattern: across column, across column, down columns, down columns, across columns.

Attached is an example sheet.  If I run the Format Fill search for yellow fill, I get this series of results:
Z5, AH11, X25, X26, AX26, AX28, K34, O37, X60, AZ61, V62, BO36

I need the search to go down each column so that the cells would appear in this order:
K34, O37, V62, X25, X26, X60, Z5, AH11, AX26, AX28, AZ61, BO36

Have group of extracted quotes arranged thematically in columns.  Have highlighted some that I that I want to quickly review.  Need to be able to focus to each highlighted cell down the columns and then move over to the next column with a highlighted cell at the lowest number / nearest-to-the-top cell.  Continue down.  Move to the top of the next column with a highlighted cell.  

So, left to right, down, over and up columns, according to cells with the fill color in question.

Any way to do that?

Many thanks,

I have a spread sheet that I need to sum a group of numbers based on shift start and shift end.

The shift starts at 11pm and ends at 7am

I have attached a sample spread sheet.

I need to sum the values in H "ToteNetWeight" for shift starting on 1-1-2018 at 11pm and ending on 1-2-2018 at 7am.  AND I want to sum only the values in H IF the value in I " ToteTypeName" has the "L8P Graves" value in it.

How can I do this as simply as possible??

Hello experts,

The following procedure allows me to extract string in between parenthesis.

Sub Extract_String_Between_Parenthesis()

    Dim strColInput As String
    Dim strColOutput As String
    Dim lngLastRow As Long
    Dim strTemp As String
    Dim lngRow As Long
    Dim strIO
    On Error GoTo Error_Routine
    strIO = InputBox("Please enter the column letters separated by a colon for the data in the form 'InputColum:OutputColumn'", "Choose Column Letters", "A:B")
    strColInput = Split(strIO, ":")(0)
    strColOutput = Split(strIO, ":")(1)
    lngLastRow = Range(strColInput & "1048576").End(xlUp).Row
    For lngRow = 1 To lngLastRow
        If InStr(1, Cells(lngRow, strColInput), "(") > 0 And InStr(1, Cells(lngRow, strColInput), ")") > 0 Then
            strTemp = Split(Cells(lngRow, strColInput), "(")(1)
            Cells(lngRow, strColOutput).Value = Replace(strTemp, ")", "")
        End If
    Exit Sub
    MsgBox Err.Description, vbExclamation, "Something went wrong!"
End Sub

Open in new window

I would like to add the following improvement:
Instead of exporting just string between parenthesis I would like to export string in between other characters.

The idea is to put two initial inputbox.
1-Inputbox "Enter the first character involved by the extract process" Example: "("
2-Inputbox "Enter the last character involved by the extract process" Example: ")"

If I can put just one inputbox like this:
1-Inputbox "Enter the first character and last character separated by ";" involved by the extract process" Example: "(;)"
It would be better.

If you have questions, please contact me.

Thank you for your help.
Hello experts,

The following procedure allows me to create folders:

Sub Create_Folders()

    Dim xDir As String
    Dim fso As Object
    Dim oWS As Worksheet
    Dim lngLastRow As Long, i As Long
    Dim MsgTxt As String, RefFolder As String
    Dim ErrorsFound As Boolean

    On Error GoTo 0

    Set fso = CreateObject("Scripting.FileSystemObject")

    Application.ScreenUpdating = False

    Set oWS = ActiveSheet

    lngLastRow = oWS.Range("A" & "1048576").End(xlUp).Row
    MsgTxt = "Before running this procedure, make sure to report following information as of row 2:"
    MsgTxt = MsgTxt & vbNewLine & " 1-Column A: Path name"

    Ans = MsgBox(MsgTxt, vbQuestion + vbYesNo, "Confirm Please!")
    If Ans = vbNo Then Exit Sub
    RefFolder = InputBox("Report reference folder in which you want to create folders reported in column A")

    'If Cancel button is activated exit sub
    If RefFolder = "" Then
        MsgBox "Procedure has been cancelled"
        Exit Sub
    End If

    If Not fso.FolderExists(RefFolder) Then
        MsgBox "unable to proceed as initial folder doesn't exist"
        Exit Sub
    End If

    If Not fso.FolderExists(RefFolder) Then
        MsgBox "unable to proceed as initial folder doesn't exist"
        Exit Sub
    End If

    ErrorsFound = False

    For i = 2 To lngLastRow
        'change the path on the next line where you want to create the folders
        xDir = RefFolder & Application.PathSeparator & 

Open in new window

When I save a new 2013 Excel file for the first time it always tries to save to some cloud location. My "Save" settings in options are set properly with my hard drive location. How can I change this? Thank you.
Hi. What Excel VBA code would I use to test if a value of 87 is present in a column named Col1 in a table named Table1 in an Access database in the same folder as the spreadsheet

Is is possible to configure an Excel Pivot Table to only show the rows where there is a value in one of Columns.

If we start with this data
Example of data
We can create a pivot table that looks like this
Example Pivot Tableusing these settings
Pivot Table Settings
What I would like to be able to do is only show the rows where the Client has only purchased Product A.  this would result in only Mark and Peter appearing.
Result of the required filter
How do I change the following code :

Sub InsertMonth() 'insert MONTH corresponding to the date
Dim LastRow As Long

      LastRow = Range("A" & Rows.count).End(xlUp).Row
      Range("B1").EntireColumn.Insert Shift:=xlToRight

      Range("B1").Value = "Month"
      With Range("B2:B" & LastRow)
            .Formula = "=TEXT(A2, ""mmm"")"
            .Value = .Value       ' replace formulas with values - optional
      End With

End Sub

Open in new window

to get Excel to insert the column "YYYYMM" in the first column A regardless of the position of the "Date" Column ?

In the above script, one would need to place the "Date" Column in Column A , and then it will insert Month in Column B, but the month is in text format.

Before and After
-Date is in the format of mm/dd/yyyy
- there are data from row 2 onwards
- objective is re-arrange the data in the desired columns (position) and the have the column "Month" inserted.


I am using the following Excel formula where the result is sometimes #N/A. Can I format the cell
to rather show a blank? If not how would I adjust my formula to show a blank instead of this?



I have an Excel sheet with simple formulas that point to another sheet eg cell A2 simply has the formula =Entry!B2
My problem, as per the following image, is that zeros are showing up for blanks on the source sheet. How do I adjust
my formula to show blanks? Thanks

In Excel what is the fastest method that will make it so whenever a user selects a cell the whole row is highlighted. Please show me methods that don't use conditional formatting.