Daily posts of Excel tips…and other stuff
Archive for the ‘VBA Basics’ Category.
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, [...]
Summing the digits of a number is a chore I’ve been doing alot lately. Originally I’d parse the number out over the columns. And since SUM() ignores text, I’d turn the characters into digits by applying an arithmatic identity operation, like this:
=- -MID($A10,COLUMN(),1)
That’s double minus signs before the MID() function. The reasons [...]
The Daily WTF posted a challenge to code the Russian Peasant Multiplication. Here’s mine:
Function RussianPeasant(lFirst As Long, lSecond As Long) As Double
Dim lDiv As Long
Dim lMult As Long
Dim lMod As Long
lDiv = lFirst
lMult = lSecond
Do Until [...]
As a big-time college basketball junkie (Villanova ‘96 grad school–Kerry Kittles et al), I thought the NY Times had two excellent NCAA basketball tourney graphics. No pie charts! I needed a tech assist from Dick to learn how to do this, so I’m not as timely as I should have been, and that [...]
The purpose of this article is to introduce various capabilities of a class property. It is not meant to serve as an introduction to classes and objects. See the references section for introductory pages.
The typical use of a Class Property is to assign a value or to query its value as in the [...]
The “wrong” way:
Sub FindTotals()
Dim rCell As Range
For Each rCell In Sheet1.Columns(1).Cells
If Left$(rCell, 5) = "Total" Then
‘Do something
End If
Next rCell
End Sub
The “right” way:
Sub FindTotals2()
Dim rCell As [...]
Red wants to have a kind-of lottery for his students. He will award them prizes based on a random drawing, but wants to weight each student based on the number of assignments turned in. Normally, I would accomplish this by typing the name of each student in column A one time for every [...]
This post introduces several new items on the www.tushar-mehta.com website.
