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

I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence  of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
Free Tool: Site Down Detector
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi There,

I have an excel utility that creates a txt file containing metadata to be loaded into another system.  This has started to fail after the users upgrade to O365 / Excel 2016.

It creates the text file by creating an object referencing itself as a recordset:

Data Source=C:\Users\Jbloggs\Documents\user\ACTIVE\2017 07 - A&B\03 - Build\ExportFile.xlsm;
Extended Properties="Excel 12.0 Xml;

However when this code is opened I receive the following error:

Code Running

Error Message

I have tried this on another machine with Excel 2016 and this code works without issue.

Any guidance would be greatly appreciated.

Many Thanks,
Need to have an query for 4 num exact to be show in  NUM sheet ans NUM_VER sheet (first to get done)

then show the 13 set brging location 1

In this file is very importante show

column F location 1 in yellow background (to be audit)

column L

column M

column N
The attached example file contains three identifier columns with the main identifier being a code in column C.
There are duplicate codes at random down column C - Rows 3 & 4,  114 & 115, 185 & 186, and 196 & 197, are examples shown in red.

The objective is to have the contents of the lower duplicate row added to the contents of the cell above and then the lower row to be deleted.
The columns affected are from D to KQ.

The actual files have about 60,000 rows.
I have two tabs in the attached file. I am trying to create a "Result sheet" based off my  "Data sheet". I need a standard subtotal for every "Sales Person" in these fields:

1) "Amount Collected"
2) "Amount Put Back"
3) "Amount tendered"
4) "Returns"
5) "Amount"
6) "Amount removed".

But the "Final Amount" field is a different calculation. It needs to be the last "Final Amount" that was greater than 0 for that "Sales Person", BUT if there were any "Amount Put Back" AFTER the date of the last "Final Amount", these need to be added to the "Final Amount". But note that is said AFTER. If these "Amount Put Back" were BEFORE the last "Final Amount" that was greater than 0, they do not need to be added. Individual explanations for each "Final Amount" is in the spreadsheet.
need query column I location 1

and show 13 set  coulmns L , M , N
in  sheet  ORD,  ORD_VER
need query column G location 7

and show 13 set  coulmns L , M , N
in  sheet  NUM-M  and NUM-M_VER
This file is an num 3  

 need look for column F  location 1 set of 13  to show coulmn F  column L column M and column N Data
I have this formula in the cell:

='Loppöversikt och loppanalyser'!C2

And it should have retrieved the date "20170620" from C2 on that tab. But instead, "2E+07" is written in the cell. I've tried to change the formatting in the cell, but nothing is accepted by Excel.
if someone has CTC 4.7 L per annum(Delhi, India),
what should be salary breakup per month?
On Demand Webinar: Networking for the Cloud Era
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

The following code helps me convert date values into dd-mmm-yyyy, what I'd like to do in addition is then convert the values into text.

So it would stay as dd-mmm-yyyy but the cell value would be text not a date format. I thought one way maybe to text special, but at the moment I have to paste the values into notepad and then pre format the cells so that they enter as text.

Private Sub CommandButton1_Click()
Dim cell As Range
For Each cell In Selection
cell.NumberFormat = "dd-mmm-yyyy"
Next cell

End Sub

Many thanks for your help.
need look for column F location 1 set of 13  to show column L column M and column N
Hello Experts,

Attached are two files, the MS Access db where I am trying to create multiple records based on the comments fields in the one record. The MS Excel spreadsheet contains a sample of what I am trying to accomplish.

I am able to move the records to a new table, but I am lost on the step where I want to duplicate the record based on how many comments are in the note field.

The attached Excel spreadsheet is a sample of what I am trying to accomplish based on MS Access ID=621.

Can you please help me out?

Thank you,

I have an Excel sheet that I am trying to find and hide rows where cells in column A contain the symbol <.
The problem is that the cell values are defined as one long string, and it is not finding the symbol by it's self.
For instance, in the following data:

1 Random Text
2 Also Random Text
3 <p>Another Random Text Also</p>
4 More Random Text
5<p>Last Random Texts</p>

The code is supposed to resolve this into:

1 Random Text
2 Also Random Text
4 More Random Text

This is what I have so far:
Sub Button1_Click()
Dim N As Long, I As Long, j As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
j = 2
For I = 2 To N
If InStr(1, cell, "<", 1) then
Selection.EntireRow.Hidden = True
Selection.EntireRow.Hidden = False
Next I
End Sub

Open in new window


I would like to sort cells in highest order and display top four variable headings.
Please refer to attached sheet
Many thanks
I have an Excel 2013 spreadsheet with over 3,000 entries.  The spreadsheet has customer numbers and Company Names (see attached sample) - my original spreadsheet has many more columns but I tried to simplify for explanation purposes. On the sample I have attached there are five customer numbers (3, 4, 5, 6, 7) and only two company names.  I am trying to find a quick way to determine what customer numbers are listed with both companies.  So in my example, customer #4, #5 and #7 are the customers that are associated with both companies.  That is what I am trying to extract.  I don't need to know the customer numbers that are only associated with one company. I need to pull only the customer numbers that are listed for both companies.  Any help is appreciated.  Thank you.
how are the steps to put this in any file i need
i have this file  working  but need paste the data in destination

after click the button
need paste the data in destination
Someone has created this Macro for me to import data from a number of workbooks into the one in which the macro is running.  All workbooks are in the same folder.  Trouble is, although this runs fine on a Windows PC (Excel 2016), it doesn't run on a Mac (also 2016).  It comes up with an "Run-time error '53' File not found".

Incidentally, the last line calls another Macro I recorded that just Word Wraps a column in the workbook.  The last line of that calls another macro that adds in some text to say that the macro has run successfully.  I don't think the error is about the calls but if you can suggest how to add the lines into the first Macro, below, that would be great.  

Any ideas what is preventing this run on a Mac?

Sub ConsolidateResults()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim rngDst As Range
Dim rngCopy As Range
Dim strFileName As String
Dim strPath As String

    Application.ScreenUpdating = False

    strPath = ThisWorkbook.Path & Application.PathSeparator
    Set wbDst = ThisWorkbook
    Set rngDst = wbDst.Sheets("Input Data").Range("F5")

    strFileName = Dir(strPath & "*.xlsx")


        Set wbSrc = Workbooks.Open(strPath & Application.PathSeparator & strFileName)

        Set rngCopy = wbSrc.Worksheets("Copy").Range("F1:F40")
        rngDst.PasteSpecial xlPasteValues

        wbSrc.Close SaveChanges:=False

        Set wbSrc = Nothing

        Set rngDst = …
Revamp Your Training Process
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

I have a template that is used monthly and each time its used, we create a new one for the upcoming month.
On several worksheets, we need to unprotect it, clear data from the entry fields, and then re-protect the workbook before saving.  I want to set up a button that will allow the end user to click it and have the macro perform unprotecting the worksheet, clearing the data and then re-protecting all with one click.  I don't mind the password showing in the code because I don't want the end user to have to enter a password to do this.

Do you have some code for this?  Here is an example of the data clearing code I will be using.  I just need to know how to attach code at the beginning and end to initially unprotect the worksheet and then protect it again after the data clearing has been done.

    ActiveWindow.SmallScroll Down:=39
    ActiveWindow.SmallScroll Down:=24
    ActiveWindow.SmallScroll Down:=-75
End Sub
How do i write a nested if statement for the following?

If greater than  14 but less than 16 return £10000
BUT If greater than  16 but less than 18 return £20000
AND if greater than 18 return £30000

I tried =IF(AND(C9>=14,C9<16),10000,0)
Which returns £10000 however when i try nest it, everything just returns #VALUE?

Thank you
Hello Experts,

I am trying to write  vba code to auto indent cells based on cell content.
Capture.PNGAsm Part: indent by 2
Operation: Indent by 3
Something like that
See example. How can I hide the duplicate rows so only the first one displays and the others can be displayed by clicking a + mark that would appear at the very left of the displayed row?

Duplicates Example

I am looking for help to write some code so that I have a listbox (or similar) that can enter a date into a cell.

Unfortunately my work IT department have blocked 6.0 objects, so can't use any of the pop-up calendars available, therefore, I would like a userform with 3 entry points; day number, month and year, to be selected (via dropdown or similar) and then entered into the cell in the format DD/MM/YY.

It's Friday and I'm full of cold and my brain can't take attempting the different answers that come up in google...please can someone help me!!!!!

Hi Guys,

I need your urgent help .

There is a workbook 1 with Sheet1 and Sheet2
I have added some value in sheet1
then In sheet2 I have added a formula like "=B3+Sheet1!B4" and "=Sheet1!D4+Sheet2!E3"

and saved the workbook as Test1.xlsx

now from "Test1.xlsx" , I have copied the sheet1 data and pasted it in New Workbook "Sheet1" sheet. And then copy the Sheet2 data from Test1.xlsx and pasted it in "Sheet2" sheet in new workbook.

When I have pasted the sheet2 data it shows me the formula like "=B3+[Test1.xlsx]Sheet1!B4" and "=Sheet1!D4+Sheet2!E3"

Note :- In both the workbook the sheets name are same.

My Question is I want to update the formula in new workbook without linking with "Test1.xlsx" workbook.

i.e my formula in new workbook in "Sheet2" would be  "=B3+Sheet1!B4" and "=D4+Sheet2!E3".

I want to do this task without "Edit" link update option or without replace logic. or Without any third party tool.

Is there any other option or way to remove the link and keep the formula only?

Appreciate your help :)


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.