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 need to use the sumif function to do the summation for each change in column z in the attached excel sheet
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

I had this question after viewing conditional copy and paste.
plz look into the Actual file
this code is very slow  plz look into it
Nothing to do with yellow highlighted colour data, highlighted colour is only for understanding purpose in actual file there is no highlighted colour
what I want is to copy the data from B to A
see the sheet2, I have putted the result to sheet2
copy the data from sheet1  as per given condition and paste to sheet2

i have to do all this by vba only
I have data in yellow highlighted colour area, nothing to do with it
Now what I want is look into G coloumn
Put B to below A that's it
I have to do all this by vba
see sheet2, I have putted the result to sheet2
but I want the result in the same sheet not in sheet2
highlighted colour is only for understanding purpose in actual file there is no highlighted colour
Grab text from a word file with Excel VBA
My requirement is to grab two texts between two flags placed in word file and write in to excel - 2 columns
Flags can be [mydate] and [/mydate]
and [mytext] and [/mytext]

e.g. [mydate] 2018-07-14 [/mydate] blah blah [mytext] this is my text [/mytext]

It should be repeated until all occurrence in the file.
Sub GrabUsage()
Dim FName As String, FD As FileDialog
Dim WApp As Object, WDoc As Object, WDR As Object
Dim ExR As Range

    Set ExR = Selection ' current location in Excel Sheet

    'let's select the WORD doc
    Set FD = Application.FileDialog(msoFileDialogOpen)
    If FD.SelectedItems.Count <> 0 Then
        FName = FD.SelectedItems(1)
        Exit Sub
    End If

    ' open Word application and load doc
    Set WApp = CreateObject("Word.Application")
    ' WApp.Visible = True
    Set WDoc = WApp.Documents.Open(FName)

    ' go home and search
    WApp.Selection.HomeKey Unit:=6
    WApp.Selection.Find.Execute "[EventDayMth]"

    ' grab and put into excel
    Set WDR = WApp.Selection
    ExR(1, 1) = WDR ' place at Excel cursor

    WApp.Selection.Find.Execute "[EventDayMth]"

End Sub

Open in new window

I had this question after viewing combine sheet1,sheet2,sheet3,sheet4,sheet5 into 1sheet.
This is my actual file but it is bigger than this i have deleted 75% of data bcoz i can't upload the file  above 50mb  here
the current code is unable to handle this much of data and it is very slow so plz look into it and do needful
plz see the actual file in the attachment
I have created a userform and for question 2 I need help writing a subroutine to:
•      Create an array and initialise it
•      Store the count of “Poor”, “Average”, “Good” and “Excellent” in the array
•      Plot a graph to show the number of count for each rating.

Attached is an example file that I have to work on. Any help is much appreciated! :)
On the sample file attached I have data arranged in columns A thru E on Sheet1.  The column E with field name "Deletion Check" is used to check the status of each row for deletion, if it says "Delete" then that row should be deleted in its entirety. I tried three different VBA procedures and those can be seen in modules to achieve the deletion of all those rows that meet the criteria but all procedures raised error.

On Sheet2 I placed the original sample data so after testing the code data set can be reverted back to its original state again.  One quick note here is that initially I was using the procedure "deleteRowswithSelectedText() " without any issues, the way I was using this code from the button was that I was highlighting the column E then running it and it was working pretty good up until this morning but for reasons that are beyond me it stopped working and after that any other code trial generated error even after I tried on brand new files.  

So if any expert can offer a working code either by modifying one of the existing code blocks or adding entirely new code would be much appreciated.
Wanted to calculate Column J based on what's in Column H.  Also Column K based on what's in column I.  I've added some calculations manually to illustrate what it should look like in yellow.

See Attachment.
I had this question after viewing Copy the data and paste the data as per given condition(See the  Sample file).
My file is big and this code is unable to do that it works best with less data file so plz look into it
i have attached my actual file
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I wanted to count all the "cancelled" for 2018 in column D for each of the dates listed in column G and have the results on Column J for each date.

See Attachment.
Wanted to see if there is an easier way to count dates from two columns.  I have several hundred rows of data that I would want to count dates from.

On column B (Create_Date) and column D (Complete_Date) i wanted to count how many times for example 6/1/18 showed up and for future dates coming up.  I did the manual count on columns G and H to illustrate what I was looking for but wanted to see if this can be done automatically.

See Attachment
Dear Experts:

I got pdf file names along with their full path in Column A of the current worksheet, such as ...
Column A
Header = Path Names
retrieve user names
I wonder whether an Excel Macro is able to retrieve information from the security tab of the file properties of the individual files.
 security tab file properties

In specific, I would like to retrieve all the entries that are listed as group or user names on the security tab.
These entries are to be retrieved and enter into Column B, separated by semicolons.

I hope this is feasible. Help is very much appreciated.

Thank you very much in advance.

Regards, Andreas
Attached file stores the actual data for my question,  On Sheet1, Column A has Product Category section and under this section I have 5 rows of drop downs with selections via data validation, under Product Name combo boxes were placed.  

The goal here is that after user makes a selection in the first box in the product category section combo box associated with this selection next to it will only show the related products per this selection.  I have completed thus far okay but the combo box depending on the selection is showing the blanks as well.  To get around that issue I created dynamic named ranges using OFFSET function and added them to the list range property of combo boxes.  

Named ranges that I created are ProdBox1 through ProdBox5 and they can be seen in properties of combo boxes as well as via the name manager.  Sources for these ranges are columns F, H, J, L, N columns on Sheet3.  Even though I used OFFSET function to be able to display the dynamically changing ranges to avoid showing blanks on combo boxes  they still do show blanks depending on the selection.  

I believe what is getting in the way here is the way that I show the selection made on Sheet3 in columns E, G, I, K, M.  I did that just so I could apply my INDEX based formula to display the respective value for the repeating name in the list. I simply copied down the selection hopefully enough number of times to capture the material name for each occurrence and obviously this is not a reliable …
I had this question after viewing if a sheet has duplicates rows then put the summary in sheet2.
When i am runing the code(macro) i am getting error message
Run time error '-2147417848  (80010108)':
Automation error
The object invoked has disconneceted from its clients

plz see my sample file and  i am runing the same code  in this file but i am getting error message
I have this exsiting code that works great to import a single sheet from a workbook.  I wanted this code to be slightly tweaked to be able to select my own workbooks; basically two different workbooks, essentially select two different workbooks and combine the sheets of each one into one.

Also the naming for each sheet is always different and the existing code it's looking for the sheet that is called "page" which will never exist in the workbooks I select, so that part of the code would need to change.

See code below:

Sub ImportSheet()
    Dim sImportFile As String, sFile As String
    Dim sThisBk As Workbook
    Dim vfilename As Variant
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set sThisBk = ActiveWorkbook
    sImportFile = Application.GetOpenFilename( _
    FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
    If sImportFile = "False" Then
        MsgBox "No File Selected!"
        Exit Sub
        vfilename = Split(sImportFile, "\")
        sFile = vfilename(UBound(vfilename))
        Application.Workbooks.Open Filename:=sImportFile
        Set wbBk = Workbooks(sFile)
        With wbBk
            If SheetExists("page") Then
                Set wsSht = .Sheets("page")
                wsSht.Copy Before:=sThisBk.Sheets("BEFORE")
                MsgBox "There is no sheet with name :page in:" & vbCr & .Name
            End If
I am using the following formula  =INDEX(Sheet1!V:V,MATCH(E6,Sheet1!G:G,0))

Why do I get   #N/A   when it encounters a number vs. text and how can I fix or prevent this from occurring?
I have tried to re-format everything as General but that doesn't seem to help.
are there any tools which can essentially split an enormous (2.5GB) text file into manageable 'chunks' which can be fully imported into excel? I have received 2x 2.5GB files which I am assured are simply space delimited log files, but excel cannot import all the rows due to limitations. I was hoping of an additional tool/process to split it into 4 smaller files and import 1 by 1. I have tried importing into SQL Server express but that's causing numerous errors. It does import into excel in the format required but misses lots of the rows (it is an unknown exactly how many rows there are in these beasts.
Hi guys,

I'm new to macros and I'm enhancing this report of mine for efficiency. I was able to create the 1st two modules (for formatting and some computations). This is for the 3rd module and for the final step.

The scenario goes like this:
1. I have a source file (Working file.xlsx). This would be the result from the formatting by the 1st two macros that I have.
2. Column I will be the basis of the number of new workbooks that will saved. So, column I will be filtered and whatever is in it will be saved as a new workbook.
3. The filename is custom for every filtered value. Let's say the values in column I are A, B, and C, the filenames are Apple.xlsx, Banana.xlsx, Cherry.xlsx. (wondering if I could use a lookup table for this one)
4. I'm using Excel 2016, 64-bit.

Hope you could help me with this one. Thanks in advance!
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.

Is it possible to create a hyperlink to a specific cell in a spreadsheet like Google Sheets or Excel?
I want to convert from sheet1 to sheet2. Sheet1 could have more data.
I have an excel spreadsheet that inserts a security code and security description into a table. Clicking a Command Button executes a SQL Server stored procedure for the insert. The issue I am having is that some securities contain a singe quote (Test's) and error when passed to the stored procedure. I want to pass two single quotes (Test''s) to the stored procedure even though the security is entered as a single quote in the cell.

Example: Test’s will give me an error when passed to the stored procedure. Test’’s does not error when passed to the stored procedure.

I have attached a very basic security loader spreadsheet, stored procedure and table creation.



Code I wish to modify for the single quote to double quote transformation:

Private Sub CommandButton1_Click()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sSecurityCode, sSecurityDesc As String

      With Sheets("Securities")
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=YourSQLServer;Initial Catalog=TestDb;Integrated Security=SSPI;I"

        'Skip the header row
        iRowNo = 2
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sSecurityCode = .Cells(iRowNo, 1)
            sSecurityDesc = .Cells(iRowNo, 2)

             conn.Execute "testdb.dbo.uspSecurities '" & sSecurityCode & "', '" & sSecurityDesc & "'"…
I would like a formula modified to ignore zeros or blank cells.
please refer to attached sheet
Many Thanks
I had this question after viewing Modification of the vba code( it's different).
I think due to symbol my excel is working slow
So now what i want is
Instead of = use E
Instead of < use L
Instead of > use G
Hi all
I want to combine 3 sheet change events in to one macro. With the help from this site, I have a macro with two change events. I want to add one more in to the existing one. The current code is
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim cell As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo Skip
If Not Intersect(Target, Range("C6:C3000")) Is Nothing Then
    For Each cell In Target.Columns(1).Cells
        r = cell.Row
        If Cells(r, "C") <> "" Then
            If Cells(r, "P") = "" Then
                Cells(r, "P").NumberFormat = "dd/mm/yyyy"
                Cells(r, "P") = Now
            End If
        End If
    Next cell
ElseIf Not Intersect(Target, Range("AB6:AB3000")) Is Nothing Then
    If Target <> "" Then
        If LCase(Target.Value) = "completed" Then
            Target.EntireRow.Hidden = True
        End If
    End If
End If
Application.EnableEvents = True
End Sub

Open in new window

My new requirement is to add the following code into the existing one

 If Cells(r, "AB") <> "Allocated back to TL/Site" Then
            If Cells(r, "W") = "" Then
                Cells(r, "W").NumberFormat = "dd/mm/yyyy"
                Cells(r, "W") = Now

Open in new window

Your thoughts please


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.