Visual Basic for Applications (VBA) is an event-driven programming language first introduced by Microsoft in 1993 to give Excel 5.0 a more robust object-oriented language for writing macros and automating the use of Excel. It is now used for the entire Office suite.
5
votes
3answers
46 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 ...
-1
votes
0answers
8 views
Changing the color of a bar within a chart based on presence in a list [on hold]
I am creating a series of column graphs that show performance on one metric, but would like to highlight the columns which belong top performers on another, related metric. I do not have the data for ...
5
votes
3answers
50 views
5
votes
4answers
177 views
Get Filtered Data Range in a Table
Purpose:
Given a Worksheet Table (ListObject) that may, or may not, have a filter currently applied to it, return a range consisting only of the filtered data.
...
11
votes
1answer
111 views
Finding the cheapest hotel
I thought this question made a good excuse for some basic OOP code, so I whipped up a little bit of code to demonstrate it in VBA; the idea is to have tutorial-grade code, to show how interfaces and ...
3
votes
2answers
38 views
Accelerate the process of filling the quality control table
I'm working on a report of quality control. I've got a table, with all the points of control inside, and each time, when I get a new test result, Excel should fill this table. My code already works, ...
3
votes
3answers
77 views
Searching values of range X in range Y
This allows me to search each cell J4:J6525 in range K4:K6525. If it is found it will paste the values in Offset(0, 2) (column M).
I run it for files with more than 60000 lines, and sometimes much ...
3
votes
2answers
25 views
Retrieve value from a formula in cells into different merged cells and retrieved value can be edited
I have created this macro and having a small issue. I am very new to this VBA and did this with experts’ guides by googling it. I am creating an excel form for a work purpose at work. The Excel form ...
1
vote
1answer
37 views
Finding and deleting a certain word
I have a working macro that finds a specific word and deletes the whole text. It finds the word "JOBSPECIALTYCODE" in the sheet, selects and deletes it, then moves to the next sheet. I just want to ...
3
votes
1answer
50 views
Copy sheet contents faster in Excel
I have code that opens a workbook and copies the sheet to the workbook running the code. I know activating is generally bad for speed but I can't figure out how to speed this up anymore. The data ...
2
votes
1answer
30 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 ...
0
votes
0answers
17 views
Paste one array (single dimension) with >100K item into a excel range [migrated]
I posted the same question on StackOverflow thread
but I think here is the correct place to ask (if is not right, admin please to remove it).Every day I need to format date imported from AS400 (data, ...
3
votes
1answer
85 views
Comparing two lists
I'm working on a report of quality control. I have a list L, which contains the names of files that I should open and import the data into EXCEL, then fill my quality control table by these data.
And ...
8
votes
2answers
38 views
Reusable VBA macro to prevent Excel from destroying the data (+ backup capacity)
My goal is to prepare the Excel file (see the download link at the end) I would share with my non-technical colleagues, so that they enter some data into it and then I was able to automatically ...
5
votes
3answers
54 views
7
votes
1answer
33 views
Audit Updating Macro
In my code, I'm trying to manipulate data for an audit between 3 sheets in a workbook.The first block of my code is to paste the data of items I need to find for the audit from the original sheet onto ...
3
votes
3answers
58 views
List(Of ) like operation in macro
NOTE: I previously asked this question in Stack Overflow and got pointed here.
I have a macro that I am trying to get running a little more quickly. The intent of the macro is to merge multiple files ...
4
votes
3answers
79 views
Dealing Poker Hands
I thought I'd give a shot at creating my own version of dealing 5-card hands to n players in VBA, printing them to columns and coloring hearts and diamonds red.
I felt I might have been a little ...
8
votes
4answers
91 views
Converting from .xlsx to .txt
I have code that converts each sheet of a spreadsheet into a .txt file. It works well, however, given the big number of exports (about 90 .txt files), I'd like to seek advice on how to speed it up.
<...
3
votes
3answers
56 views
Error handling for cells.find
Revised and truncated version of my last question. I updated how I handle the error if a cost center is not found. I originally asked expecting improvements on the matching system so thoughts on that ...
0
votes
0answers
18 views
Automatically pull in data from one workbook into a template workbook and update the formulas [duplicate]
I have two workbooks: one being a template file with saved formulas that extend to the 30,000 row and the other workbook that contains the data that is input into the template file.
Due to the fact ...
6
votes
3answers
77 views
Loop through a unique combinations driven by choices
I am trying to optimize my code and make it as fast as it can be, I have researched as much as I can and am now asking the community for any tips because there are definitely people more experienced ...
5
votes
3answers
238 views
Transferring data for a given month from one sheet to another
I have dollar amounts sorted by month that I want to transfer from sheet entry to sheet actuals. I only want to do it for a ...
10
votes
3answers
734 views
Macro that does some kind of duplicate check
This macro pulls in two workbooks, one being a template with saved formulas already, and the other containing data with thousands of rows...I need to increase the speed because the process takes more ...
4
votes
1answer
106 views
Clearing filters, copying a range, and then pasting a formula down a row
Novice coder here. I have the following code to perform a few actions like clearing filters, copying a range, and then pasting a formula down a row till the last line.
It works perfectly, but it ...
10
votes
3answers
97 views
Userform for getting data from data sheet into a table
I am making a userform that grabs data from a data sheet and puts it into a table:
Grabs the data based on what the user wants (Brand -> Items for brand)
Allows multiple items to be added
Displays ...
11
votes
4answers
583 views
Copying values from one sheet to another
I am trying to copy values from one sheet to another using loop with two conditions and it is very slow. Removing conditions from code doesn't have any effect on the speed of the execution.
I already ...
7
votes
3answers
222 views
Summarize raw data in new sheet, 2.0
Thanks to zak for some great advice on this previous question. This is a follow up question:
I have an Excel workbook with two sheets: "Raw data" and "Summary". In the raw data sheet there are ...
2
votes
1answer
39 views
Summarize raw data in new sheet
Note: This question has a follow up question here.
I have an Excel workbook with two sheets: "Raw data" and "Summary". In the raw data sheet there are several blocks with data structured like the ...
9
votes
4answers
1k views
Create a list of random numbers with sum = n
I want to create a list of random integers in the range [0, 9], that sums up to 100 in Excel, using VBA. The list should be printed in a single column.
The routine I've written is as follows:
...
2
votes
0answers
32 views
Mailmerge from Excel to premade template in Word
I've written the following code to take data from the Excel file "Data" and use it for a mailmerge in a prepare Word template named "Template." I'm new to using VBA in word or Access, so any feedback ...
9
votes
5answers
831 views
Macro code that removes excess rows
I'm a business student who just started to learn VBA. I am trying to write a macro for a project but only have minimal experience actually stepping into the code.
The macro I have written is to ...
5
votes
1answer
51 views
Compare and find duplicates in 2 corresponding columns in 2 sheets
I want to compare (500) and find duplicate daily records within 2 sheets, and copy the unmatched row to another sheet, copy the match from another to 3rd sheet, and delete the matched records from ...
4
votes
2answers
45 views
Consolidate used student hours into master sheet for export
I've developed a spreadsheet that has evolved over several years to help keep track of the number of student contact hours that have been used to help flag up when students have run out / are running ...
6
votes
1answer
68 views
Excel VBA to parse JSON out of Google Maps API
Some code that I wrote and broke into 5 separate questions on stack overflow. Some guy wanted to query Google Maps receive some JSON and then for each result found within that JSON do another query. ...
2
votes
2answers
55 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 ...
6
votes
1answer
198 views
Print worksheets depending on checkbox. Slower than before
I have an userform that has check boxes where user can decide which worksheets to print.
Code in userform:
...
4
votes
2answers
65 views
Importing JPG, JPEG, and PNG images from a folder to an Excel worksheet
As I am a novice, I was keen to solve the task, so now I want to optimize the code.
...
4
votes
1answer
38 views
Auto-upload tool
I created an automatic upload tool, which is working great (most of the time). As I want to improve the code I have several questions:
Is it possible to make the iexplorer process smoother? I don't ...
4
votes
1answer
46 views
Using VBA Copying from one ,master sheet to other workbooks based on the file names provided in master book
Pretty new to this hence taking it as challenge. I have a master sheet in which Column A have all the file names( complete file name with .xls extensions). I need to copy data of column b in this ...
7
votes
1answer
59 views
Interpolating data of multiple trains
I have written a piece of VBA code to essentially replace a complex formula that I was using in the Excel cells.
I have the data on Sheet2, which describes multiple train journey's along the same ...
1
vote
1answer
29 views
CLS_Compound_Predicate
I have a CLS_Comparison_Predicate which will take an input value, perform a logical comparison and return a Boolean.
CLS_Compound_Predicate instead takes lists of ...
4
votes
2answers
83 views
Copy, Paste and Format 2.0
This is a follow up with revised code, see the original question and subsequent answer in the following link (Copy, Paste And Format)
This is a full version of all three "Macros" or subs within the ...
10
votes
1answer
554 views
Copy, Paste And Format
I am currently working with this code to automate some tasks for senior staff members that are not very adept in Excel. Wondering if VBA is simply not a very quick code or if my code is clunky and ...
7
votes
1answer
72 views
Class: 2D Variant Array
Note: Yes. It's big. I'm not expecting commensurately
long/detailed answers (though if anyone wants to write one, you'll definitely be receiving a substantial bounty). This class is going to be used ...
4
votes
2answers
35 views
Re-arranging data on a worksheet based on contents of Column A
The following Macro sorts through a worksheet, copying and removing lines based on what appears in column A. If ("A" & i) is not a number, it triggers and moves the data to the appropriate place. ...
1
vote
0answers
27 views
An [InternalCopyArray] by any other name
So I'm building a 2D Array class.
I want to have CopyArray and RemoveIndexes functions. However, in both cases, the method ...
3
votes
1answer
22 views
Code to copy, paste and summarize a data set
The code currently works as it should and seems to work dynamically as I need it to but I am pretty new to Excel so I think there is probably a better way to do some of the things I have set up. I ...
5
votes
1answer
34 views
Rearrange data without using Cut and Insert
I'm fairly new to VBA, and this was basically a brute-force solution to a problem I was encountering. I wanted to take data that appeared in two columns, and pull it together into one.
The current ...
7
votes
1answer
245 views
Logical Predicate Class
For a while now I've been playing around with various ways of passing a logical predicate E.G. >= someValue as an argument to a function. (see: If ...
