Questions tagged [excel]

Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.

Filter by
Sorted by
Tagged with
0
votes
0answers
24 views

VBA loop optimization

Hi folks: I posted a SO (https://stackoverflow.com/questions/57541246/optimizing-vba-function-loop) and was told to ask here. I am starting to think that rather than relay on excel as my data ...
2
votes
0answers
30 views

Compare two Excel sheets by Cell Content

This code is a result of a lot of help from this community. What does it do? It compares two excel sheets cell by cell and copies the differences into another excel file. I’m trying to modify the ...
1
vote
0answers
24 views

Modify code to compare two files and link to user-form

Due to the help of the community here I ended up with a working code that serves its purpose. The code itself compares two sheets and then creates a report file with the differences between the sheets....
4
votes
4answers
122 views

Find differences between two spreadsheets

I have this code that is supposed to compare two Excel sheets. The code is working fine for small comparisons. I did a test run with 7 rows and 2 columns. The code itself works as follows: it ...
-2
votes
1answer
24 views

Working Excel VBA code runs and finishes, but gives “not responding” while waiting, could use a speed boost [on hold]

My code below works, but I'm sure could use some help to speed it up. I'm using an adodb connection with sql to get info and put it in a userform. Any suggestions would be appreciated. ...
1
vote
1answer
23 views

Clean up code that transfers data from DB2 Server to Excel

The code below is functional and works as expected, but I imagine there is a better way to test for the error that I am testing for . The scenario is that data is transferred between two different ...
4
votes
5answers
91 views

Turn On/Off Functions to speed things up

I have these two macros that are like my Yin & Yang. They turn off and on functions in excel so my macros calculate quicker. I call on in the beginning and one at the end. Is there anything I ...
0
votes
0answers
55 views

Excel VBA - How to optimize this code with a lot of inserts

Can you please suggest how below code can be optimize? It takes around 2 minutes in Windows and 4 plus minutes in Excel 2016 for Mac. I think I will have to use write once method, accumulating the ...
0
votes
0answers
40 views

Can Excel Vba code be updated via an Excel Add In for multiple users

I have created an XLAM file called "MyCustomAddIn" from an XLSB file called "Call Tracker" which contains all my Subroutines, Functions and UserForms. I have referenced "MyCustomAddIn" inside of "Call ...
3
votes
1answer
67 views

Excel VBA code that copies data from one sheet to another

I have written a VBA code that copies data from source sheets into destination sheets. The destination sheets are formatted as a table so that formulas can be calculated automatically to update a ...
4
votes
1answer
39 views

Exporting array of tabs to new workbooks, save them based on tab name

Brief: This is a question geared towards optimization and general better coding practices. I'm self-taught in VBA so I'm cautious of bad practices, but they do come up. My Reasoning: I break the ...
6
votes
2answers
69 views

Statistical User-Defined Functions (UDF)

Problem The following statistical functions were created to calculate statistics of experiments: Mean Peak to Peak Standard Deviation Variance Mean Absolute Error (MAE) Mean Square Error (MSE) Root ...
0
votes
0answers
66 views

Excel: read data into ADODB recordset

I wrote the function below, but I feel it could be improved and so I am open to your wise suggestions. The use case for this is to manipulate Excel data. As a freelance dev, I cannot always choose the ...
2
votes
1answer
39 views

Reads files into 12 different sheets & cross compares files with audits and charts it out

I was wondering if anyone has any improvement ideas for my code. At the moment it is somewhat sluggish though it does work. This is my first project in VBA; I come from a background in PHP/Python. <...
8
votes
0answers
86 views

Not so Excellent Knight's Tour

Note: This is part of a set of demo applications I'm writing for colleagues to show we're not limited to VBA when dealing with Excel. Considering our IT challenges (no admin on-site & no local-...
1
vote
2answers
56 views

Macro for transforming 2 way matrix into 1 way matrix

I have written some code that transforms 2 way matrices into 1 way matrices. Although it's comfortable to use, it's very sluggish and slow in execution. Does anyone have any idea how to improve the ...
3
votes
3answers
101 views

Brute force looping & formatting Or Create Union range & Format? Which is efficient and when?

May be my question is not up to the standard for Code Review, but of upmost importance for reassessing methods used in my VBA coding . While preparing/testing answer for a SO post (thought to simplest ...
3
votes
1answer
75 views

Updating yearly reporting templates, ideally before the next year starts

Work flow is: Copy Previous Year Reports Folder Update Following Templates Save Close There are 19 workbooks in the Source Folder and in the NewYear Folder. Each Workbook has to save the final data ...
3
votes
1answer
63 views

Matching and Merging CPU info between worksheets

Goal: Transfer Sheet Test1 2nd Column to Sheet TestS 2nd Column. Where: For each CPU in Test1. If CPU name in Sheet Test1 ...
0
votes
1answer
44 views

loop code through each slicer value selection

I have a PivotTable with a slicer that has 5 filters. I'm looking to have these filters to each be selected individually one by one and run a copy/paste code for every filter selected. I've set up ...
2
votes
1answer
46 views

Store subranges from contiguous range into array based on criteria [closed]

My code parses through a contiguous range of data in a spreadsheet (orng) and creates subranges from orng that contain the same string value (lot number) in the 6th column of orng. orng has 42 rows ...
0
votes
1answer
48 views

Generate an ID number for each new row of text that is pasted into the worksheet

This code checks if "B2" and so on is filled in and if it is then it will generate a new ID number for the row of data that was transferred to the worksheet on the next empty row, I know this code ...
3
votes
1answer
46 views

ERP-system with UserForm that transfers data to next available row in an archive spreadsheet

I made this huge code (Edit: huge for such a simple task) for transferring data and adding an ID number to every new row of data. The only functionality flaw I see is that it creates new ID numbers if ...
4
votes
2answers
59 views

Copy Multiple Excel Tables to VBA Dictionary

I have a number of Excel tables that I want to populate into VBA dictionaries. Most of the code is repetitive but there are some unique things for each table that prevent me from making one general ...
4
votes
2answers
136 views

Print a pattern of text based on a line from an Excel file

I started to learn Python not long ago to automate certain tasks I work on. I want to optimize a script I wrote that takes a line in an Excel file and prints a pattern of text based on this line's ...
0
votes
0answers
26 views

Filter by Date, summarise and paste data between spreadsheets

I have written the below working Code to speed up the summarisation of transaction data into a reduced format whilst also checking and providing feedback where there is no data to transfer. Overview: ...
1
vote
1answer
53 views

Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop

As said, this is take 2 (see linked for Take 1 for my massively beginner code: https://codereview.stackexchange.com/questions/223236/) As an overview: I manage the bookkeeping for 40+ companies in ...
1
vote
0answers
58 views

Speeding up formatting of all comments in a worksheet

I have recently added a subroutine, CleanUpComments, to be called from multiple other subroutines to clean up comment positions and sizes. It works, but it is extremely slow taking about 10 seconds ...
2
votes
3answers
74 views

Faster output to Sheet from a collection of Dictionaries

My code is currently offloading parsed data into multiple spreadsheets, but it's pretty slow: about 110k rows and 35 columns in 3.5 minutes. I was wondering if there were a more efficient way of ...
2
votes
2answers
72 views

Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop - EDIT new/better code posted as new thread

EDIT: Improved/More Streamlined Code (rather than spaghetti below) now posted here: Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop I'm a relative newbie to coding and have "...
3
votes
0answers
83 views

Rich data types - boolean object

Context You can probably skim most of this, it's unlikely to be useful for a review and is just for background info. Also download the files I've tried to create a ...
3
votes
2answers
47 views

I'm reading all lines of another sheet and I paste the value that I want to the active sheet VBA

I have a program that read thousands of lines and return the value that I need. The problem is that request take a lot of time probably like 1min just to search and paste the value, even when I'm ...
0
votes
1answer
64 views

Excel table filling with EPPlus

I am making a tool to read 3 input files in CSV and then filling an XLSX file with the information from the CSV. I want to know if I am repeating myself and if I can make it better and faster. ...
1
vote
1answer
57 views

Copy-Pasting (kind of) from Excel to an existing Word table

What I'm trying to do here is to open an Excel file and then search for and store the data I need (i.e if there is a reference then I copy 2 columns). Afterwards, I paste or write that data into a ...
2
votes
1answer
57 views

Excel - Visual Basic For Applications - Mastermind Game

For those who are unfamiliar with the game: https://en.wikipedia.org/wiki/Mastermind_(board_game) A link to the workbook: https://github.com/Evanml2030/Excel-Mastermind Had trouble getting the ...
3
votes
1answer
77 views

Update a table based on other table

I created this simple macro that works fine for its purpose: allow the user to create a list of updates that need to be made based on groups in a target table (to basically do mass updates). However,...
3
votes
0answers
121 views

Alternative for VLOOKUP/Index-Match in VBA

I am sharing this function I wrote with the community to provide an alternative to slow VLOOKUP and/or Index-...
4
votes
1answer
65 views

Collecting all relevant data from xlsm files

Is there a way to speed this guy up? The code is designed to go into a directory, open up all of the .xlsm files in the folder, and copy specific data into a target file. This code works as is, but it ...
5
votes
0answers
64 views

Parsing Long Excel Report Files for Predefined Results

I work as an IT intern in a multinational and I was given a tedious task of combing through a 2500+ long multi-column excel report file in search of inactive servers. Here's a sample row from that ...
1
vote
1answer
33 views

Django export to xls format

I'm working on a Django project and I'm wondering if there is any way to improve the process time to export my data to xls format. Actually, it takes between 43 seconds and 49 seconds to create the ...
3
votes
1answer
73 views

Most efficient way to loop through files using VBA Excel

I have a procedure for looping through all files in folders and subfolders starting at a folder which the user can select. The user can select both the source and target folder. I'm using Excel VBA ...
2
votes
0answers
43 views

Node JS | From Excel to Json

Intro -> I have to convert an excel file, which comes usually in this way: to a Json file that must looks like this: ...
2
votes
1answer
73 views

Change case the data inside a column

I created a code that import the data regularly into a selected workbook. The code run well, it takes less than 20 seconds before the data is imported successfully. How does it work? I get a monthly ...
5
votes
0answers
54 views

Search for used Range.NumberFormats in Excel workbook

On an overly abused workbook we've hit the limit and NumberFormats can no longer be added. If we do want to add more we need to consolidate current formats. My code ...
2
votes
1answer
69 views

Excel VBA Userform to assist in the use of Index Match function

I often use the Index Match function in daily reporting tasks. I was looking for a quicker way to utilize Index Match, as I find the formula cumbersome to enter. I ended up creating a userform that ...
3
votes
2answers
33 views

Excel To CSV Converter Ch 9 ATBS

I am learning more about python using the book "Automate The Boring Stuff". The problem I completed was "Excel To CSV Converter". I want to kick bad habits to the curb, and make sure I'm learning the ...
2
votes
1answer
51 views

VBA code to underline numbers in the header takes significant time and sometimes crashes excel

Background: For several of my workbooks I run Overview sheets with charts in them. When printing the Overview sheet, I put my workbook name as header, where the workbook name includes the criteria ...
6
votes
2answers
335 views

Speeding up Excel distance calculation using Bing API calls

I am writing VBA in Excel to calculate the distance between an employee's home address and work address using Bing Maps API calls. The process follows this general flow: 1) Convert the employee's ...
3
votes
2answers
87 views

Speed Up Excel Macro - Autofilter Copying

I am using this code which works fine, but runs painfully slow. The code filters an Excel Table and then extracts only certain columns and pastes them into another sheet (in a different order). ...
1
vote
2answers
106 views

Calculations for trucks

How can I improve my VBA code to run faster? The issue I'm having is that the For/Next commands are taking ~10 hours to calculate and print all of the data. I ...