All Questions
56
questions
5
votes
2
answers
133
views
concatenate values and delete the remaining rows using arrays
the below code used to:
Concatenate the values on a specific column "N" depend on the value of column "A" then delete the remaining rows.
It works, but with range of 30k rows the ...
- 275
3
votes
1
answer
91
views
Iteratively paste values from spreadsheet tabs into empty spreadsheet
Basically below code is taking first 7 tabs from my current sheet and pasting them as values in a blank excel sheet with same tab names and saving them in a specific folder by filename I specified. ...
- 31
4
votes
2
answers
987
views
Loop between two Arrays and change values based on condition
Instead of using 45 IF conditions , I put my two ranges into variant arrays.
Then I used the below code to loop between them and change values of the first array ...
- 275
6
votes
1
answer
448
views
Fastest function to `Remove Duplicate Lines` per each cell
The below function is used to Remove Duplicate Lines per each cell.
It works without problem, but it is slow with a range of only one column and 17k rows.
Actually, ...
- 275
0
votes
2
answers
173
views
VBA - Compare & Consolidate Dynamic Arrays
Background
The CSV files being used can be 6+ mill records in size, using a small sample size to proof the methodology.
Why are you doing this on excel? Access or SQL would be quicker
My company has ...
- 11
0
votes
3
answers
91
views
Populate array using elements from four source arrays
Long piece of code, I hope it is readable.
The code is basically if statements looking at 4 arrays: ArrPnLDataD1, ...
- 13
2
votes
1
answer
690
views
=PRINTF("{1} - {2}", ... ) in Excel without VBA - LAMBDA functions with variable number of arguments
The TEXTJOIN function has a really nice interface:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …, [text252])
... where ...
- 2,395
2
votes
4
answers
216
views
To what degree is my VBA Array size function safe, robust, and performant
Coming from a Java/Scala background with a strong focus on OOP+FP, I recently started working with Excel+VBA to implement a front-end for a SaaS. It was like stepping way back in time, over two ...
1
vote
1
answer
95
views
Looking for a more condensed method of assigning values by using arrays and fixed ranges
I currently have a Template Workbook set up that has charts and graphs updating when new data is plugged into the Data Tables.
The Data Tables are fixed ranges, and won't change.
The Macro is within ...
- 41
2
votes
1
answer
113
views
Output the rounded sum of each column in selection
I've written something (i.e. frankensteined from stack exchange) that appears to work but I haven't done much testing on the edge cases. Signed up here for some feedback on what optimizations or other ...
- 23
3
votes
1
answer
171
views
Array manipulation object
I'm trying to write a class that simplifies the most common operations with arrays and then I want to distribute... Maybe it can help someone...
But I'm facing some problems in make the object ...
- 354
2
votes
1
answer
150
views
OOP approach to filter two-dimensional array
So, after this functional(?) approach and thanks to the great improvement of @CDP1802, I've decided to try an OOP approach to filtering an two-dimensional array.
In my opinion, the result is way more ...
- 354
5
votes
1
answer
420
views
Filter a two dimension array
This is the function I've wrote to filter a two-dimension array.
I use it mainly on forms with user-defined filter (categories, dates, search bar, etc).
It works fine, but it's ugly.
Do you have ...
- 354
5
votes
4
answers
189
views
Check non-continuous range for equal values
Background
I'm trying to check equality between cells in an irregular, non-continuous range of cells. For this purpose I would like to know if all cells are equal to the first ...
- 218
1
vote
1
answer
104
views
Comparing information to a directory of active enployees
The code is doing everything that I need it to. It's taking much too long though upwards of a couple minutes to complete the macro. Does anyone know how I code optimize this VBA code to run quicker? I'...
6
votes
1
answer
388
views
Excel's Dynamic Array Functions...In VBA (No Win 32)
In recent months I have been trying to figure out how in the world one can mimic the functionality of Excel's New Dynamic Arrays exclusively in ...
- 668
3
votes
0
answers
2k
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-...
- 668
2
votes
0
answers
2k
views
VBA array functions: push, pop, shift, unshift
I want to write my snake game procedurally, using as much windows call as I can so as to practice. Looking into GetAsyncKeyState to capture keyboard inputs and play sound functions. Also making a ...
- 525
3
votes
1
answer
116
views
Array that stores values from two ranges and compares if one cell is blank
This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.
The long and short of the code is ...
- 593
2
votes
0
answers
96
views
Use arrays to perform 'VLookUp' type activity between workbooks
Background
I have the below UDF that opens a workbook (this workbook has extracted data from a queue within another app). I get the used range from this workbook and assign it to an array (...
- 121
2
votes
2
answers
235
views
Index Match implementation
The code below takes header variables, retrieves the column index, and then, using an Index/Match function, returns data from a matching account number.
The reason it is written this way is because I ...
- 131
6
votes
2
answers
112
views
Find select values on one worksheet and copy to a second
In the Calculator worksheet of my spreadsheet, for any appliance type present in a building being studied, users enter a quantity. There are 18 different appliance ...
- 419
3
votes
1
answer
487
views
Find winning move in Tic-Tac-Toe with boolean arrays in VBA
I've been trying to mess around with boolean arrays in VBA since I started to try to write an algorithm to solve sudoku puzzles. I thought I'd first try to solve codeabbey tic-tac-toe. I might be ...
- 7,159
2
votes
2
answers
194
views
Mapping one array onto another where columns from first array become rows in second array
I am processing an unformatted CSV, New-AmbSYS-to-2018-Jan.csv, from NHS England Ambulance Quality Indicators, by reading its contents into an array, and carrying out a number of steps that lead to a ...
- 375
2
votes
2
answers
2k
views
Reorder columns in Array
I am reordering columns in an array and removing one column. The mapping is as follows:
...
- 375
1
vote
2
answers
93
views
Extracting filenames from a URL by splitting a string
I am used to accessing items from an array, returned by the Split function, directly. This is lazy, I know. Usually, I know the element I want and I say something ...
- 375
1
vote
1
answer
333
views
Array Wrapper Class
I’ve wrote a small array wrapper class which mimics the built-in Collection for adding and retrieving items. Once the object is created, you set the array capacity and that would not change.
...
- 215
2
votes
2
answers
235
views
Multiply a 2D array by 1D array to get a third (2D) array
I've working with three dynamic arrays (all are datatype Double) - they are
OriningalArray
This will be assigned from a range that the end user will see and ...
- 131
5
votes
1
answer
90
views
Merging List Arrays
Some utility functions I re-wrote today:
Purpose:
Given an arbitrary list of 1-D arrays (possibly empty variables), return a
1-D array containing unique values from all lists.
Ex: Given (1,...
- 8,800
2
votes
1
answer
148
views
VLookup simulation: copying data from one sheet to another
I tried to write a VLOOKUP in my macro but for some unknown reasons it didn't work (see my post on StackOverFlow for more info.
So I decided to do a macro which achieves the same result, but it takes ...
- 121
11
votes
1
answer
283
views
Find all differences between 2 strings
I pulled this out of my code bucket and dusted it off earlier today in response to a post over on SO that made me cringe. This was originally written to highlight changes in Excel cells in real time ...
- 4,192
3
votes
1
answer
119
views
Comparing two arrays and printing results as a collection of a class
This is really my first time using a class and I originally wrote it without the class and without refactoring.
This is to take two inputs (csv), place the data in arrays and then compare the arrays. ...
- 7,159
1
vote
2
answers
217
views
Splitting Excel workbook sheets
Running Office 2007, I have master workbook with data, which I:
Split master sheet data to new sheets for every salesmen
Create new workbook (with 1 sheet)
Put sheets of salesmen to an array ...
- 123
3
votes
1
answer
55
views
Comparing and pasting ranges of spreadsheet cells
I have a moderate size sub-procedure where I am calling multiple functions to feed values into other functions. I feel there may be a better or more concise way to present and run this module, but ...
- 319
4
votes
2
answers
1k
views
A 'flexible' VBA approach to lookups using arrays, scripting dictionary and user input
In my previous post Optimise compare and match method using scripting.dictionary in VBA I wanted to address optimising the scripting.dictionary approach I was using and I feel I have achieved that (...
- 319
2
votes
1
answer
4k
views
Optimise compare and match method using scripting.dictionary in VBA
In my previous post I was looking for a better solution to write a single column of a 2D array into a worksheet besides looping between code and worksheet and finally managed to achieve that.
On ...
- 319
2
votes
3
answers
5k
views
Writing a particular column of a two-dimension array to a worksheet
I had some great feedback on my code previously here and I was hoping that I could get some further help with writing a column of the array to the worksheet.
I have an example here where I write one ...
- 319
4
votes
2
answers
100
views
Comparing dates from sheets
I am looking to increase the speed of this Excel VBA Nested Loop. The loop compares dates from one sheet to a secondary sheet. If they match, I change the border around the cell to highlight it. It ...
- 41
5
votes
3
answers
97
views
CopyOf2DArray (Values Only)
Purpose:
Given a 2-Dimensional Array, return a copy of the Array. In VBA, if I just
Set newArray = oldArray
then newArray is ...
- 8,800
2
votes
1
answer
44
views
Converting values to Dates in column
I'm not happy about my code, but is the better way that I find,
I'm not happy because I see that there are many code repetitions and when any change is easy to make mistakes. Do you think you can do ...
- 307
2
votes
2
answers
161
views
Importing data into Excel
Is there an easier way of importing data into an Excel array or other data structure? I've tried researching collections but I have found the documentation hard to comprehend.
MSDN
The code I have ...
- 123
3
votes
2
answers
3k
views
Using Array to store calculations in VBA
I have the following VBA code, which works perfectly well to calculate "q"
However, the code is very slow and that is due to the large number of q's being calculated (roughly 7.2m q's are being ...
- 41
4
votes
2
answers
8k
views
Assigning entire column of data to specific column of an array in VBA
The below code I have written is to allow me to Sum the results data for every year from 1 to 1000 over a range of 5000 locations. although a 1000 years seems extreme, for the application I am using ...
- 41
3
votes
2
answers
122
views
Data Table Report Class
Most of the VBA I write is to produce tabulated reports from spreadsheet data.
So, here is my attempt at creating a CLS_Data_Report class.
Properties:
a 2-...
- 8,800
6
votes
1
answer
147
views
Get Worksheet Data Array (Standard Methods)
I'm re-writing my module of Standard Methods. Virtually every project I do begins with grabbing some number of Data Tables and putting them in arrays. So, this is my general "Get Worksheet Data" ...
- 8,800
8
votes
2
answers
209
views
Report Building (Data Retrieval, Validation, Aggregation, Business Logic, Report Building, Visual Presentation)
This:
Is a data table we get from our financial platform with lots of useful information. For reference, "--" is also the string they use to denote empty values.
This:
Is a spreadsheet I built (...
- 8,800
3
votes
1
answer
226
views
Building a set of flags based on data from a source workbook
This takes in an array of data built from a source workbook, builds a set of "flags" based on the data in each row of the array. Then it creates a new finalRng ...
- 189
6
votes
2
answers
319
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 ...
- 313
10
votes
2
answers
387
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 ...
- 8,800
5
votes
1
answer
78
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 ...
- 8,800