Skip to content

Instantly share code, notes, and snippets.

View hoffstein's full-sized avatar

Benjamin Hoffstein hoffstein

View GitHub Profile
@hoffstein
hoffstein / gist:1109382
Created July 27, 2011 13:43
Excel find column by header text
Function FindColumn(headerText As String, Optional sheetToSearch As Worksheet, Optional headerRow As Integer) As Integer
Dim foundCell As Range
If headerRow = 0 Then headerRow = 1
If sheetToSearch Is Nothing Then Set sheetToSearch = ActiveSheet
Set foundCell = sheetToSearch.Rows(headerRow).Find(what:=headerText, lookat:=xlWhole)
If Not foundCell Is Nothing Then
FindColumn = foundCell.Column
Else
Err.Raise Number:=vbObjectError + 1000, Source:="FindColumn", _
Description:="Column not found: headerText=""" & headerText & _
@hoffstein
hoffstein / gist:1109380
Created July 27, 2011 13:42
Excel find worksheet by name
Function FindWorksheet(sheetName As String) As Worksheet
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sheetName Then Set FindWorksheet = ws
Next
End Function
@hoffstein
hoffstein / gist:1109377
Created July 27, 2011 13:41
Excel find last column
Function FindLastColumn(Optional sheetToSearch As Worksheet) As Long
If sheetToSearch Is Nothing Then Set sheetToSearch = ActiveSheet
On Error GoTo handler
FindLastColumn = sheetToSearch.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Exit Function
handler:
FindLastColumn = 1 ' No data on sheet
End Function
@hoffstein
hoffstein / gist:1109374
Created July 27, 2011 13:41
Excel find last row
Function FindLastRow(Optional sheetToSearch As Worksheet) As Long
If sheetToSearch Is Nothing Then Set sheetToSearch = ActiveSheet
On Error GoTo handler
FindLastRow = sheetToSearch.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Exit Function
handler:
FindLastRow = 1 ' No data on sheet
End Function
@hoffstein
hoffstein / .gvimrc
Created July 18, 2011 15:42
My gvimrc
set nocompatible
set backspace=indent,eol,start
set nobackup
set mouse=a
set confirm
" Windows settings
source $VIMRUNTIME/mswin.vim
behave mswin
@hoffstein
hoffstein / gist:1089896
Created July 18, 2011 15:40
Vim keep only the first word in each line
:s/\(\s*\w\+\).*/\1
@hoffstein
hoffstein / gist:1089894
Created July 18, 2011 15:39
TSQL truncate time (returns just the date at 12:00:00AM)
DATEADD(d, DATEDIFF(d, 0, @date), 0)
@hoffstein
hoffstein / gist:1089890
Created July 18, 2011 15:38
TSQL last second of day
DATEADD(s, -1, DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, @date), 0)))
@hoffstein
hoffstein / gist:1089884
Created July 18, 2011 15:37
Regex for matching floating point number
[-+]?[0-9]*\.?[0-9]+
@hoffstein
hoffstein / gist:1089880
Created July 18, 2011 15:36
Vim Delete All Trailing Whitespace
:%s/\s\+$//