[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Excel

135K

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

Hello experts,

I have two sheets  

I would like to add to my personal.xlsb a procedure which cover the following requirements:

1.      Inputbox “Select initial range to compare related to first sheet to compare”
2.      Inputbox “Select initial range to compare related to second sheet to compare”
3.      Create a Comparison sheet as attached in which true/false values should be displayed based on = formula.
4.      Highlight false values.

I attached dummy file.

Key information:
-True, False values should go till the last used Range, related to Sheet1 & Sheet2. The best is to identify this by checking last used range of Sheet1 and Sheet2. If one of the sheet have more used range this should be the reference
-If comparison sheet already exists, delete it.

If you have questions please contact me
comparison_14122018.xlsx
0
Microsoft Azure 2017
LVL 12
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.

Generate serial numbers from user form and write to  worksheet

I have a user form to input values. This form will be used repeatedly to generate serial numbers depending on movement types. I want a macro to run this and record the results onto a worksheet. Each time a new batch starts, the results will be recorded continuously i.e first batch will be recorded on row 2, 2nd batch will be recorded continuously after the last row of the worksheet. Help is needed.
0
I have Pivot table that shows Year and Month (in number format) and $$ for each month.  When I do a line chart, I want to show the Year and rather than the month in numeric format on the x-axis, I want to just the month by the 1st letter text ( 01 = "J", 02 - "F", etc)  I have a column set up in the data the pivot reads that looks at the number and creates a the appropriate letter but when I do the chart off of the pivot, it doesn't sort properly It shows the month names sorted alphabetically rather than the numeric month number.  Is there a way to tweak the pivot table or the chart to sort based on the month numeric order (not part of the pivot) rather than the alpha of the month name?
0
Hi,

I have colleague complains that if they open Share Excel file it does not indicate that  File is already open with user. and that cause overwriting issue within there team.
I have looked into this and cannot figure out why this happen. the file is on Cifs shared folder.

please advice how to restrict the file that if some one has open it, it will force  read only for other colleagues.


thanks
0
My workbook has VBA code that runs automatically at a set time each morning (using Windows scheduler to launch Excel and run the script). Excel opens and loads the workbook, and the VBA code starts executing properly. At the same point in the VBA script, the program stops running and Excel closes (not gracefully, just instantly) - no VBA error messages or Excel error messages - it all just stops and returns me to the Windows desktop. However, if I open Excel, load the workbook, and access the VBA code manually (Alt-F11), then run the program using the 'Run' button in the VBA editor, the program executes flawlessly, does what it is coded to do, and exits gracefully.

I have done a Ctrl-Shift-F9 to clear all breakpoints and saved the workbook. I have re-booted the PC. If the code will execute correctly when I manually start it, why will it not do so when running automatically from the scheduler? There are no coding errors I can find and none present themselves if they are there. I'm at a loss as to what to try next. This is all running under Windows 7 and Excel 2007, SP3.

Thanks for any information you might provide!
0
Excel: I have several sheets which different people are updating and I need to put everything on one master sheet.
Please attachment: It has an example
1 - Master tab - this needs to be updated with the in info from the other tabs
2. Individual tabs for each person:  Joe, Mike, Fred
CombineSheetToMaster.xlsx
0
From time to time I see lines of code containing ADODB and connection strings.

Usually people just copy/modify code they find to achieve results, but someone had to originally write the code. I am wondering what the best resources are for me to learn all about this so that I can write my own from scratch?

For example, in the code at the link below, someone had to originally know that Provider, Extended Properties, Data Source, HDR and FMT existed and their proper syntax for their code to work. How did they figure that out?


 https://stackoverflow.com/questions/22947425/using-ado-to-query-text-files-terrible-performance
0
Hi

I am looking for examples of using the Outlook envelope in Excel using VBA.
I want to use it to automatically loop through all sheets and email them in the email body to the same recipient

Thanks
0
I have three columns in an Excel table. X Coordinate, Y Coordinate and Text Description. I have produced a scatter plot from this data.

When I hover the mouse over a point in the scatter plot, Excel pops up the coordinates and the Series Name for that point. Instead, I would like it to pop up the text Description from the third column. How could I make that happen?
0
I have a spreadsheet that needs to be formatted (i.e. deleting columns, concatenate fields etc) and I'm using a macro to do that. However, part of the formatting makes use of the Kutools Add-in therefore I want to include the Kutools functions in the macro. When I tried running the macro the Kutools function was not included in it. Is there anyway to include Kutools in the macro?
0
Bootstrap 4: Exploring New Features
LVL 12
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

VBA Find first empty row in table
I can find first empty row in sheet////
  Sheets("Sheet2").Select
    Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

My table to paste the data is on "sheet2" >>> Table4
Table header row is row 1 first column is A
0
Using Excel 2013 .....
I have a button that when double-clicked, enters today's date.
The problem is that it is entered on to the sheet in American format (mm/dd/yyyy).
For today, it incorrectly displays 12/11/2018 instead of the correct UK formatted 11/12/2018
(G46 Should read ... EE 0 Days Ago)

G47 is correctly formatted and Win10 Locale is set to UK
Why could this be happening?

(If I incorrectly set the format of the vba to American it works .... a most unsatisfactory workaround)

Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Range("G47").Value = Format(Now(), "dd/mm/yyyy")
End Sub

Open in new window


G47 Format
0
We have a client who is using Sharepoint instead of Network drives.

They have a shared spreadsheet which is an xlsx that they all have write access to so have the correct permissions for Co-Authoring. This sheet is used multiple times a day with multiple people editing.

The users are getting an 'unable to save due to others being in spreadsheet' bar appear nearly everytime they are in it. They then have to close the spreadsheet & reenter any data that was input.

I have already resaved the spreadsheet to xlsx to give them co-authoring. Am I missing something else that would be causing this issue?
0
Hello

Need help with a formulas to calculate:
1. (Cell DM23) Most recent past 6-week run rate of 52 week historical data that excludes zeros and high- low numbers (seasonality/Standard Deviation > X).
2.  (Cell DN24) Return data in Range $BM$22:$DL$3171 in column DN depending on current week written in Cell DM16

(see attached file)
Automate-Run-Rate.xlsx
0
How do I get data from a closed excel file into an outlook VBA collection?

The size of Sheet1 is N-row by M-column and data is sparsely populated.

N is not fixed (changing as rows are added to or deleted from the end of the Sheet1).

I would like to read data (including empty cells) from the entire N x M range.

The total number of collections is M and this number is fixed.

Kindly provide VBA code if possible.

Thank you.
Thomas
0
How can I change the below code into a function so that I can just plug in the following information?

Variables to substitute in code
ActiveSheet = Sheet name
PivotTable = Pivot table name
Value = Field name
Columns = Field name
Rows = Field name
Filter = Field name


Sub Rebuild_PivotTable()

    ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Open Raw Data", _
        Version:=6)
        
    'Clear all pivot table fields
    ActiveSheet.PivotTables("PivotTable3").ClearTable
    
    'Add to values field
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Incident ID"), "Count of Incident ID", xlCount
    
    'Add to Columns field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Status")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    'Add to Rows field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Site")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'Add to Filters field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("On Report")
        .Orientation = xlPageField
        .Position = 1
    End With
    'Apply filter
    ActiveSheet.PivotTables("PivotTable3").PivotFields("On Report").CurrentPage = _
        "TRUE"
End Sub

Open in new window

0
J1741_VisitTracker-_05Dec18_EE.xlsmHello!  I have the following formula which works no problem:

=IFERROR(OFFSET(‘SheetXX’!$E$5, MAX(IF(NOT(ISBLANK(‘SheetXX’!$E$5:’SheetXX’!$E$34)), ROW(‘SheetXX’!$E$5:’SheetXX’!$E$34),0))-ROW(‘SheetXX’!$E$5),-2),"")

It looks at a sheet, finds the last date entered in a column, and returns the value of the cell 2 to the left of that last date.
What I'd like to do is refer to SheetXX using an Indirect reference, which by itself, also works no problem

=INDIRECT("'"&$K$6&"'!$A$6")

so if the Indirect formula returns the correct sheet name, I thought it would make sense to replace 'SheetXX', with the indirect formula exactly as written.  
That's not working, no matter how many semi-coluns I add or change places in:

IFERROR(OFFSET(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5, MAX(IF(NOT(ISBLANK(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5:INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$34)), ROW(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5:INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$34),0))-ROW(INDIRECT(“‘“&$K$6&”‘!$A$6”)!$E$5),-2),"")

Any help is apprecaited.

Thankks
Matt
0
Just a question regarding the best direction and tools to use. I have published reports to sharepoint using PowerBi and the Microsoft Gateway and the schedule allows me to refresh the data to the web. All works well.  
However, I have an excel workbook with a pivot table linked to an SQL database. Company management would like to be able to view this spreadsheet remotely. I am looking for direction on what are the steps/ tools required to publish an excel workbook to Office 365/sharepoint and maintain the ability to refresh the data from the onsite SQL server either by request or on a schedule.
I have looked a the PowerBi matrix but it doesn't really give me the same level of flexibility as an excel pivot table.
Thanks in advance.
0
Hi all

I'm trying to find the last row number that has data in a specific range.  For example what is the last row with data between A5:A20?
0
Introduction to Web Design
LVL 12
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.

A user gets the message attached when they try and open an excel file (not sure if this is the same with Word) in Outlook.  When they "preview" the file everything then opens fine.  This also has been happening sometimes when they open a file from within documents.
Screen-Shot-2018-12-07-at-8.34.45-AM.png
1
i have outlook email with huge content. i like to search in that email say xyz
if i click control+F it is opening new reply email window.

what is the shortcut to search email content
not sure why there is no search button
please advise
0
Macro for footer in Excel keeps defaulting to default font instead of desired font..here's the code. The user wants the font to be Book Antiqua size 8 not the standard which it keeps defaulting to

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    ActiveSheet.PageSetup.LeftFooter = Range("G12").Text  End Sub

ActiveSheet.PageSetup.LeftFooter = "&8&""Book Antiqua"

Thanks as always :-)
0
Hello experts,


Sub Add_Specific_Char_First_Position()

Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long
Dim intPos As Integer
Dim strPrefix As String

strCol = InputBox("Please enter the column letter in which you add specific string", "Choose Column Letter")
lngLastRow = Range(strCol & "1048576").End(xlUp).row
strSpecificChar = InputBox("Please enter the character that you want  to add in first position", SpecificChar)

For lngRow = 1 To lngLastRow
Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value
Next

Open in new window


I have the following procedure which allows me to add character in first position.
I would like to reviewing as follows:
1-Instead of adding character in first position I would like to add character in last position
2-Instead of adding the specific character in input box, I would like to refer to an specific cell such as.
“Please select the cell in which you want to add the specific character”.

If you have questions, please contact me
0
Hello,

I have an Excel document which contains computers in column B, C, D.  I also have a separate group of computers in column E.

In the column A, I want to put notmatch next to computers which is in column B but not in column E.  

For example, computer1 is in column B but not in column E, so I want a "notmatch" next to computer 1 in column A

Please see the attached and advise how to do it.  

Thank you very much in advance.
test.xlsx
0
hello all,

How to create dashboard from excel.
I have data in my excel with different categories, how can I turn them in dashboard?

Regards
0

Microsoft Excel

135K

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.