I'm an experienced programmer (C++, C#, JAVA, ASP), but I know almost
nothing about Excel. One of our people gets a weekly spreadsheet containing
a column named "Actual Hours / BY". This column contains data like TI 21 /
DS 4 / RR 6. The data is simple text, and the number of items and field
length varies. Before you ask, I know this is a stupid way to send data, and
I have no idea why they do it this way.
Our guy wants a total of this hour info (e.g. TI 21 / DS 4 / RR 6 =
21+4+6=31). I suspect I'll need to define a new column in the VB editor and
write a snippet to total by row, then run a total on the newly created
column.
Problem #1: I haven't the slightest idea how to do this.
Problem #2: Our guy gets a new copy of this spreadsheet weekly, so I need to
run whatever function I create every week. Does Excel have a global cache
for stored procedures?

macros are stored in a workbook. You can put your macro in a workbook and
save it. Then open it to run you macro. The macro can be written to
operate on the activeworkbook, so you would make the data workbook the
active workbook and run the macr.

Assume you have xl2000 or later

assume the column containing the data is column C and the results will be
placed in column D (insert a new column D

Sub GetSums()
Dim rng As Range, cell As Range
Dim i As Long, j As Long
Dim sStr As String, sChr As String
Dim varr As Variant
Dim dblTot As Double

' insert new column D
Columns(4).Insert
Set rng = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
varr = Split(cell.Value, "/")
dblTot = 0
For i = LBound(varr) To UBound(varr)
sStr = ""

For j = 1 To Len(varr(i))
sChr = Mid(varr(i), j, 1)
If IsNumeric(sChr) Then
sStr = sStr & sChr
End If
Next
If Len(sStr) 0 Then
dblTot = dblTot + CDbl(sStr)
End If
Next
cell.Offset(0, 1).Value = dblTot
Next
End Sub

--
Regards,
Tom Ogilvy


"bigbob" wrote in message
...
I'm an experienced programmer (C++, C#, JAVA, ASP), but I know almost
nothing about Excel. One of our people gets a weekly spreadsheet
containing
a column named "Actual Hours / BY". This column contains data like TI 21 /
DS 4 / RR 6. The data is simple text, and the number of items and field
length varies. Before you ask, I know this is a stupid way to send data,
and
I have no idea why they do it this way.
Our guy wants a total of this hour info (e.g. TI 21 / DS 4 / RR 6 =
21+4+6=31). I suspect I'll need to define a new column in the VB editor
and
write a snippet to total by row, then run a total on the newly created
column.
Problem #1: I haven't the slightest idea how to do this.
Problem #2: Our guy gets a new copy of this spreadsheet weekly, so I need
to
run whatever function I create every week. Does Excel have a global cache
for stored procedures?

Thanks, Tom. I appreciate your effort, although I didn't expect anyone to
provide that complete of a response.

"Tom Ogilvy" wrote in message
...
macros are stored in a workbook. You can put your macro in a workbook and
save it. Then open it to run you macro. The macro can be written to
operate on the activeworkbook, so you would make the data workbook the
active workbook and run the macr.

Assume you have xl2000 or later

assume the column containing the data is column C and the results will be
placed in column D (insert a new column D

Sub GetSums()
Dim rng As Range, cell As Range
Dim i As Long, j As Long
Dim sStr As String, sChr As String
Dim varr As Variant
Dim dblTot As Double

' insert new column D
Columns(4).Insert
Set rng = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
varr = Split(cell.Value, "/")
dblTot = 0
For i = LBound(varr) To UBound(varr)
sStr = ""

For j = 1 To Len(varr(i))
sChr = Mid(varr(i), j, 1)
If IsNumeric(sChr) Then
sStr = sStr & sChr
End If
Next
If Len(sStr) 0 Then
dblTot = dblTot + CDbl(sStr)
End If
Next
cell.Offset(0, 1).Value = dblTot
Next
End Sub

--
Regards,
Tom Ogilvy


"bigbob" wrote in message
...
I'm an experienced programmer (C++, C#, JAVA, ASP), but I know almost
nothing about Excel. One of our people gets a weekly spreadsheet
containing
a column named "Actual Hours / BY". This column contains data like TI 21
/
DS 4 / RR 6. The data is simple text, and the number of items and field
length varies. Before you ask, I know this is a stupid way to send data,
and
I have no idea why they do it this way.
Our guy wants a total of this hour info (e.g. TI 21 / DS 4 / RR 6 =
21+4+6=31). I suspect I'll need to define a new column in the VB editor
and
write a snippet to total by row, then run a total on the newly created
column.
Problem #1: I haven't the slightest idea how to do this.
Problem #2: Our guy gets a new copy of this spreadsheet weekly, so I
need
to
run whatever function I create every week. Does Excel have a global
cache
for stored procedures?