Microsoft Excel

134K

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:

In a previous post (https://www.experts-exchange.com/questions/29114146/Add-remove-trendline-to-Excel-graph-through-VBA.html?anchor=a42656598¬ificationFollowed=211366882#a42656598), I needed assistance with developing VBA code that would allow me to add/hide trend lines (via command button) to an existing histogram.
Expert Subodh has provided a superb solution that exactly does what I needed.

Now, I am requesting assistance with modifying the existing code (see attachment) where a single cmdButton allows to add/hide trend lines to all histograms on the worksheet.   Please see additional details in the attachment.

Thank you for your help in advance.
EEH
Command-Button-to-Add-Trendline-v03.xlsm
0
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 am working with a very arcane program that uses nested IF statements to calculate product quantity to consume.  The formulas are based on "choices" (hence the C1, C6, C7, jargon) made within a "configurator".  Not being an expert with the nested IF thing I have to create a spreadsheet with all of the criteria, color code all the possible answers and then work my way through it.  Usually I can stumble my way through these to a working formula... unfortunately...  I've run up against a formula that two of us here just cannot seem to resolve.  The formula has to be written   in the format
IF(C1=6,IF(C6<=2,IF(C7<=13,2.5,IF(C7>24,2,IF(C6>2,IF(C7<=13,.5,IF(C7>24,0,.....

Open in new window

as it does not accept the more Excel and SQL like IF, THEN, ELSE syntax.
Whatever help you can offer will be greatly appreciated as I've expended nearly all of the brain cells I have left on this.  :-)
Screen capture Excel cheat sheet
0
Doesn't Trim work within formulas?

I got a SUMIF like this
=SUMIF($A$18:$A$1006,A2,D$18:D$1006)

It looks like some of the text in the range $A$18:$A$1006 have trailing zeros! So they are not included in the SUMIF.
I tried this, but it doesn't like it
=SUMIF(Trim($A$18:$A$1006),A2,D$18:D$1006)

Why???? Isn't that just a nested formula?

Thanks
0
I want to select the least date of each dates per month in a range.

e.g. My column A has dates

16Aug18
16Aug18
16Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
14Aug18
13Aug18
13Aug18
13Aug18
11Aug18
10Aug18
10Aug18
10Aug18
10Aug18
9Aug18
9Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
7Aug18
7Aug18
6Aug18
2Aug18
1May18
16Apr18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Apr18
15May18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
15Aug18
14Aug18
14Aug18
13Aug18
13Aug18
13Aug18
13Aug18
13Aug18
13Aug18
10Aug18
10Aug18
10Aug18
9Aug18
9Aug18
9Aug18
9Aug18
9Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
8Aug18
7Aug18
7Jun18
7Jul18
7Apr18
6Apr18
6Aug18
4Aug18
4Aug18
3Aug18
3Aug18
3Aug18
2Aug18
2Aug18
2Aug18
2Jun18
2Aug18
2Aug18
1Aug18
1Jul18
1Aug18
1Aug18


I want to select the least dates for Apr, May, Jun, Jul and Aug
0
I'm trying to set up a spreadsheet that calculates commissions, using a sliding scale. Frontwards is easy: Take the value of the sales. Use the base commission, and add it to the sliding commission based on a lookup on the bonus, based on the sales value. No problem.

But I'd like to also be able to do it backwards. In other words, I want to know that if I want to make $X commission, then this is how much I need in sales. But it has to factor in that same sliding scale. And I don't know how to manage that. I'm attaching the spreadsheet here.Commissions.xlsx
0
I have 2 macros in this file. 1) to sort by comment colors, then ship date and 2) sort the certs bucket by ship date only. When I run the macro it runs it for the buckets with the exact cells I highlighted  when I created the macros. Currently every time I want to sort the other buckets by option 1, I have 5 steps which I thought creating the macro would eliminate that so I didn't do something correctly but not sure what. I want to be able to use a macro to sort any of the buckets by option 1 no mater how many rows are in the bucket. If you would also explain to me what I did not do correctly that would also be helpful. Thx You
FINAL-CERT-SUMMARY---MACROS.xlsm
0
Hi All,

I want to delete all the review comments in excel at once. I mean, I have multiple tabs and each tab has review comments, it is time consuming task to run through all the tabs and use clear comments option.

Can I get VBA to delete all review comments in entire workbook at once ?
0
* We are looking to generate a report type output. Again there are many pages we need to draw information from.
* We would like to add a button onto our Owner List that when pressed would generate the output at the top of sheet Device Report.
* We created a macro that extracts all the data but it really does not do what we want.  
* I have highlighted the information at the top and where we got it from the list below.
* I have added two pages one called "Start" and the other called "Finish" the pages of interest are between the Start and Finish.  I have been told that the pages can be arranged in any order. Also the Start and Finish pages are there for reference and can be deleted.
* On the individual pages there is a column called "Complete?", should that column contain the word "Wave" it is considered complete and should not be included when the macro runs, we only want non-completed items to be returned.
* On the "Device Report" page that gets generated they would like to input the "Maintenance Window" Information and possibly be able to write it back to the page and row it's associated to.
* They would also like to update the "Notes" back to that page like the "Maintenance Window"
9K-Tracker-8.16.18-V2.xlsm
0
I am attempting to setup Rest API in VBA and need some help walking through the steps. Can anyone help me by walking me through step by step for that I can learn how to do this for future projects?
0
Hello
Can you please help with the attached file if possible.

The attached file is macro enabled file and I need to protect all cells contain formulas ( highlighted in yellow color ) and any additional cells will contain formulas in future too.

i tried to protect the sheet but the Macro will not work unless I remove the protection.

 Any ideas how to protected the cells without  and keep the macro working in same time.
Filter-Test.xlsm
0
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 to manage error handlers in importing mails from outlook to excel through VBA?

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sales")
i = 1
   For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime = Range("From_date").Value And OutlookMail.Subject = (" updates")  Then
'        Range("eMsail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
'        Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body
       
        i = i + 1
     End If
   
Next OutlookMail
 msgbox "operation done!!."

 If i = 0 Then
' OutlookMail.ReceivedTime <> Range("From_date").Value Then
        msgbox "No Login/Logout found for the given date"
End If

But it is not working properly .

Here i need if mails are available for mentioned date it has to show success message  and if no mails are available it has to show message as no mails.

Please help!

Thanks in advance..
0
I sometimes get into a mode where I cannot copy text from a vba program into some cells in my worksheet

When I use ctrl c to copy then ctrl v to paste, an older clipboard entry was pasted.  (I was very confused at first, but I eventually figured out there was a problem with the office clipboard.

This problem used to happen once every several months, but lately, it has been happening every few days.

For instance the Office Clipboard looked like this:
 office clipboard
I can click on the lower entries and they paste fine. But, when I click on the first entry, nothing happens.

If I close Excel, the reopen it, everything is back to normal for a while.

I will eventually try fixing this with  an Office repair, but right now I just want to know if anybody else is having this problem ?
0
Does anyone know the exact Excel formula for calculating Calories burned (from example, jogging or running)?
For it to be entirely accurate, I would think this formula would need to include:
-- Age
-- Gender
-- Weight
-- Average Heart Rate
-- Distance
-- Time Run

 ... and perhaps even:
-- Elevation gain
-- Weather/Heat Index
-- Other variables.

Thanks in advance!
0
How is it possible for me to concatenate several cells in Excel without using using CONCATENATE with lots of arguments, e.g. CONCATENATE(A1, ",", A2, ",", A3, ",", A4, ",", A5, ",", [....]) ?  I have several dozens of cells to combine in this way and I am wondering if there is a quicker way. Thanks.
0
Loading.Net Framework

it appears this message appears in the status bar only for certain workbooks. Not for all of them, regardless if I have macros in the workbook or not.  
Do I have control over it? if yes, how do I turn it off as it appears it is slowing down some of my workbooks or get in conflict with other programs.

Any help with the above is greatly appreciated.
Thanks
0
Update Word Document with Data entered in Excel
sp
I have Excel sheet displayed on the screenshot above.
I also have word documents with the name that matches exactly the name of each Comp-Name.

BATH.docx
MONR.docx
STAR.docx
JUSGB.docx




Each word document has a table with the same column names as the Excel sheet

I would like to have the respective word document table updates automatically  its data to match the new entered data in Excel .
for instance If I have a new purchase, I can manually add it to a new Excel row, and the program should add that new row automatically to the corresponding word document.

Example:
I have new purchase for a company MONR
On Excel , I will add new entry:
MONR      TX      3/3/2018      3/8/2018

on a corresponding word document MONR.doc, I should have the new entry added automatically to the table in word.

Any help will be very much appreciated.

Thanks
0
=IFERROR(NL("First","7503 Item Attr. Value Translation","5 Name","2 ID",AC8,"4 Language Code","FRE","Company=","Design"),"CONTACT IT FOR TRANSLATION")

Hi I want to add to this Excel formula so that if it returns blank and cell U8 is not blank it will also return "CONTACT IT FOR TRANSLATION" as in the iferror workings

so sort of

=IF(ISBLANK)AND U8 <>"" ,then   "CONTACT IT FOR TRANSLATION" else      IFERROR(NL("First","7503 Item Attr. Value Translation","5 Name","2 ID",AC8,"4 Language Code","FRE","Company=","Design"),"CONTACT IT FOR TRANSLATION")

So it can be blank but if it's in error or it's blank and U8 is populated it returns the text.

Just not sure how to correctly get it into the formula - any brains out there appreciated.

Thanks
0
Sub Link()

Dim Turbidity As Long
Dim RawTurbidity As Range


'Sets variables Turbidity being the ActiveCell and RawTurbidity refering to the last captured cell in raw sheets'
Turbidity = ActiveCell.Row
Set RawTurbidity = Sheets("Raw Data").Range("C4").End(xlDown)


'The formula assiging the last captured cell in Raw sheets to the active cell'
Sheet1.Cells(Turbidity, 4).Value = RawTurbidity.Value
End Sub

Open in new window


This is my code and it does pretty much what I want it to do. I have an instrument that spits data into column C in Raw Data sheet. What this macro does is copy that data into column D in Sheet1. I have another small Macro running on Raw Data that calls this macro whenever a change is made in column C in RawData and that's what activates this macro. Anyway what I'm trying to do is after the calculations have been copied to have the activecell automatically go to the next row just like pressing enter when your in a cell. Typically ActiveCell.Offset(1,0).Select would be the line for that. For some reason when I add that to the end of the macro it copies the data into the next four rows.

In case you were wondering the analyst don't do the analysis on sheet1 in order hence why I had to have Raw Turbidity copied into the activecell instead of just doing the available blank line.
0
A general question. I have often wondered, when VBA is doing a lot of stuff on Excel you get a 'not responding' message while the screen goes haywire (oddly). But given a few about 10 seconds it comes ok.
This happens even if I turn Application.ScreenUpdating = False

Question 1: what is the reason for this? Not enough RAM?
Question 2: can it be prevented? (as it scares users, and me!)

Thanks.
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

I had this question after viewing "This file has been renamed. We recommend saving it now to get the new name." When opening Excel files from WebDAV in Windows Explorer.

Similar issue: A user is opening any excel file from a mapped drive and is getting the error "This file has been renamed. Please reload the workbook". Only options are "Reload workbook" or "Later".

If the user opens SharePoint (2010) and finds the same folder and tried opening it from there, there are no issues.
excel.JPG
0
I have a strange behavior with a recorded macro.

I recorded this macro to see the color to highlight a header row. As you can see, PatternColorIndex is -7. Minus?
Sure enough, when I run it it says 'subscript out of range' on that line.

    Range("D15:G15").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = -7
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With

Is this a known thing?
0
This does what I want, but I need to extend it a bit.
Range("A16:AH6000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AM1"), Unique:=True

Open in new window


I need the Range to be already filtered by this criteria. Field 26  = "Application"

I tried adding Field:=26, Criteria1:="Application" (a wild guess from AutoFilter!) but that did not work.

Thanks for any help.
0
I have a procedure where I open a workbook (Source.xlsx), do some clean up including Delete some Columns, then Save and Close.
I'm experimenting to see if I can do that with the Source.xlsx 'hidden'. (to try and make the steps in that clean up quicker/cleaner/not confuse the user)

This is my code
Sub Macro1()
    
    Dim wkbSource As Workbook
    Dim wkbApp As Workbook
    
    Set wkbApp = ThisWorkbook
   
    Workbooks.Open Filename:= _
        "C:\Hidden workbook test\Source.xlsx"

    Windows("Source.xlsx").Activate
    
    Set wkbSource = ActiveWorkbook    
    
    wkbSource.Windows(1).Visible = False
    
    MsgBox wkbSource.Sheets("Sheet1").Range("C4").Value
    wkbSource.Sheets("Sheet1").Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
        
    wkbSource.Windows(1).Visible = True
    
    wkbSource.SaveAs Filename:= _
        "C:\Hidden workbook test\SourceOutput.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

    wkbSource.Windows(1).Close
    
End Sub

Open in new window


I'm finding that, if I save when the workbook is Visible = False then when I Open the Saved SourceOutput.xlsx I is INVISIBLE, but Excel thinks it's already open.

And, the Delete Column is erroring.

Question: am I trying to do something impossible? ie. run code on a workbook that's not Visible?
And (!) can a workbook really be saved in a Visible = False state?!!! for it to Open 'invisible'? (or am I hullucinating?!!!)

Thanks
0
I am trying to find a formula to find the number of concurrent calls at one time. My columns contain phone provider, outbound number, inbound number, start date and time (one cell), duration of call, and end date and time (one cell) from A to H in the order they are listed. In this stage of the project, inbound and outbound numbers are not important and aren't necessary to include, nor are the phone providers or the durations. Some of the calls last several days, so that will have to be taken into account. There are almost 20,000 rows in the sheet, so it would be ridiculous to do it manually. I am essentially just looking for how many phone calls are active at one time. If there is a formula that can help me do that, that would be great. I have already tried many =COUNTIFS and =SUMPRODUCT formulas, but they are not giving correct results. This project is due fairly soon, so any answer could be helpful.

Thanks!
0
I have a Named Range "myRange" which refers to "C10:G20"

What would be the coolest way to refer to the left-most column of the range? (Which would return "C10:C20")
ie. Range("myRange"). ????

Thanks
0

Microsoft Excel

134K

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.