Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
-5
votes
0answers
24 views
How can I optimise this code? [on hold]
Everytime, when cell value (1,2) changes it will copy that value and the value of cell (10,19) and paste it in column A & B respectively
Excel hang when the value of cell (1,2) changes:
...
-2
votes
1answer
24 views
Clean up VBA search?
I have code, that is identical minus two changes that would happen each time, which I can use variable for, but I dont want to use a bunch of if statements.
the code I have is
...
-2
votes
0answers
13 views
I'm, trying to create a form to log events. How do I create an If response to direct data to different worksheets dependent on response [closed]
I'm trying to create a logger that separates data into different excel worksheets depending on a list response. At present the code below creates a singular log on a worksheet. Any help would be ...
3
votes
2answers
60 views
Multi-dimensional ascending and descending sort array function
I'm back with improved version of previous Deca dimensional array sort function. This one is not just 10 dimension limited. It could be extended to unlimited number of dimensions by simply adding rows ...
2
votes
1answer
44 views
Data-collection in Excel-VBA using nested dictionaries
Introduction
I have a spreadsheet in Excel, looking like this:
I collect and sort the values in the column labelled "Antal dagar venting", based on the values in the columns "Nivå 1" and "Nivå 2".
...
13
votes
1answer
123 views
Template UserForm Input - launch, input, validation, error handling
I'm building a template [Process-Tracker] spreadsheet.
The idea is that, for any process where we can define what steps should occur in what order (and preferably, how far apart), there will be a ...
3
votes
1answer
49 views
VBA - refactoring multiple nested if statements
Here is some code which loops through an area in a spreadsheet and executes the code based on the condition that the source cells do not contain the value "(blank)"
The code works, but its very ...
3
votes
0answers
24 views
Getters, constructors and eval
I'm trying to parse an object generated from an Excel file. The output lists each cell with its contents. If the cell is just a number then it remains a number but if the cell contains a formula (has ...
7
votes
2answers
140 views
Deca dimensional ascending and descending array sort function
Some folks told that a two-dimensional array can solve almost all the problems, but let me disagree with that. That's why I made this Deca dimensional array sort function. The array must be ...
2
votes
1answer
36 views
Importing Excel file to a database just once
How should I rewrite an if-return-else condition in the middle of this function?
...
8
votes
0answers
45 views
Creating a pseudo Pivot Table / Database using a 4-D array
Why am I not just using a Pivot Table / Database?
a) I've never ever used either before. And I don't have time to learn how before this project needs to actually be finished.
b) The final output ...
4
votes
3answers
68 views
Analyze very large sets of engineering data from Excel files
I am an electrical power engineer with some programing skills. My boss asked me to make a program which could analyze very large data, make some calculations and give the result.
The task lookes like ...
5
votes
1answer
47 views
Defining array contents using multiple statements on one line
I'm writing a sub which is going to determine which type of business is being detailed on a particular line of data. It's going to use column positions to check if the data exists, and then output the ...
7
votes
3answers
48 views
Re-worked Workbook_Open, Creating DateStrings
So, I had a go at re-writing my Workbook_Open event based on The advice from my previous question. Thoughts?
...
10
votes
2answers
108 views
Financial Data From Webqueries in Excel
I'm new (to CR and to programming in general). I wrote my first VBA on Monday. This is my first working project. It Takes a bunch of financial data from a company called Financial Analytics and a ...
5
votes
0answers
39 views
Aggregating data from multiple worksheets into a consistent format
First off, this is the 3rd (and probably last) review of the project in question. You can find the previous question here.
The Macro accesses a workbook containing 8 worksheets each with similarly ...
10
votes
1answer
77 views
Standard Methods in VBA
Since I learnt about refactoring, I've been busy creating my own Module of Standard Methods. I feel it's about time they got a review of their own.
Open to all aspects of advice but particularly ...
8
votes
2answers
74 views
Aggregating 2 lists by matching UniqueIDs
This Macro takes my company's 2 main sources of all-client data (each a data table in a separate worksheet) and aggregates them into a 3rd list, by matching a unique account number (for clients that ...
5
votes
2answers
63 views
Filtering and aggregating data from a multi-Worksheet Workbook
First off, this is version 2 of a previous question about the same macro: Previous Version. Thank you to Raystafarian, RubberDuck and everyone in chat who helped me make it better.
The Macro accesses ...
3
votes
1answer
68 views
Bot that selects web reviews based on spreadsheet entries
In my program, I prompt the user to enter a range of cities (in a spreadsheet), and then a range of review scores, and then make some decisions based on what the user has entered. Since the handling ...
4
votes
2answers
163 views
Loop through column cells and compare value with a Collection
I have a Collection of 166 elements:
...
7
votes
2answers
69 views
Long running time on VBA macro which filters and splits dataset into new workbooks
The VBA macro below uses a worksheet (source_data_worksheet) to filter and split out the records based on about a dozen unique column (D) values in a dataset of about to separate workbooks which are ...
5
votes
1answer
67 views
Matching between files and a list of filenames at scale
Given a folder typically containing ~250,000 small (20-100kb) files in HTML format (they open as single-sheet workbooks in Excel) and a list of ~ 1 million filenames, I need to open all the files that ...
2
votes
1answer
77 views
Finding the position of a string in a dimension of an array
Given an array and a string and bounds within which to search, I need to find the position of the string within those bounds.
What I want to optimise:
If possible, I want to re-design the function ...
5
votes
1answer
218 views
Automating the aggregation and filtering of an 8-worksheet spreadsheet
Every piece of business that gets written (E.G. an adviser's client puts an additional £10k payment into a pension plan) goes in a spreadsheet called the SubSheet. It's split into 8 worksheets for ...
5
votes
1answer
52 views
Refer to other cells besides the one in the Cells.Find
I have the following that loops through a range. When the initial value is found on the second sheet it goes to the cell to be able to compare some date values on the same row. Once the comparison ...
5
votes
2answers
70 views
Excel SLOOKUP UDF
I created an Excel AddIn that has a function called SLOOKUP which can replace INDEX MATCH combinations in an easy and smart way. ...
19
votes
3answers
196 views
Racetrack in… VBA?
So, I took a look at the August Challenge:
The top-voted answer is
Racetrack1:
"In the game of
Racetrack2, cars race
around a track bounded by two concentric closed loops drawn on a
...
1
vote
1answer
87 views
Writing a hyperlink to a cell with Apache POI
This code embeds a hyperlink into a cell of your choice of an Excel file.
...
2
votes
2answers
151 views
Copying and pasting from MS Project to MS Excel
This copies data from a project into Excel to be calculated into a final report. The code runs pretty slowly at this point and I would like it to be a little bit faster. Are there any blatant coding ...
2
votes
2answers
28 views
Pick out excel cells from a long horizontal list based on an adjacent numerical value
I've already written this macro that does exactly what I want it to do. However, it's quite large, and I may wish to expand on it in future. Basically, the purpose is to copy and paste items from a ...
5
votes
2answers
475 views
Injecting JavaScript to website for ordering products
I am a newbie at programming and I helped my dad with making this program which orders multiple products to a website from a table of Excel.
It involves 3 processes:
Passing orders from Excel to ...
5
votes
2answers
127 views
Downloading stock information from Yahoo! Finance
The program downloads stock information from Yahoo! Finance and displays it in the spreadsheet. On my Mac the program takes 10 minutes to get data for approximately 4000 stocks and on the PC it takes ...
7
votes
5answers
532 views
Copying and comparing two sheets before deleting duplicates
I am running this code as a macro in Excel, which copies two columns from sheet 1 and pastes them into sheet two. It then compares the first column to a column in sheet two before deleting any ...
0
votes
1answer
77 views
Transferring information between sheets based on column headings
This function conditionally copies and pastes information from one sheet to another based on the column headings to standardize data before exported to SQL database.
I am not an expert in VBA and ...
6
votes
2answers
68 views
Adding all IDs associated with a Fax to one Row
I found a custom function called UniqueItems() to use and then developed my own RemoveDups() and ...
6
votes
2answers
105 views
Copying data entry values in to a separate WorkFile
The code is working but slow. Is there a way to speed it up or make it more compact but still easy to read?
There are 35 different jobs to be done. They are made in a worksheet with merged cells to ...
3
votes
1answer
75 views
Streamlining function that gets the length of each value
The code works well but before I added sections (13) and (14), it ran in 6 minutes and now runs in 16 minutes. If there is a way to streamline this to cut down the runtime, that would be ...
5
votes
1answer
255 views
Excel macro to move data across workbooks using an ADO connection
I am writing a macro to move data from a CSV to an excel template. Currently, I have the code set to search for a keyword in column A of the CSV, and extract data from specified columns in the ...
4
votes
2answers
598 views
Converting CSV files to Excel workbooks
My converter converts all CSV files in the subfolders of folders 1, 2 & 3 into Excel workbooks. As of now, I am converting using codes for each folders. I previously tried to combine those into ...
4
votes
1answer
73 views
VBA macro to move data across workbooks
I'm new to VBA and am trying to write a macro to move data from an exported file to an excel template. The code below works, however I have to add many more ranges of data and am worried it will be ...
7
votes
3answers
167 views
Extracting data from CSV file on the internet
This is my code I made that extracts data from a .csv file stored on the web. My sheet called 'New Data' stores this data for 2 days. I store 2 days worth of data so that when I run it next time, it ...
4
votes
3answers
210 views
Copying a range from one file to another
Since it has too many loop, loop inside loop, this works very slowly. How can we increase the speed of this program? I am trying to copy a range from one file to another file of same name in different ...
8
votes
3answers
232 views
Colorizing Excel cells with a date range filter
In a table there is a number of column (chambre) and a number of row (alveole). If these matches in a list in an other sheet then it checks if it the date is between two dates on that second sheets. ...
5
votes
2answers
224 views
Calculating how long sales reps are idle
I wrote a procedure to calculate the number of months elapsed since a sales agent' last sale. It works but runs in over 10 minutes... for 20 rows.
...
6
votes
2answers
157 views
Select and Paste Rows in a Different Location
I am working with the output from a chemistry instrument, in .xls format.
The output contains up to seven blocks of data corresponding to different types of samples e.g. controls, unknowns, ...
5
votes
3answers
117 views
Copying data from an Excel sheet to Word
This code copies a specific range and a chart from an Excel sheet to Word. This code works fine but it is very slow. I have used delays(Wait) to work properly while ...
7
votes
2answers
221 views
Intense worksheet manipulations: what price did I pay for performance optimization?
The code bellow was refactored for performance improvements for another user on this site.
Functionality, high level:
Sheet1 - CodeName aIndex: used as the main reference to the structure of the ...
6
votes
3answers
173 views
Copying columns and formats on condition
I have made significant changes in the code and now it takes about 20~40 seconds to process each worksheet. This will conditionally copy column based on the column headers in sheet2 that match to the ...
5
votes
3answers
187 views
Sorting and color-coding based on variables
I have the following three macros all running in the same module. Each one is attached to its own button (3 buttons total). All three buttons take an average of 6-12 mins to run. As I am working on ...
