Microsoft Excel

137K

Solutions

38K

Contributors

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 have a large number of excel files I need to open and run a macro to count columns and count non-blank and blank values on a row.  The first 8 columns are the same and then after ~ the columns per file various.  It could be 1 column to 45 columns.  Also a file could have only 1 record or it could have over 300 records.  I am trying to figure out a way to open all of the files at once apply the macros to remove a few columns and add 3 columns to hold the "total count", "blanks", and "completed" values, along with having  the output from each file combined into one larger file.  Any help would be greatly appreciated.
0
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

I have a table that is generated from a data source.  The table is based on a 6-month time frame.  My challenge is how to populate the table if I do not have all 6-months of available.  So if I have 3-months of data, then they those 3-months would be displayed reading left-to-right, with the oldest in the left most column.

I have played with nested IF statements, but with no success.

Attached is a small example of my data with a table showing 4 of the 6 months and one with all 6-months. Table_With_Moving_Data_EE.xlsx
Table_With_Moving_Data_EE.xlsx
0
I have a report in excel where the total time is written like so:

100d2h54min29s

I was wondering / hoping someone had a formala to confert that data into a straight number of seconds.

IE:

8650469

Thank you,
0
Our FE.mdb front end database runs in "user mode" which means: special keys off; navigation off; full menus off; etc
But Joe needs a "tester mode" with special keys ON.

Currently, we accomplish this manually: v
Joe opens FE.mdb then clicks File > Privacy Options > Current Database > Use Access Special Keys > OK
After closing and reopening FE he is in "tester mode".

But he needs to do this every day because our network signon script always recreates c:\front end\fe.mdb from the server's FrontEndMaster folder.

I am planning on modifying the signon script so that Joe's refresh comes from a FrontEndSpecial folder which I must carefully maintain. Is there a cleaner  approach?
0
Try to set a OFFSET function but it returns an error. Any idea ?

Thx
Excel VBA
0
When I create an invoice for my customers, alot of my customers decide to pay me over time. Those that opt in to pay over time make a monthly payment to me. As I run some reports, I am trying to figure out two things: 1. How many months does it take for each invoice to get paid in full? 2. In how many months is 1/2 the invoice amount paid. I want to know this because when 1/2 the invoice is paid, I offer them other discounts. I attached a sample of my report I export. I added two columns (B and C). I put the answers in those columns that I am hoping you can help me with the formula. So, can you please help me with the formulas in columns B and C? Thank you in advance!!!
Data-Sample.xlsx
0
I am using this code to put time stamp in cell B5:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.

    With Me
        Set KeyCells = .Range("D5")
   
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
   
            ' we will put time stamp in cell B5
            Application.EnableEvents = False
            .Cells(5, "B").Value = Now()
            .Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
            Application.EnableEvents = True
        End If

    End With

End Sub

It works ok however I need to concatenate  "Run at: "  to this time stamp.
Any idea.
0
The variables lRow and lCol will only show the correct information on the first loop, the following loops is a report on the first loop. A similar thing happens if I use LastRow variable.


Public Function ExportToExcel(ReportFilterType)
' Tools > References > Microsoft Excel Object Library

On Error GoTo Error_Message
'Requires Reference to Microsoft Excel Object Library
 Dim rsMainList As DAO.Recordset
 Dim rsExport01 As DAO.Recordset
 Dim rsExport02 As DAO.Recordset
 Dim rsExport03 As DAO.Recordset
 Dim sqlExport01 As String, iCol01
 Dim sqlExport02 As String, iCol02
 Dim sqlExport03 As String, iCol03
 Dim xlObj As Object
 Dim xSheet01 As Object 'Upload File
 Dim xSheet02 As Object 'Batch Summary
 Dim xSheet03 As Object 'Batch Detail
 Dim strFolder As String
 Dim LastRow As Long
 
 On Error Resume Next
 DoCmd.Hourglass True
 Set rsMainList = CurrentDb.OpenRecordset("SELECT DISTINCT UPLOAD_ID, VISUAL_BATCH_ID, VISUAL_BATCH_TYPE, VISUAL_DATABASE  FROM VMTBL_NI_VM_ORCL_GL_UPLOAD_STAGING")
 If rsMainList.EOF Then Exit Function
 rsMainList.MoveFirst
 
 strFolder = CurrentProject.Path & "\"
 
 Do Until rsMainList.EOF
   
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add

    sqlExport01 = "SELECT Database, [Posting Date], [Account Combination], Co, Div, Fun, Rig, Job, AFE, Maj, Min, [I/C], [Debit Amount], " + _
    "[Credit Amount], [Total Amount],[GL Description], [Upload ID], [Batch ID], [Currency ID] " + _
    …
0
I have a table with columns A,B,C,D,E,F,G.  Column A-F are categories and are sorted first on A, 2nd on B and so on through column F.  G is a price.

I want to count and total column G for rows that match on all columns A-F.

So for a table that contains columns like Country      State      County      City      Street House      Price(actual column names are different and make little sense to someone outside the company),
how do I count the number of houses on a street and subtotal the price?  The problem is that you could have two cities with the same street number.  These two cities could be adjacent in the sorted rows.  So I can't just count and subtotal when the street changes since sometimes the streets will be the  same while the cities are different.
0
I have issue with change event in Excel Worksheet.
Excel 2016.

In my workbook I have several worksheet.
On first one I have two cells that I am using as parameters for my data source query  at the same time when I change value in one of two cells I want to use that  as trigger to pull date and time when this happened and put that on spreadsheet.


Here is code of Worksheet_Change event.



Private Sub Worksheet_Change(ByVal Target As Range)


    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("G4:G5")

    If Not Application.Intersect(KeyCells, Range("G4:G5")) Is Nothing Then

        ' we will put time stamp in cell B5
        Cells(5, "B").Value = Date & " " & Time
        Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
       
    End If


End Sub

Event is fired only when I first time open workbook and change value in those two cells.
After that event is not fired data source is refreshed and I got new data in my spreadsheet but change event is not triggered for date time display.

What could be issue.
0
Introduction to Web Design
LVL 13
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Hello, so I have this Excel table (facsimile):

Initial table
I've been asked to make it so that the table can be printed using the second column as a way to split it into more tables. So, the data should be grouped depending on the second column value.

So this table should be printed on 3 pages, and we'd get these 3 tables, one on each page:

3 tables, grouped by the value in the second column
Does Excel have some quick way to do this which I don't know of? Do you have any suggestions?

Thanks.
0
Hi

I have a workbook that nearly does what I want it to do but I think the formula corrupt.

In sheet called Tableau if data missing from Watford sheet if comes up "NO MATCH"  and when data missing from Tableau sheet but in watford sheet it should also show as "NO MATCH"  but at moment it is showing REF!

Im also trying to set it up so that I get rid of sheet called "new rec" and I dont want any references to this.


Any Ideas?

File attached with formulas
0
Hello experts,

The following procedure allows me to add string on multiple columns.


Sub Add_Specific_String_Multiple_Columns()
    
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
    Dim strToAdd As String
    
    On Error GoTo Error_Routine

    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")

    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    If IsNumeric(intWhich_temp) Then intWhich = intWhich_temp

    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
        & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    
    strToAdd = InputBox("Input the value that you want to add.", "String To Add!")
    
    If strToAdd = "" Then
        MsgBox "No input!", vbExclamation
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        

Open in new window

0
When I use the following equation it works great

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(2018,11,20)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(2018,11,21)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

But if I try to use this equation it breaks:

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(c5)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(h5)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

I am TRYING to refer to another cell to grab the date.  What am I doing wrong??
0
Hello experts,
The following procedure allows me to identify values which are in based on two different ranges.
Sub Is_In()
    Dim wsSource As Worksheet
    Dim wsComaparison As Worksheet
    Dim rngSource As Range
    Dim ComparisonRange As Range
    Dim rCl As Range
    Dim LRSource As Long
    Dim LRComparison As Long
    Dim colSource As Long
    Dim colComparison As Long
    Dim cntMatch As Long
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Please Select any cell in your range source, in this range you will find the cells which are in your range to compare", Title:="Source Range Selection", Type:=8)
    
    Set ComparisonRange = Application.InputBox(Prompt:="Please Select any cell in the Range to compare", Title:="Select Range To Compare With", Type:=8)
    On Error GoTo 0
    
    If rngSource Is Nothing Then
        MsgBox "You didn't select any Source Range to compare.", vbExclamation
        Exit Sub
    ElseIf ComparisonRange Is Nothing Then
        MsgBox "You didn't select any Comparison Range to compare it with Source Range.", vbExclamation
        Exit Sub
    End If
    
    Set wsSource = rngSource.Parent
    Set wsComaparison = ComparisonRange.Parent
    
    colSource = rngSource.Column
    colComparison = ComparisonRange.Column
    
    LRSource = wsSource.Cells(Rows.Count, colSource).End(xlUp).Row
    LRComparison = wsComaparison.Cells(Rows.Count, 

Open in new window

0
I have a small table (another learning exercise for me),  In column A, I have Order Description.  In column B, I have Costs.
 I want to capture the order description using Xl Down, because the order may have 1 or 50 entries.. and that works fine.. Once captured, I am having a little trouble with moving it to the OrderDataBase page, (page 2).
  I want to use on Page 2, column A, XlUp to get to the next free row, and I can, but I don't know how to paste the information from the clipboard, nor do I know if that is the best method.
 
I know this is very simple for you guys but after hours of searching, I am lost..

Thanks,
Norm
0
Excel Search Cells by Format [Fill Color] follows no discernible directional pattern: across column, across column, down columns, down columns, across columns.

Attached is an example sheet.  If I run the Format Fill search for yellow fill, I get this series of results:
Z5, AH11, X25, X26, AX26, AX28, K34, O37, X60, AZ61, V62, BO36

I need the search to go down each column so that the cells would appear in this order:
K34, O37, V62, X25, X26, X60, Z5, AH11, AX26, AX28, AZ61, BO36

Have group of extracted quotes arranged thematically in columns.  Have highlighted some that I that I want to quickly review.  Need to be able to focus to each highlighted cell down the columns and then move over to the next column with a highlighted cell at the lowest number / nearest-to-the-top cell.  Continue down.  Move to the top of the next column with a highlighted cell.  

So, left to right, down, over and up columns, according to cells with the fill color in question.

Any way to do that?

Many thanks,

OT
search-direection-test-by-formattin.xlsm
0
I have a spread sheet that I need to sum a group of numbers based on shift start and shift end.

The shift starts at 11pm and ends at 7am

I have attached a sample spread sheet.

I need to sum the values in H "ToteNetWeight" for shift starting on 1-1-2018 at 11pm and ending on 1-2-2018 at 7am.  AND I want to sum only the values in H IF the value in I " ToteTypeName" has the "L8P Graves" value in it.

How can I do this as simply as possible??

Thanks!!
0
Hello experts,

The following procedure allows me to extract string in between parenthesis.

Sub Extract_String_Between_Parenthesis()

    Dim strColInput As String
    Dim strColOutput As String
    Dim lngLastRow As Long
    Dim strTemp As String
    Dim lngRow As Long
    Dim strIO
    
    On Error GoTo Error_Routine
    
    strIO = InputBox("Please enter the column letters separated by a colon for the data in the form 'InputColum:OutputColumn'", "Choose Column Letters", "A:B")
    strColInput = Split(strIO, ":")(0)
    strColOutput = Split(strIO, ":")(1)
    lngLastRow = Range(strColInput & "1048576").End(xlUp).Row
    
    For lngRow = 1 To lngLastRow
        If InStr(1, Cells(lngRow, strColInput), "(") > 0 And InStr(1, Cells(lngRow, strColInput), ")") > 0 Then
            strTemp = Split(Cells(lngRow, strColInput), "(")(1)
            Cells(lngRow, strColOutput).Value = Replace(strTemp, ")", "")
        End If
    Next
    
    Exit Sub
Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"
    
End Sub

Open in new window


I would like to add the following improvement:
Instead of exporting just string between parenthesis I would like to export string in between other characters.

The idea is to put two initial inputbox.
1-Inputbox "Enter the first character involved by the extract process" Example: "("
2-Inputbox "Enter the last character involved by the extract process" Example: ")"

If I can put just one inputbox like this:
1-Inputbox "Enter the first character and last character separated by ";" involved by the extract process" Example: "(;)"
It would be better.

If you have questions, please contact me.

Thank you for your help.
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hello experts,

The following procedure allows me to create folders:

Sub Create_Folders()

    Dim xDir As String
    Dim fso As Object
    Dim oWS As Worksheet
    Dim lngLastRow As Long, i As Long
    Dim MsgTxt As String, RefFolder As String
    Dim ErrorsFound As Boolean

    On Error GoTo 0

    Set fso = CreateObject("Scripting.FileSystemObject")

    Application.ScreenUpdating = False

    Set oWS = ActiveSheet

    lngLastRow = oWS.Range("A" & "1048576").End(xlUp).Row
    
    MsgTxt = "Before running this procedure, make sure to report following information as of row 2:"
    MsgTxt = MsgTxt & vbNewLine & " 1-Column A: Path name"

    
    Ans = MsgBox(MsgTxt, vbQuestion + vbYesNo, "Confirm Please!")
    If Ans = vbNo Then Exit Sub
    
    RefFolder = InputBox("Report reference folder in which you want to create folders reported in column A")

    'If Cancel button is activated exit sub
    If RefFolder = "" Then
        MsgBox "Procedure has been cancelled"
        Exit Sub
    End If

    If Not fso.FolderExists(RefFolder) Then
        MsgBox "unable to proceed as initial folder doesn't exist"
        Exit Sub
    End If


    If Not fso.FolderExists(RefFolder) Then
        MsgBox "unable to proceed as initial folder doesn't exist"
        Exit Sub
    End If

    ErrorsFound = False

    For i = 2 To lngLastRow
        'change the path on the next line where you want to create the folders
        xDir = RefFolder & Application.PathSeparator & 

Open in new window

0
When I save a new 2013 Excel file for the first time it always tries to save to some cloud location. My "Save" settings in options are set properly with my hard drive location. How can I change this? Thank you.
0
Hi. What Excel VBA code would I use to test if a value of 87 is present in a column named Col1 in a table named Table1 in an Access database in the same folder as the spreadsheet
0
HI,

Is is possible to configure an Excel Pivot Table to only show the rows where there is a value in one of Columns.

If we start with this data
Example of data
We can create a pivot table that looks like this
Example Pivot Tableusing these settings
Pivot Table Settings
What I would like to be able to do is only show the rows where the Client has only purchased Product A.  this would result in only Mark and Peter appearing.
Result of the required filter
0
Hi

I am using the following Excel formula where the result is sometimes #N/A. Can I format the cell
to rather show a blank? If not how would I adjust my formula to show a blank instead of this?

=VLOOKUP(Entry!B2,Lookup!A:B,2,0)


Thanks
0
Hi

I have an Excel sheet with simple formulas that point to another sheet eg cell A2 simply has the formula =Entry!B2
My problem, as per the following image, is that zeros are showing up for blanks on the source sheet. How do I adjust
my formula to show blanks? Thanks

1
0