excel 2016

  1. I

    Why does this JSON Microsoft Script VBA code work in Excel 2016 but takes forever in 2019?

    I have this JSON Microsoft Scripting VBA code that pulls data from a work website into Excel. Can pull about 55,000 rows of data. About 5 columns of data. On 2016 the code worked fine but on 2019, which I need the code on, it takes forever to run or crashes Excel. I then have to quit Excel. I...
  2. E

    Formula help return value based on criteria and date

    Hello, I am trying to create the formula below and hitting a brick wall again. Any help would be greatly appreciated. if A1 = Green and A2 = - then good if A1 = None and A2 <> then bad if A1 = Amber or red and A2 <> - and A2 is less than today then bad else good
  3. E

    Excel formula to return value based on certain criteria

    Hello, I am using excel 2016 and trying to create ta formula that will return a certain value based on multiple criteria. I have set out the logic below but everything I try doesn’t work :mad: if(and(a1=“Green”,a2<>”-“),”Good”,”Bad” if(and(a1=“Amber”,a2<>”-“),”Good”,”Bad” if(and(a1=“Red”,a2...
  4. M

    Excel Power Query - Change Folder Location (using MS Office Professional Plus 2016 - 64 bit version)

    Hello, I hope someone can help me with this query: Each month I run a set of Excel Power Queries to pull data from 50 files into a consolidation model. Each of the Power Queries pulls through different datasets from the 50 Excel files, e.g. 1 would be for financial data, another would be for...
  5. L

    Perform Calculation in VBA

    Hi, I wrote a code in VBA for calculating the simple formula as gaussSum= [n*(n+1)] / 2, the number is given by the user. But the program does not perform the calculation it just gives me the number inputted by the user. How can I connect these two, so the program could take that number and then...
  6. Johnny Thunder

    VBA Help - Create Conditional Formatting to Sheet

    Hello Everyone, I am stuck on a project that requires a sheet to get a refresh to the conditional formatting to a sheet. Whenever a user Adds Rows to a sheet or duplicates the sheet the conditional formatting seems to get messed up by duplicating rules and changing my ranges from being entire...
  7. R

    Days Aging Formula (# of days overdue)

    I have a list of past due invoices. I have a PO Date (column f) and I am wanting to add a column for Aging Days (column H) that will tell me how many days have gone by from the PO date. In column I, I would like it to tell me if the invoice is past due or not past due. Any help would be...
  8. G

    Message box List

    I have been searching with no avail on a vba to display a msgbox with specific data. I have an idea but the specific throw me off (still a beginner). This message box will display a value1 then tab then value2 then tab value3 then new line value1-2, tab value 2-2, tab value 3-2 and so on until...
  9. richh

    Power Query Data Source - Not Pulling from Sub Folders

    MS Excel 2016 This is the first time I've used a query, so I could very well have constructed the applied steps incorrectly. My problem is that records from newer files are not present. The query is designed to compile lists of reviewed records from weekly reports. They are the same lists...
  10. A

    Largest Value based on Multiple criteria

    Hi there, I'm working with a data sheet and am trying to get the Nth largest value in a specific column based on multiple criteria. My single criteria formula works fine, this is it: ={LARGE(IF('Sheet1'!C:C=3,ABS('Sheet1'!W:W),""),1)} However, I run into the #VALUE! error once I add another...
  11. Johnny Thunder

    VBA Help - Check if multiple String Variables are Blank

    Hello All, I have a small script that takes values from one sheet and populates other tabs based on specific criteria. I have a small declaration of variables that I am trying to validate that there are values within the variables, if not I want to change the variables to "N/A" as the...
  12. mehidy1437

    Formula to extract first character from right side but before the numeric data

    Hi guys, I want the below result from the string, how can I do this with formula? AB1STRINGREQUIRED RESULT2BBBA123A3BB1234B4CC3C5DD123CD6F11CCD32CD7F11CCE32CME8F11CCF3CMF9F11CCG345CG
  13. B

    Create a list of all peers from a given Employee List

    Hi, Newbie here with very basic knowledge on how to use VBA (not knowledgeable enough to create VBA code). I need some help in creating a list of all peers for a given employee list. Below is a sample: Employee List Emp Name Manager Name A1 M1 A2 M1 A3 M1 A4 M2 A5 M2 Output...
  14. richh

    VBA Loop Through Groups of Option Buttons on User Form

    Excel 2016 Windows 10 I'm trying to loop through all my controls on a user form to ensure they've been completed; I'm confused on how to check to see if one option button has been selected in a group of buttons. Each button is coupled with only one other button per group name, so if either one...
  15. U

    VBA Combining Data and Create Pivot

    Hello Gurus, Hope you are doing well despite the pandemic. I was wondering if you have any idea on how to code this: 1. I have a worksheet with 5 tabs or more (may vary). The headers are same (see below): Number Name Sales Div Data Note 1 Sam SA 100 2 Amy FA 90 2. I want to create...
  16. S

    Log into FTP folder and download the latest file to my local folder(Based on created)

    I'm trying to access FTP using hostname, username & password. need to access the particular folders and copy the latest file (Based on created, as the filename may vary each time) to my local path. Copying the latest file is working using the below code. Could someone probably help me to append...
  17. richh

    Defining Order of Controls in User Form for Loops

    I am using Excel 2016. I am attempting to loop through a set of controls within a frame in a specific order and writing their content to a worksheet. I've tried defining the tab indexes, which seems to do what I need it to, for the most part, but a few controls are not looping in the correct...
  18. G

    Excel 2016 and Power Query issue

    Hi everyone, I developed a file on O365 using Power Query for my wife to automatize some tasks. On her laptop the file with power query would not work (using Excel 2016). I first thought it was linked to a version issue but then I did the following on her professional laptop : Close and...
  19. M

    Offset "Application.Match" result to find destination cell

    I'll try to be as brief as possible. Here's the rundown. Setup: I have 2 Workbooks. I'm trying to loop through Workbook#1 and for every cell that contains a blue border, I then want to search for that cell value on Workbook 2 (a result SHOULD always be present). Once the result is found, I...
  20. M

    Multiple Column Loop from wbA and copy/paste all data to 1 column on wkB

    Hello all, I'm trying to create an array loop that goes through columns A1,D1,G1, J1 (Keeping the headers) from one workbook and paste all the data to one column (keeping cell formatting) on another workbook. The data in the copied columns (A,D,G,J) will vary every time the macro runs so I'm...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back