I have a set of data with different groups ("colors"). For each group (color), there are questions that apply to "categories" ("A", "B", "C", etc.). Each has a numerical score. I need to pick the highest scoring question for each category, for each group (color).
Example: Group "Blue" has 2...
I have formula that's working fine in small data but now I have to use in a large data set so I really need a way to optimize the formula.
the table is like this,
I was hoping the community might be able to help me figure out what tech specs in a new computer could help me optimize the performance for an Excel file I use to produce a twice/weekly report/newsletter as a side business.
The file is ~100 Mbs and growing. The file essentially has 3...
Would it be possible to optimize the code below?
Sub Graphs()Dim lr As Integer, lc As Integer, r As Integer
Sheets("data_pivot").Visible = True
lr = data.Cells(Rows.Count, 1).End(xlUp).Row
lc = data.Cells(1, Columns.Count).End(xlToLeft).Column
lr2 = data_pivot.Cells(Rows.Count...
I am using SUMPRODUCT to summarize the vacation days for all employees for each month. My formula looks like this:
My below code is working fine but I want to optimize it using a loop. Can I re-write below code using any loop in VBA?
Set rng2 = ws.Range("O14:O500")
Set rng3 = ws.Range("P14:P500")
Set rng4 = ws.Range("Q14:Q500")
Set rng5 = ws.Range("R14:R500")...
First post; I searched the forum and found threads that are pretty close but not quite right for what I'm trying to do.
I have a working function that is unnecessarily redundant, and I'm trying to convert it to an array function. Here is the raw:
I have a workbook with lots of formulas and sheets. It is updated weekly and the data grows irregularly with every update. As of right now I have my formulas referencing entire columns in order to capture all new data. This is starting to catch up with me, as the processing time is detracting...
The below code works, but it takes about 5 minutes to complete. I was hoping someone might be able to share some tips on how to get this to run much faster? I really appreciate any help or advice. Thank you!
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column Q...
I have a spreadsheet that has a list of URLs on Sheet2. The macro pulls the JSON data from each URL and then pastes them in a certain order on another sheet. Since the data pull is huge, it keeps freezing when i try to do all 196 URLs at once. But if i do them 50 at a time, it works just...
Hoping someone can point me in the right direction with regards to optimizing some vba.
The below code takes 12 seconds to run against 3000 cells selected in each worksheet, it looks at one column in each worksheet, looks for a duplicate value and highlights the cell accordingly, the...
Hey guys, here's a part of my code that contains putting these formulas in 7 different columns for i amount of rows (i = ~210,000). At the end of the code, I copy the range and paste as values to get rid of the formulas, but keep the values.
It works, but the problem is that it REALLY slows...
Hi everyone, I am new to this forum but have been reading a large number of posts recently as I am currently self teaching VBA for use at work!
I currently am having an issue with a bit of code that I have created. The aim of the code is to autofilter multiple sheets depending on a cell value...
I am using Solver to iteratively find best fit solutions for rather complex polynomial equations. I’ve created 3 macros by recording VBA code for loop calculation of desired “variable”. I am only a beginner and don’t have time or need to become proficient.
I have 8760 rows (8760 hrs/year)...
I have a Spreadsheet with over one-million rows of data. Of course it takes awhile to load or even update. Plus when I try to run a filter it vegetates every time.
I was wondering if there are things I can do (e.g. save in older formats, remove "bells and whistles" or formatting) that would...
I'm a newbee... that needs help to solve what is probably a simple solution.
From the project list (below) I need to select those projects that will collectively optimize a payback of <2.
Save Cost Payback
$6,529.54 $16,273.66 2.492315534
So I have created this MACRO for work, to ensure everything is getting charged right. Well I would like it to run through pretty much the entire database data for 2 years, I can not get it to last that long. I can copy the data to a separate spreadsheet and run the macro in chunks, but I would...
(Spoiler, long post)
I am relatively uneducated when it comes to VBA, but I have muddled my way into a semi-functioning project that until recently worked fine. Let me begin with a little background. I work in Security and we moved to a different Asset Tracking system a little bit...