TheShed

Excel Macro Munging

Category: Programming
#Excel #VBA #macro

The problem:

A set of items in VSO where we wanted the ID as part of the title text.

(It's a long-ish story, but the default VSO board shows only title and assigned to so we adopted a convention of adding the ID to the title text too).

Over time, we'd gathered a number of MVPs on the board where the ID wasn't included. Rather than hand editing, I slurped the query into Excel using the TFS team extensions and ran a little macro to update the items. Here's the code:

Sub fixMissingIDInTitle()

Dim MyCell As Range, MyRange As Range
Dim count, total As Long
Dim sTitle As String
Dim sID As String
Dim iIDLength As Integer


' grab the starting point and then extend to get the range of items
Set MyRange = Sheets("All MVPs").Range("C3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

total = 0
count = 0

' iterate over the range
For Each MyCell In MyRange

    sTitle = MyCell.Value
    sID = MyCell.Offset(0, -2).Value
    iIDLength = Len(sID)

    If (Left(sTitle, iIDLength) = sID) Then
        ' do nothing - ID already at start
        ' Q: but is it the right ID?
    Else
        MyCell.Value = sID + " - " + sTitle
        count = count + 1
    End If

    total = total + 1

Next MyCell

MsgBox ("Updated " + CStr(count) + " of " + CStr(total) + " items.")

End Sub