Wednesday, January 14, 2009

Old Skool VB Trickery

Recently I had need to modify some column values in an Excel spreadsheet.

I used the following combination of the InStr and Mid functions to modify values on all rows in Sheet1.

Sub RemoveHashesFromAM()

Dim b
Dim v

Dim lresult As String

For i = 1 To 1124

If InStr(1, Sheet1.Cells(i, 16), ";#") > 0 Then

b = InStr(1, Sheet1.Cells(i, 16), ";#")
b = b + 2

Sheet1.Cells(i, 16) = Mid(Sheet1.Cells(i, 16), b)


End If

Next i

End Sub


And I used the following Replace and Trim functions as necessary

Sheet1.Cells(i, 31) = Replace(Sheet1.Cells(i, 31), ";#", " ")
Sheet1.Cells(i, 31) = Trim(Sheet1.Cells(i, 31))

No comments:

Fixes to common .NET problems, as well as information on .NET features and solutions to common problems that are not language-specific.

Fixes to common .NET problems, as well as information on .NET features and solutions to common problems that are not language-specific.

Z