Solved: Making Scripts More Efficient

[Opv]

I have a number of scripts that takes certain actions on specific columns within a worksheet (database). Currently I have my column references hard coded into my scripts. However, each time I expand my database, add or rearrange columns, it renders some of my scripts obsolete until I manually go in and change all the column references.

I've been thinking about creating a function to "Find" the appropriate heading and capture the current column number so that the scripts run regardless of whether a column has been moved, and so that I don't have to continue to manually update my scripts.

Is such an approach reasonable in terms of efficiency or would it have more of a negative effect than its worth?

[mikerickson]

One approach would be to use named ranges for your column headers.

If you have NameHeader as a named cell, the VB reference Range("NameHeader") will adjust to Inserting and Deleting, while Range("B1") will not.

[Opv]

One approach would be to use named ranges for your column headers.

If you have NameHeader as a named cell, the VB reference Range("NameHeader") will adjust to Inserting and Deleting, while Range("B1") will not.

Hmmm. I can see how that would be more efficient constantly "finding" the new column number. Thanks.

[mdmackillop]

If efficiency is what you are after, have a look here (http://www.decisionmodels.com/)

[Opv]

If efficiency is what you are after, have a look here (http://www.decisionmodels.com/)

Thanks.

[Paul_Hossler]

Another way -- I have a 'toolbox' workbook of subs and functions that I use for 'List' type of worksheets.

I just copy the module(s) I need into the workbook in development. and then just call my own little library. Makes reusing a lot easier. When I seen something clever in here in the forums, I just incorporate it into my toolbox.

So I have these for finding the column on a WS by heading and returning the number. Since I usually want to do something with or to the data, the column number is typically used with the other functions that return a range of some sort.


Option Explicit
Sub drv()
MsgBox ListColData("MasterData", "DDD").Address
MsgBox ListColData("MasterData", "AAA", 2, False).Address
End Sub
'take ws name and column header and return colnum number, 0 if not there
Function ListColNum(ws As String, s As String) As Long
ListColNum = 0
On Error Resume Next
ListColNum = Application.WorksheetFunction.Match(s, Worksheets(ws).Rows(1), 0)
On Error GoTo 0
End Function
Function ListColRange(ws As String, s As String, Optional IncludeHeader As Boolean = True) As Range
Dim r As Range
Set ListColRange = Nothing
On Error Resume Next
Set r = Worksheets(ws).Cells(1, ListColNum(ws, s))
On Error GoTo 0

If IncludeHeader Then
Set ListColRange = r
Else
Set ListColRange = r.Cells(2, 1).Resize(r.Rows.Count - 1, 1)
End If
End Function

'returns a range object of the data cells in a col or cols. Optional include headers
Function ListColData(ws As String, ColName As String, _
Optional NumCols As Long = 1, _
Optional DataOnly As Boolean = True) As Range
Dim r As Range

Set ListColData = Nothing

If ListColNum(ws, ColName) = 0 Then Exit Function

If DataOnly And Worksheets(ws).Cells(1, 1).CurrentRegion.Rows.Count = 1 Then
Set ListColData = Nothing
Exit Function
End If

With Worksheets(ws)
Set r = Intersect(.Columns(ListColNum(ws, ColName)), .Cells(1, 1).CurrentRegion)
If DataOnly Then
Set r = r.Cells(2, 1).Resize(.Cells(1, 1).CurrentRegion.Rows.Count - 1, NumCols)
Else
Set r = r.Cells(1, 1).Resize(.Cells(1, 1).CurrentRegion.Rows.Count, NumCols)
End If
End With

Set ListColData = r
End Function


SOmetimes I do need the column number, but I still use the ListColNum function



iStartDateCol = ListColNum ("Master", "S_Date")

.....

rRow.Cells (i, iStartDateCol).Value = #4/1/2011#


Paul