Daily posts of Excel tips…and other stuff
Archive for the ‘Arrays’ Category.
In regard to Multiple Substitute Formula, here’s one way to do it in VBA.
Sub RemoveStates()
Dim rInput As Range
Dim rStates As Range
Dim vaInput As Variant
Dim vaStates As Variant
Dim i As Long, j As Long
Dim sTemp As String
Set rInput [...]
Two recent instances required transferring data from one array to another. One was a solution to speed up the use of COUNTIF for each element in a large range testing against another large range. In the process I had to build several building blocks including a replacement for the native Excel TRANSPOSE function, [...]
Every time I post code that includes picking a random integer, Tushar reminds me that my methods produce biased results. The last time, I swore to myself I would do it right. Here’s how the old me would have done it:
Sub RandTest1()
Dim lRand As Long
Dim i As [...]
Hi everyone,
With all these Euler posts I thought, why not post my own?
Suppose the following:
I have a products table set up like this:
Code ProdName Price
0001 Product1 556.68
0002 Product2 977.41
0003 Product3 350.62
0004 Product4 509.16
0005 Product5 748.4
0006 Product6 802.96
(list goes on to as much as 5000 products)
And I want to show this table in a userform and give the user some filtering possibilities (lets say on the first two [...]
Here’s a nice explanation of arrays
Arrays, What’s the Point
On the Stackoverlow Podcast, Joel and Jeff agree that arrays are obsolete. There are far better data structures, most of which are built on arrays.
I still use arrays quite a bit. But considering I’m using an obsolete language (VBA = VB6), I guess that’s not [...]
In a previous post, I demonstrated how to use constants to improve your code. Then everyone started beating up my loop. The code wasn’t solving a real life problem, so I just threw any old loop together. It wasn’t relevant because that’s not what the post was about. To fight back, [...]
An AOL-user asks J-Walk:
An AOL User: What five letter word appears in the KJV only four times? It was made before Adam and named by Adam. Had no legs, arms or soul. Was given a soul and it was taken back.
Using J-Walk’s Bible in Excel, I listed all of the five letter words that appear [...]
I need to save the names of one or more files in the custom document properties of a workbook. I don’t want to create a separate property for every filename. I thought about saving a comma-delimited string, then parsing it out. It would look like:
file1name,file2name
Another idea I had was just save one [...]
