Usually dealing with a few hundred to a few thousand rows
Might be a simpler way... since I only need the records that don't have exactly 1 record with 'ADD' status I might be able to simplify the logic some
It's absolutely critical though that once a Serial Number is matched to a Contract Number, that I delete all records with the same Contract Number (possibly after performing a calculation to count of many of each status match that exact serial)
Maybe shove all the different contract numbers matching a serial into an array...? Then delete all records matching any contract number in the array, then clear the array and start over for the next serial number
@RubberDuck gave a pretty awesome review here, I'm just going to cover dissect the InCollection function, which was left out.
I like that you are sticking a comment at the top of every procedure - that's very good practice. One little nitpick about this one though:
'Returns boolean true if an ...
My Excel 2010 workbook contains
4 datasheets with a table (i.e. a ListObject)
deliveries, used range $A$1:$T$7647
stock, used range $A$1:$V$22962
sales, used range $A$1:$U$2348
summary, used range $A$1:$AA$9509 (a summary of the life cycle per item, created in VBA)
7 hidden sheets with piv...
@Chrismas007 DBA SE is not interested in Excel. SuperUser SE makes sence, but you would encourage me more with some explanation why than with a downvote. — Dirk Horsten7 mins ago
That's not what ActiveWorkbook.Connections is for. That would show the number of pivot tables, query tables, etc. that are using a connection; VBA connections are totally independent of that. — retailcoder9 secs ago
I'm seeing that Range.Interior.Color returns different numbers for the same color in some cases, depending on whether it is running in Excel 2007, or Excel 2010 or 2013.
Is that expected?? I'm surprised.
Range.Interior.Color is the background color ("Fill Color") of the cell. In the Immediate p...
I used to drop+recreate all the staging tables overnight, but I changed it to truncate+repopulate earlier this week, hoping it'd fix the metadata issues
thing is, the source system is pushing its dumb text-file-based data to a MySQL database every night, re-creating tables every night. So I keep getting random "column f30 may result in truncation blablabla" and "metadata crap foobar blablabla"
...on different tables/columns every day
when it's not network/connectivity issues. really, fml
Starting some time this morning, when I vote to close a question, the vote is successful, but the "Why should this question be closed?" pane doesn't go away.
As you can see in the screenshot below, the "You've voted to close this question" appears underneath the overlay, next to the "Too broad" ...
Good day!
Now this is more of a syntax question for VBA, specifically when calling on multiple columns in a row. My code is used to give a format to a new row at each iteration. These iterations get progressively slower. I have already looked around a bit to find ways to speed it up, one which I...
what is the syntax for writing vlookup in vba where you want to return multiple columns? My code:
For i = 2 To lastrow
wsTEMP.Range(Cells(i, 16), Cells(i, 20)) = _
Application.WorksheetFunction.VLookup( _
Workbooks("template.xlsm").Worksheets("Qry").Cells(i, 1), _
...
And how would a VLOOKUP with "multiple col_index_num's" even work as a regular Excel formula? WorksheetFunction works just like the worksheet function; if it can't be done with a VLOOKUP, it can't be done with a VLOOKUP in VBA. — retailcoder8 secs ago
@retailcoder is my SE-wide username (matches my email address), back from when I first created my SO account a long time ago. Then CR happened, and I turned into @lol.upvote and then to @Mat'sMug, but only on CR. It might be time to turn into @Mat'sMug network-wide now that you make me think of it ;)
According to a quick Google search, Range.Find will not find the data if the cell is hidden if you use LookIn:=xlValues. I tested this with "Test" in Cell A6 and hid the row. This code returned Nothing:
Sub TestIt()
Dim x As Range
Set x = Range("A1:A7").Find("Test", , xlValues, xlWhole...
Did you guys know that Range.Find won't look in hidden cells if you use xlValues for the LookIn?
Just got a 1 hour side-by-side with brand new (cute) Systems Analyst. Go to show her our awesome data processes, and of course, give her a tour of SNAP!
The main tag for each Microsoft Office program is not consistent on its usage of the ms- prefix. For example:
excel - Synonyms Link ms-excel is synonym-tag
ms-word - Synonyms Link word is a different thing entirely but has many mis-tags
ms-access - Synonyms Link No usage of access that I can f...
See rows 2-5, for example. The data I need is the count of how many Contracts have each kind of Status code, but being there is multiple serials per each contract, and serials are how we find the duplicate, I need to make sure each contract is counted only once
@RubberDuck I am horrified to discover that you are correct. But this reverts the problem to changing the code with the OP's option 2. — Degustaf12 mins ago
@RubberDuck gave a pretty awesome review here, I'm just going to cover dissect the InCollection function, which was left out.
I like that you are sticking a comment at the top of every procedure - that's very good practice. One little nitpick about this one though:
'Returns boolean true if an ...
My Excel 2010 workbook contains
4 datasheets with a table (i.e. a ListObject)
deliveries, used range $A$1:$T$7647
stock, used range $A$1:$V$22962
sales, used range $A$1:$U$2348
summary, used range $A$1:$AA$9509 (a summary of the life cycle per item, created in VBA)
7 hidden sheets with piv...
I'm seeing that Range.Interior.Color returns different numbers for the same color in some cases, depending on whether it is running in Excel 2007, or Excel 2010 or 2013.
Is that expected?? I'm surprised.
Range.Interior.Color is the background color ("Fill Color") of the cell. In the Immediate p...
what is the syntax for writing vlookup in vba where you want to return multiple columns? My code:
For i = 2 To lastrow
wsTEMP.Range(Cells(i, 16), Cells(i, 20)) = _
Application.WorksheetFunction.VLookup( _
Workbooks("template.xlsm").Worksheets("Qry").Cells(i, 1), _
...
According to a quick Google search, Range.Find will not find the data if the cell is hidden if you use LookIn:=xlValues. I tested this with "Test" in Cell A6 and hid the row. This code returned Nothing:
Sub TestIt()
Dim x As Range
Set x = Range("A1:A7").Find("Test", , xlValues, xlWhole...
VBA Rubberducking
VBA Rubberducking