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

Hello experts,

I have the following table.2018-06-24-11_51_11-Microsoft-Excel-.png
I am looking for a sumif formula in order to have the monthly benefit by month.
For example for April I should display the monthly benefit (income-expense) by month.

Could you please help to set up the formula?

I attached xlsx file.

Thank you very much for your help.
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

I have been building Excel VBA apps/scripts for a long time, but I'm having a problem with something that has worked in other apps for years.

I build a menu item in an xlsm file, and then it closes when the workbook is closing.  

But the command bar menu item isn't going away with this one, and then i get an "out of memory" error sometimes when closing (usually if i'm saving the file also).

I call the subroutine in the workbook Workbook_BeforeClose event.  

The code is as follows...  Pretty straight forward.  The menu stays in the remaining application, and then when i run the menu item, it opens up my xlsm file again.

What could this be?

Sub DeleteCustomMenuItem()
    Dim cbc As CommandBarControl
    Set cbc = Application.CommandBars( _
        "Worksheet menu bar"). _
        FindControl(Type:=msoControlPopup, _
    If Not cbc Is Nothing Then cbc.Delete
End Sub

Open in new window

I seem to be having an issue with my workbook. It comes up with the Run-time error '1004' Select method of Worksheet class failed when I copy it into a new folder or rename it. The code is simple. I am new to VBA and don't know how to set workbook or sheets as Active correctly.

Private Sub Workbook_Open()

Sheets("QUOTE SETUP").Visible = True
Sheets("QUOTE SETUP").Select
End Sub

Open in new window


I need assistance with, preferably, developing a VBA function (in Excel) which allows to *transpose* (not in the typical sense) data from "View 1" to "View 2".   As we always say... "a picture is worth a 1000 words".   Thus, please find attached spreadsheet that shows the existing view and the desired view.

I hope the truncated information + call outs provide sufficient information.

I have a code below that finds CSA and places it into a specific cell and then removes CSA from the original cell. My question is there a way to search for anything between parentheses and do the same thing? Copy it out and place it into a specific cell and then remove it from the original cell?

One thing that makes it tricky is some of the parentheses could start in one column and end in another. For example the starting parentheses  could be in column A1 and the end parentheses could be in E1.  If I could copy the text between those and put them in one cell somewhere else that would be ideal.  

I uploaded an excel doc with an example of what I have on sheet1 and what I am attempting to make it look like on sheet2.

Sub CSA2()
Dim findrange As Range
Dim finddata As String
Dim k As Integer
Dim cel As Range

'k = Range("A" & Rows.Count).End(xlUp).Row
'Set findrange = ActiveSheet.Range("A1:A" & k)
Set findrange = Selection
finddata = "CSA"

For Each cel In findrange

    If IsEmpty(cel.Value) = False And cel.Value <> "CSA" Then
        If InStr(1, cel.Value, finddata, 0) Then
            cel.Value = Replace(cel.Value, finddata, "")
            cel.Offset(0, 2).Value = "CSA"
        End If
    End If

Next cel

End Sub
From a SQL 2008R2 database, using Exlcel 2010,
I need to update the D cell (Account No)-number  by using the values in the C cell (Client ID) -text.
The key to select off the SQL table, CLients, is CLIENT_ID (text) and I need to return the SQL column ACCOUNT_NO (int) and update the column 'Account No' on the excel spreadsheet.
I'm assuming I need to create an ODBC to the SQL Database, which I have.
I am TNCO for an Army National Guard unit.  I have been tasked to keep track of school dates, etc.  I started a spreadsheet in which it tracks dates for the start of the school.  I wanted to use conditional formatting to let me know (by color) how close the date is to today's date.  For example, the further out the date the color would be green and at a certain point it would to to yellow then red when it is close to the start date.  I don't know if conditional formatting would be the best tool for the job.  If anyone could help me make the attached spreadsheet better I would greatly appreciate it!

Hello, Please find the attachment. I have this Excel example file. From the sheet B, I want to import the data to the F:19 in first sheet A using MATCH, OFFSET, COUNTA, INDEX functions. Please let me know how to use the functions to extract that data.
Copy and paste the data from sheet1 to sheet2 conditionally
plz see the sample file
I want excel to count cells that say "19" in column E and either "Fullfilled","Waitlist", or "Fullfilled without Contract" in Column C.

Can I do this with one formula? Or do I have to have 3 countifs that add eachother up?
Cloud Class® Course: MCSA MCSE Windows Server 2012
LVL 12
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

How do I use a named range titled: "SampleRng" in an Index/Match formula?  Assume I am trying to search for "Val" In the second column of the named range. I have attempted a formula for this already, but it does not seem to work. See below...


Note: In the above formula I am trying to use Offset to index the second column in 'SampleRng' and I am trying to use Offset/Match to match "Val" in the first column in 'SampleRng'. It does not work though...
I want to separate file server from domain controller. As it will change host name of the server this file server has lot of excel file with links by changing server name will there be any effect on files containing links. if yes how we can tackle this issue for successful migration without disturbing the links
How to get Sumproduct & subtotal  formula for two column count.

This one is applicable for one column .

In the same way can you give me the formula for two column data count.

i am sending the attachment for the same
Microsoft Excel
The attached file contains ID in Column A and ID in Column C.
The problem is that the ID list in column A does not correspond with the ID list in Column C.
I am seeking a script that will move contents of A & B to the row where the ID in Column A corresponds with the ID in Column C.
Note: There may be some rows where there is an ID in Column A and no corresponding ID in Column C (Row 1746). In this case, the cells from  C to U are moved down.

In the example, the  rows have been adjusted manually until row 1963.
I am in need of an Excel macro that will perform the following:

Compare two columns of stock symbols that are numerically listed and the results of the comparison displayed in a third column.  I’m looking for stocks in column B that have improved in their numerical order (one being the best) from their listing in column A.  The results would be displayed in new column C, and the entries would be listed in order of those making the best improvement at the top.

•      Columns A and B contain 200 entries (rows) each.  No blank cells in either column A or B.
•      Column B may have symbols that are not contained in column A.  
•      Symbols in column B not listed in column A would receive the highest ratings in column C.
•      If there are two or more symbols in column B that are not in column A, the symbols in column B would be ranked accordingly in column C.

Example of conditions for column C listing using attached example file: Two columns of Symbols.xlsx.
•      Symbol ARDM is listed in column B but not column A, so it goes to the top of the list in column C and is listed alphabetically (preferred, but not necessary).
•      Symbol FCRE also listed in column B but not column A, so it goes to the top of the list in column C and is listed alphabetically (preferred, but not necessary).
•      ATNM was 151 in column A, and is 8 in column B, a gain of 143, so it would go above CADC, which was 91 in column A and is 10 in column B for a gain of 81.
•      CADC would go above NEPT …
Access vba routine needed

I have a text file:  C:\USER\MyText.txt
Pipe delimited "|"

What I need:
I need to examine this text file and delete any  $ or  %
18460|1TZG8|Dust Mop Handle,Clip On,60"L|1000|[b]$2.22[/b]|0|-51.8018018018018|$3.17|-6.30914826498423|70||1|
18460|3H384|Broom Handle,Wood,Natural Wood,60"|330|[b]$1.75[/b]|0|-10.8571428571429|2.5|22.4|77||1|
18460|3NB91|Wet Mop Handle,Jaw,60"L|3000|15.77|0|35.8275206087508|$22.53|55.0821127385708|58||1|
18460|3H384|Broom Handle,Wood,Natural Wood,60"|330|1.75|0|-10.8571428571429|2.5|22.4|77||1|
18460|3NB91|Wet Mop Handle,Jaw,60"L|3000|15.77|0|%35.8275206087508|22.53|[b]%55.0821127385708[/b]|58||1|
18460|1A841|Push Broom,Head,24",Yellow|250|12.28|0|61.6449511400651|19.57|75.9325498211548|69||1|
18460|3U767|Push Broom,Head,36",Green|250|12.59|0|26.131850675139|24.89|62.6355966251507|70||1|
18460|1VAC2|Lobby Broom,Synthetic,Black|2500|6.8|0|57.2058823529412|9.71|70.0308959835221|64||1|
18460|1NXZ9|Bench/Counter Brush,Synthetic,9"|150|3.94|0|59.3908629441624|5.63|71.5808170515098|70||1|
18460|1DU74|Deck Brush,Poly,Replacement Brush Head|200|6.42|0|69.9376947040498|10.29|81.2439261418853|74||1|
18460|1VAD9|Scrub Brush,Synthetic,Iron Style|500|1.12|0|69.9376947040498|1.6|30|61||1|
18460|38TM04|Toilet Bowl Brush,Round|1000|2.25|0|17.3333333333333|3.88|52.0618556701031|27||1|
18460|1TZC5|Dust Mop,Blue|2500|6.47|0|37.2488408037094|9.24|56.0606060606061|63||1|

Open in new window

I had this question after viewing Word VBA How to programmatically set highlight color of dropdown content control?.

I want to populate unique values from a excel column to a wdContentControlDropdownList and assign it to a bookmark defined in word. Can you help how to do it?

For the time being, I have tried hard coding the list entries but still no luck. below is the code.

Dim lobdd As ContentControl
Dim WRD As Object
Dim ws As Worksheet
Dim DOC As Object
Set ws = ThisWorkbook.Sheets("Sheet1")
lstrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
    Set WRD = GetObject(, "Word.Application")
    If WRD Is Nothing Then Set WRD = New Word.Application
    Set DOC = WRD.Documents.Open ("path here" , ReadOnly:=False)
Set lobdd = ActiveDocument.ContentControls.Add(wdContentControlDropdownList)
With WRD.ActiveDocument
str = ws.Range("B2").Value
.Bookmarks("Title").Range.Text = ws.Range("B2").Value
With lobdd
         .Title = "Cities"
         .SetPlaceholderText , , "[choose a City]"
        .LockContentControl = False
         .DropdownListEntries.Add Text:="Copenhagen", Value:="1"
         .DropdownListEntries.Add Text:="New York", Value:="2"
         .DropdownListEntries.Add Text:="London", Value:="3"
         .DropdownListEntries.Add Text:="Paris", Value:="4"
         End With

Open in new window

I have an .xlsx file. I want to fetch data through VLOOKUP function from a .CSV file which gets generated everyday. Is there a way to use the function? Please help. Its urgent
Hi Anyone,

My boss want me to define all the users (about 20,000 domain member) which member of group (about few 1000 groups) we've in our enterprise domain to come with a accurate user domain permission matrix.

What should I do as I'm very new to the enterprise domain network and not very good in writing script like using powershell or never work in the enterprise network before.

What is the best suggestion for me in order to complete within 1 days as my boss requirement.

Anyone can help.


Free Tool: Site Down Detector
LVL 12
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.

In the attached spreadsheet I need to loop thru each tab (see SD1 and SD2 tabs) NOT called MAIN and copy over columns C and D to the Main tab starting in Col E.  In the Main tab my intent is to copy the data going in a vertical direction starting at Col E of the Main tab.

In Excel VBA I know how to loop thru each of the tabs, but I am struggling with is what code I need to use to determine what the next available open column is in the Main tab before I copy the data from Tabs SD1, SD2, SD3, etc.
I need to  export from .NET to Excel .
I need place 5 tables to to the same Excel sheet.
I use this code

 private void eResponsetable(DataTable dt, string ReportName, HttpResponse response)

            StringWriter stringWriter2 = new StringWriter();
            HtmlTextWriter htmlWrite2 = new HtmlTextWriter(stringWriter2);

            //this is the grid that will be output to excel
            GridView tempGrid2 = new GridView();
            tempGrid2.Caption = ReportName;
            tempGrid2.EmptyDataText = " ";
            tempGrid2.DataSource = dt;
            tempGrid2.GridLines = GridLines.Both;
            tempGrid2.HeaderStyle.Font.Bold = true;
           // tempGrid2.HeaderStyle.Font.Size = FontUnit.Large;

           tempGrid2.HeaderStyle.ForeColor = Color.White;
            tempGrid2.HeaderStyle.BackColor = Color.Gray;

            //tell the gridview to render itself to our htmltextwriter

  protected void CreateAllReports2()
            HttpResponse response = HttpContext.Current.Response;
            //clean up the response.object
            response.Charset = string.Empty;
            bool inline = true;

            //set the response mime type for excel

Open in new window

I had this question after viewing Remove night hours for All week days.

How do you get this solution in hh:mm  for hours and minutes
How to convert Excel file into Data table with formatted columns in VB.NET.

I have Excel File in my desktop. i just want to convert as data table.

The thing is, i don't want same column name which is been specified in the excel file.

i want some corrected and newly defined column name (Formatted Column) .

for example, i have excel column like "Turn Date [S1]" ==> my data table column name is only "TurnDate"

How to do this...?

Attached screen shot for reference.
Hi. I need to batch extract all table data from 500+ Word files (a mix of .doc and .docx).  I have found and successfully tested the VBA below which works on a single named file. Is it possible to adapt this code so that loops through all files in a directory, saving each workbook with the source Word filename? Otherwise software that can achieve the same? Thanks.

(src Dinesh Kumar Takyar)  

Sub importTableDataWord()
Dim WdApp As Object, wddoc As Objecthow-to-import-data-from-Word-tables.xlsm
Dim strDocName As String
On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set WdApp = CreateObject("Word.Application")
End If
WdApp.Visible = True

strDocName = "C:\our-inventory\inventory.DOC"

If Dir(strDocName) = "" Then
MsgBox "The file " & strDocName & vbCrLf & _
"was not found in the folder path" & vbCrLf & _
"C:\our-inventory\.", _
vbExclamation, _
"Sorry, that document name does not exist."
Exit Sub
End If


Set wddoc = WdApp.Documents(strDocName)

If wddoc Is Nothing Then Set wddoc = WdApp.Documents.Open(strDocName)
Dim Tble As Integer
Dim rowWd As Long
Dim colWd As Integer
Dim x As Long, y As Long
x = 1
y = 1
With wddoc
        Tble = wddoc.tables.Count
        If Tble = 0 Then
            MsgBox "No Tables found in the Word document", vbExclamation, "No Tables to Import"
            Exit Sub
        End If
        For i = 1 To Tble
With …

I have csv file which gets generated into my network drive automatically everyday. I want to convert the CSV file to an XLSX file delimited with ; and sort the data based on one column i.e., Name to either different sheets or different files. Is it possible to get a VB Script to perform the above process?

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.