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.

I have a timesheet workbook. On each sheet, it totals the hours.  I want to take the first sheet and aggregate all the totals from the same field in all the sheets.  I wastimesheetQuestion.png looking to get total hours for all weeks and put it on the first sheet.  See Image.
why am I getting leading apostrophies in my columns even after I delete them? will that affect my export into the database
I have an excel wookbook that contains 2 different worksheets.  The worksheet1 has all the information.  Worksheet2 has cells taken from worksheet1.  I want to be able to have the 2 cells that are highlighted in the worksheet1.png to be automatically copied to the next available cells in worksheet2 when the amount is entered in the cell which in this case is -15332.00 otherwise it won't enter anything.  Hope this makes sense.  Any suggestions??worksheet1.pngworksheet2.png
Hello all

Below is a Sample that I have in multiple spreadsheets

AlarmID      Zone      Stime      Etime      StoreOpen      Comments
M1253      Bdoor      1:53      1:59      Y      
M5243      Bdoor      1:02      1:03      N                     Please research
M8696      Fdoor      1:04      1:06      N                     Second time this week
M4586      Roof      3:48      3:53      Y      

I would like to add a button to the spreadsheets that would generate 1 email with only the alarms there the StoreOpen column is N.  I think some sort of loop through a range in excel would work.

For example with the information above the email could contain 2 records in the body of the email and would look something like below  (the most should only be 5 so not too long):

Subject: Alarm Alert

Please review the following Alarms:

AlarmID: M5243
Zone: Bdoor
Comments: Please Research

AlarmID: M8696
Stime: 1:04
ETime 1:06
Comments: Second Time this week
Hi experts,

I have an Excel sheet where I show the comment field on click and resize the comment field at the same time. We use this to add comments to a project overview. I would however in addition like to remove the author on top or change it to the word "Actions" for example. Is it possible to find bold text in the comment field and replace it with other text?

Best regards,

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range
' Turn off the comment indicator
Application.DisplayCommentIndicator = xlNoIndicator

For Each myCell In Target.Cells
    If myCell.Comment Is Nothing Then
        ' Do nothing
        myCell.Comment.Visible = True 'On cell click the comment is visable
        'HOW TO Replace the author with something else
        myCell.Comment.Shape.Fill.ForeColor.RGB = RGB(0, 255, 0)
        myCell.Comment.Shape.Width = 400
        myCell.Comment.Shape.Height = 150
        myCell.Comment.Shape.Top = myCell.Comment.Parent.Top + 15 'Increase to move the comment down
        myCell.Comment.Shape.Left = myCell.Comment.Parent.Offset(0, 1).Left - 50 'Increase to move the comment to the right
    End If
Next myCell

End Sub
hi experts i need an ifstatement formula in row 23;
if L6 = zero or nothing do nothing, otherwise L9*(L19-L13) + L20%, rounded up to 0.1
I get type mismatch error to right line below

        tm = Replace(Cells(rw, 3).Value, ".", "")
        If Trim(tm) = "" Then
            tm = ""
            tm = Right("0" & Hour(tm), 2) & Right("0" & Minute(tm), 2)
        End If

Open in new window

with this values. Please help.

in a previous ask to "Convert Months to Days for Total price Calculation". I had an issue with entering a "Renewal Date" into the userform. In the attached workbook is the fix.  

This issue was fixed but changes are not incorporated into this worksheet 29173473a.xlsm. Please incorporate the fixed code.
A bug in the attached script creates all blank output when the cell references have not data.

For example, cell BR10 is blank, so the "Numbers_3" sheet has not data. It has to do with blank cells, because when I put data in that cells I get cell data in "Numbers_3" sheet.

I would like to resolve it where I can have blank cells because if I have to put data beyond number 70 I will need another script that will delete any row containing any number above 70.

blank cells create blank resultsGreen represents blank cells

The attached file contains data in all the "Numbers_X" sheets but after the macro is run only Numbers_2 has data and it is the only filled line.

I need help with converting the attached link file worksheets Original Quote and RAW_DATA to a Table format with the ability to convert back to a range while having the code to still function correctly.

If you have a SQL view in Excel and if the Excel spreadsheet is the data source for Power BI, is it possible to refresh the dataset without having to refresh the actual spreadsheet and if so how?


Software Engineer

I would like to modify this script or even have another script run on the output if needed, so that any data in the output file which are "numbers stored as text"
will be converted to "numbers"... and the file resaved.
I have an Excel add-in firing an HRESULT 0x800401A8 error, when calling the getter of Worksheet.Parent.

I would be glad if anyone could let me know what the error might be, but more importantly and more generally, I would like to know how I can understand these HResult codes?

I did the following but am still confused:

Read the Microsoft's documentation on the Structure of COM error Codes.
Checked this SO thread.
Tried my chances with Magnum DB.

The attached file with script removes "decis" but would like to combine both "remove Deci" scripts from prior posts into one script and make it faster.

The attached file contains two additional sheets to test with.
The idea is to create a script that removes rows where there are 2 or more numbers from different "decis". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from different decis" or something similar. If the input is one (1) then
2) choose the sheet to be processed
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

Remove rows containing 2 or more numbers from different decis
Notice how all the green rows contain number from only 1 "deci". That is the goal.
Is there a way to have a single excel workbook shared with multiple users where each user can only SEE a single sheet after supplying login credentials?  I know you can prevent editing in this manner but I want to prevent viewing as well.

I am trying to create a simple password keeper for a small organization so that the users can stop using sticky notes stuck to their monitor for this purpose.  I am open to other suggestions but they have to be free and easy to use.

Thanks so much!
I want to bring data from one spreadsheet to another, if a field is 'No', then pull comments from D5 over to another sheet.  

So one sheet - D5 is 'Yes' or 'No' - if 'No' and D6 are comments.  I would like the data to appear on another sheet.  

If 'Sheet1' - D5 = 'No' then pull data from 'Sheet1' - D2.   Thanks Experts.
I have an excel sheet with Names, email and passwords
I want to send an email to each individual on the list with their password, also with some text explaining what it is used for.
I guess I could use word and merge the excel sheet data
But don't know how I can get it to send separate emails to the individual
Any ideas or guidance please

I have a list of dates in A12 -   only workdays.
How can I add 2 blanks for Sat and Sunday
Should look like this:
Thank you very much.
Best regards,
The idea is to create a script that allows a maximum of "X" numbers from same "deci". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from same deci" or something similar. If the input is one (1) then
2) ask to click on any cell that contains numbers to be processed then
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

max of "X" from same DeciThe picture shows items in RED that should be removed they all contain more than one number from the same deci. The sample numbers that should remain are 3, 7, 10, 20, 30 since only 3 and 7 are from same deci. 3, 7, 10, 20 and 29 are shown red since it contains 3 and 7 from same deci plus 20 and 29 from same deci. Thus it does not meet the criteria.
Hello. Im requesting assistance with the following. I have a excel file that i want to filter according to the dropdown list selected. attached is the example file. In the "main" tab is where the table and the dropdown list will be featured. Once the table is filtered to the dropdown selection, the last column (column E on the "main" tab in the attachment) needs to be answered either with YES / NO. Once i have answered all the rows, I want excel to record the options selected in the "results" tab.

the "results" tab will have the same columns as 'main" tab.

Your assistance is greatly appreciated.
On our process controller servers we have come across an interesting issue since the server was upgraded to server 2019.  An IIS based web app on the host is instructed to upload a file from the user's local disk, then open it in Excel on the host where it will be parsed into a waiting database.  The same code is working well on several other machines that are running server 2008.  On the 2019 host the file uploads, and successfully writes it to the temp directory. Excel is invoked, but it fails to find the file and open it.  A rather generic windows type error is passed back to the app.
"An unexpected error occurred: Microsoft Excel cannot access the file 'C:\Temp\Test SP_WCETest1_001_Test Meter_20200218040939.AMD.xlsx'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook."
I have verified the permissions on the file and the folder are open to group 'everyone' with full control.  From the host console the file will open in excel with a double-click. There are no errors in the event logs beyond the one passed back to IIS. (above)
I pulled up procmon on a working host and compared file activity to the temp directory between the working and non-working.  csv files are attached. Ike is the working 2008 server, Phillip is the failing 2019 server.  The only thing that I found of interest is on Phillip, there is some svchost …
I have 2 seperate worksheets. I choose the range from one column to paste into another blank worksheet for analysis and to create a table. IS there an easier way then choosing the top of the column and having to scroll down to the end of my records? There are 1,000 records in some and more in others. I dont want the whole column as I am chosing a range and creating a table in my new worksheet.
I have 2 tables in power query I want to comare the id column in each table and find the matches? How do I do this?
The attached file contains code that makes combinations from data in "sheet1" B10:AG19  and puts them in sheets "Numbers_X" where "X" is the first number in B10:B19.

When the code is executed again it deletes data in sheets "NumbersX" then writes new data.

The goal here is to edit the code to maximize the number of combinations made.. The current script makes combinations using 32 numbers from "sheet1" (grey / white alternate for easier view). The current code also uses the first number as the only common number as can be seen in sheets "Numbers_X".

The additional numbers have been highlighted light and dark yellow. If it is not possible to max at 69-70 then I'll accept the maximum possible under the circumstances. Please specify the maximum possible numbers used so I can adjust the data accordingly.

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.