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 using Office 2016 standard edition, Excel 2016 MSO(16.0.4266.1001) 64-bit. When ppl are sending excel sheets with graphs that has data labels or data tables some of the data is missing, although other users using the same version of office or different versions can view the same files properly. I tried repairing office installation, uninstalling office and installing the same version, as well as uninstalling and installing Office 2016 home and business still the same issue exists.

I was thinking of performing a full system fresh installation, just worried with all the pain that goes with it that the problem will remain and it will be another waste of time.

I appreciate your prompt help on this
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.


Basically, I need to recreate 3 sheets on excel workbook using access vba. The 4th sheet on excel has got table/chart which have got excel formulas and they are looking into the other 3 sheets to generate values. Each day when I delete these 3 sheets, and recreate them, the formulas in the 4th sheet get corrupted. Even if I generate other 3 sheets, the values on the 4th sheet are not being updated because of the corrupted formulas. what is a solution for this? One solution is to generate formulas on 4th excel sheet using access vba. But there are lots of formulas (more than 100 as of now, big chance to increase). Is there any other suggestion like keeping a sheet with formulas and copy paste them using access vba.?But if I copy paste I need to do "replace" sheet name at the end and its going to be complicated. Any help on this greatly appreciated.

thank you
Hello experts,

I have multiple vba procedures which I would like to share with final users so they can re-use it when they open excel application. I was wondering what is the best way to automatically share with them those procedures. I was thinking to add all those procedures into an add-in file and then share the file however I don’t know if it is the best way.

Thank you very much for your feedback.
I have a spreadsheet with an Excel table.  There is protection applied to the sheet but the table is unlocked for data entry. However, there is no option to add comments to cells. How can I keep the table format and allow users to add comments. Or is that possible when a table format is being used? Thanks
In Excel I have a worksheet that has data in columns. Last Name ,First Name, Address,  Email, Phone Number.
Last Name: Smith
First Name:John
Address:15 Main Street
Phone Number:888 195-1234
Last Name: Jones
First Name:Bill
Address:25 South Street
Phone Number:888 123-4567
In a second sheet I have the formula  =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-FIND(":",Sheet1!A1))  in cell B1   and =RIGHT(Sheet1!A2,LEN(Sheet1!A2)-FIND(":",Sheet1!A2)) in B2
etc for the first 5 columns

I want to take lines 6 through 10 and put the data in row 3.
I know I can write the formula  =RIGHT(Sheet1!A6,LEN(Sheet1!A6)-FIND(":",Sheet1!A6))   etc and so on for data in Sheet 1 rows 11-15 etc.

I tried cell references =RIGHT(INDIRECT("Sheet1!"&"A"&CELL("row")),LEN(INDIRECT("Sheet1!"&"A"&CELL("row")))-FIND(":",INDIRECT("Sheet1!"&"A"&CELL("row"))))

etc but it seems to jump all over depending on the last cell clicked on.
Is thera a way that it can use the actual cell as a reference?
So the data from sheet1 will be populated in sheet 2 with the description lines stripped out.
So in this example I will see:
Last Name   First Name    Address                 Email                         Phone Number
Smith               John            15 Main Street      888 195-1234
Jones              Bill               25 South Street          888 123-4567

ETC.  I could have 25 to 100 or more contact names

Just …
How to copy and paste data with conditional formatting format from minitab worksheet into excel ?
I tried but they copy in values and the format that i had in minitab was not reflected in excel.
create an new sheet Call DD with only the doubles digit

as shown

here the file
I use a vb script to reformat CSV I receive from a vendor on a daily basis. I've started receiving a new batch of fields (columns) that I need to concatenate AND insert separators between values. Currently, my tortured method is below:

objSheet.Range("X2:X" & lastrow).FormulaR1C1 = "=RC[5] & "";"" & RC[6] & "";"" & RC[7] & "";""& RC[8] & "";"" & RC[9] & "";"" & RC[10] & "";"" & RC[11] & "";"" & RC[12] & "";"" & RC[13] & "";""

However, this results in a whole lot of orphan semi-colons whenever a column is empty. I've tried TEXTJOIN like so:

objSheet.Range("X2:X" & lastrow).FormulaR1C1 = "=TEXTJOIN("";"",TRUE,RC[5]:RC[16])"

but that failed.

Can somebody provide an alternative method that won't end up creating entries like this?

eliminated the follows after  the repeat key number repeat

as shown

I had a GREAT spreadsheet until I added some lines and columns.  

In the account number fields (found throughout) when you entered 1234
you got 1234-00-00
When you entered 12
you got 1200-00-00
When you entered 123456
you got 1234-56-00

Suddenly, not only is that routing not working, once active content is enable, any entry to a custom or numeric field gets a Debug error. Sample spreadsheet is attached.

I know the problem is in VBA, but even when I update the routine with the appropriate cell numbers, ranges, I still get the error.
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

I am trying to copy a formula in a range of cells. I placed the worksheet activate and nothing is happening. What am I missing.

Private Sub Worksheet_Activate()

Range("B10:B1000").Formula = "=IFERROR(MID(C17,FIND(" * ",SUBSTITUTE(C17," \ "," * ",LEN(C17)-LEN(SUBSTITUTE(C17," \ ",""))))+1,LEN(C17)),"")"
Range("D10:D1000").Formula = "=LEFT(B10,10)"

End Sub
I would like a formula to detect a word within a string of words.
example:  Detect the word  'pear' from the following situated in cell C2     I picked a pear  (or)  I picked a plum
Return 1 if word exists and zero if it doesn't.
Many thanks
Hi Experts,

Hoping someone can help me with a solution to automate a manual daily task
I need top download a file from a http (url - report server) site and save it to a folder at regular intervals ( hourly )

The manual process is as below
1) Go to the website/url using IE
2) Click on a drop-down item and select "Export to Excel" (Actions --> Export (option 3) --> Excel (option 5) --> Its a sub-menu see html below
3) A pop-up appears to Open or Save the .xlsx file
4) Click on Save button
5) Save  it to a folder location on my pc.

I cant use any 3rd party tools or software as I cant download it due to restrictions in my organisation.

Preferably excel/vba solution or if there is an inbuilt windows solution to capture and record actions above and then schedule it at regular intervals.



            <td class="ms-toolbar sqlrv-ToolBarCell" nowrap="nowrap">
      <div id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu">
                              <span style="display:none"><menu type='ServerMenu' id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ctl00"><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ReportBuilder_forRendering" type="option" onMenuClick="STSNavigate2(event,'http://project/PWA/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderViewer&ReportPath=http%3a%2f%2fproject%2fPWA%2fReports%2fPSD+Extract.rdl');" text="Open with Report Builder" menuGroupId="2147483647"></ie:menuitem>
The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:


Open in new window

I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Charts are easy to insert manually but sometimes we need to automate the chart insertion and most of the people find it difficult to control the chart creation and its properties via VBA. In this article we will learn about how we can use VBA to insert a Chart and manage it's appearance.
after the TOTAL sheet is created
ias shown in this sample
then need an selector to select the  data  and create in SELECT  sheet as shown


Im new to VBA and got some problems with making a index page from values in my msgbox.

dont under stand why this 2 lines of codes dosent work.
Set wsName = Me.BDClass.Value

Open in new window

and this one
shIndex.Range("B5").End(xlDown).Offset(1, 0).Select 

Open in new window

Private Sub AddFromBtn_Click()

    Dim r As Range
    'Checks if BDClass allready exist
    If BDClass.Value <> "" Then
        Set r = wsIndex.Range("B:B").CurrentRegion.Find(BDClass.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en klassifikation med samme navn")
                Exit Sub
            End If
            MsgBox ("Der skal angives klassifikation")
            Exit Sub
    End If
    'Checks if BDName allready exist
    If BDName.Value <> "" Then
        Set r = wsIndex.Range("C5").CurrentRegion.Find(BDName.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en bygningsdel med samme navn")
                Exit Sub
            End If
            MsgBox ("Der skal angives bygningsdel navn")
            Exit Sub
    End If
    'Creates new sheet
    Worksheets("Temp").Copy After:=Worksheets("Index") 'copy Temp to after Index
        ActiveSheet.Name = BDClass.Value 'Renames the sheet to BDClass value
    'Writes value to new sheet
    ActiveSheet.Range("C3") = BDClass.Value
    ActiveSheet.Range("E3") = BDName.Value
    'Writes value to Index 

Open in new window

create the find

the find total have to be exact match
then create the TOTAL sheet as show

I a am Civil Engineer and I am using VBA for Excel from last 10 years. Recently I developed a macro to generate payslips of employees and my friend's company purchased it.

I think more companies require that tool which can make their life easier and I am also interested in the monetary benefit.

But a person with a little knowledge in VBA can break the password, will be able to steal the code and I don't want that to happen. I am looking for ways to make the code secured.

In this regard I expect expert's help.

Thanks in advance
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I need some help coming up with a present value formula to calculate what we call in our industry the true interest cost of debt or TIC.   In the attached file is the calculation produced by software that we use however it does not provide formulas.   I would like to come up with a spreadsheet that I can use to calculate TIC on my own.   About the spreadsheet that I have provided.   At the top is the solution to the cash flows below.   The dated date is the date that interest begins on the loan the TIC is the effective borrowing rate or the rate necessary to discount the amounts payable on the respective principal and interest payment dates to the purchase price (loan amount) received assuming compounded semi-annually.   The first column is the dates payments are made, the second column is the amount of debt paid each semi period and the third column is the discount column calculated by the software to come up with the TIC (discount rate) that equals the target amount above of $10,513,798.96.  I would like to setup a spreadsheet that will allow me to calculate on my on the TIC.  Meaning if I know the target amount, Dated Date, and semi-annual cash flows the spreadsheet will calculate the TIC that discounts those cash flows so that they equal the target amount.  I have tried using the IRR function but it did not work for me.  Hoping to get other ideas how to set this up using this example to check that it works by trying to recreate the answer.  I hope this make sense but …
Hello All.  First let me start by saying "TRIM" DOES NOT Work.

Using Excel VBA, I used the InStr Function to separate the ID Numbers using the comma.
The problem is, this data is in an Excel Cell.  The guy used the Alt + Enter to move to the next line after adding the comma.
When I pull it into a variable, I get one of two results.
" Paragragh 3.5.3"  or "Paragragh 3.5.3 "
It has a blank space before or after each one.  I am not sure it if is a Carriage Return or not.
I have not been able to get rid of it.  I need that term to use a search.  Because of the extra spaces, they are not being found even it they are there.

Fake Data:


  strLeftToCaptureCheck = Left(strToCapture, 1)
  If strLeftToCaptureCheck = "" Then
            MsgBox "Check"
  End If

As a test I used the code above to see if there was something there.
The result was strLeftToCaptureCheck  = "".  The "" is not a blank space.  It skipped over the message box.

Is there a way to find the first character position or just get rid of whatever those blank space are?
I never know if they are going to show in the beginning or the end.  I need a way to look at both ends.

I would like be able to get same output as this workbook but without any data in column 'D
I use text files as source data to feed Access tables due to 2GB size limitation of MS Access.  I am currently using two text files that are merged daily after the one is updated, one of the text files is 2016-2017.txt historical data merged with daily updated 2018.txt file.  The merged file size is 1.24 GB and I can work just fine with it without any problems.  

Lately I have decided to use a bigger size text file for a larger historical analysis like going back to 2012.  I compiled text files into one  2012-2017.txt file using Windows commands on C:\ prompt and added my daily updated 2018.txt file into this history so I could work with one historical data set all the way back to the year 2012.  This is the method that I use daily for my current daily updates anyway without any issues.  The expanded file size is 3.45 GB and when opened in Excel Power Pivot it displays 90 fields. My goal is after loading the data model in Excel power pivot cerate various pivot tables for historical data analysis.

The problem is that when I merge daily refreshed 2018.txt file into the historical file then connect to the resulting merged file via Excel Power Pivot (Access fails to open this file due to size but power pivot works ok along with Power BI) and insert a pivot table what I see is that all the years' total sales dollars from 2012 to 2017 get displayed correctly but 2018 is incomplete by a huge margin.  I tried to load and re-load several times in different Excel files and I …
I am importing from excel to ms sql table. I keep getting &nbsp; in all my empty fields. Can someone help me to fix this so that the empty fields are just empty? All the data is correct just the blank fields.
Hello experts,
I have the following report attached.
I am looking for a procedure to remove blue lines.
Procedure should:
1-Remove Output sheet if it exists
2-Copy Input Sheet
3-Rename copied Sheet with Output name
Remove the various blue lines. They have in common the following attributes: Values of C column=As Code, values of D column=Resource Organization, values of J column=Projects, Values of K column Project ID
4-Add headers based on blue lines removed in topic 3


I attached example file.
If you have questions, please contact me.

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.