thanks guys for your help

to answer questions,

how do i get my figures:

B/M,Rigger[200%] = 1 B/M + 2 Rigger = 3 men.

the comma is the delimiter and the % is the number, no % = 1, 300% = 3

as for max numbers, its unlikely there will be more than 9 of any given
trade on a job but not impossible.

i appreciate your different methods of achieving the output, but i was
wondering if someone can tell me why mine doesnt work. if for no other reason
than my own education.

i just cant understand why the instr function wont pickup the [ on the last
iteration but works any other time. I could understand if the first line of
test data failed, but it works perfectly. that is unless the last iteration
has more than one person.

Again thaniks for your time guys


"Nigel" wrote:

You do not say if the percentage can be greater than 200%, but the following
code allows for up to 900%, puts the result in column 'F' to the right of
the strings in column E

Sub CountMen()
Dim lR As Long, iX As Integer
Dim iMen As Integer, sText As String

With Sheets("Data")
For lR = 2 To 1000
sText = Trim(.Cells(lR, 5))
If Len(sText) 0 Then
iMen = 1
For iX = 1 To Len(sText)
If Mid(sText, iX, 1) = "," Then iMen = iMen + 1
If Val(Mid(sText, iX, 1)) 1 Then iMen = iMen + Val(Mid(sText, iX,
1)) - 1
Next
Else
iMen = 0
End If
.Cells(lR, 6) = iMen
Next
End With
End Sub

--

Regards,
Nigel




"Joseph Atie" wrote in message
...
Ive got an issue with a piece of code i wrote, its aim is to split a
string
into pieces and count the number of men it indicates

The string comes from ms project and is a labour allocation so its format
is
what it is and i cant change it.

here is the test data in using

P/F,Rigger[200%],Welder,P/F,Rigger[200%],Welder,P/F,Rigger[200%], Welder
P/F,Rigger[200%],Welder
P/F,Rigger[200%],Welder
B/M,Rigger[200%]
P/F,Rigger[200%],Welder
B/M,Rigger[200%]

These are the results i expect

12
4
4
3
4
3


these are the results i get

12
4
4
2
4
2

here is the code

Sub manhours_cal()

'declaration
Dim resource As String
Dim trade As String
Dim labour As String
Dim labour1 As String
Dim cell As Object
Dim total As Integer
Dim count As Integer
Dim num As Integer

Sheets("Data").Select
Range("E2:E1000").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
For Each cell In Selection
resource = cell.Value
If resource = "" Then
total = 0
Else
For count = 1 To 10
num = InStr(1, resource, ",")
trade = Left(resource, num)
If num = 0 Then
count = 11
trade = trade & ","
End If
'MsgBox ("Resource: " & resource)
resource = Right(resource, Len(resource) - num)
num = InStr(1, trade, "[")
'MsgBox (num)
If num < 1 Then
labour1 = 100
Else
'MsgBox (trade)
labour = Right(trade, Len(trade) - num)
'MsgBox ("Labour: " & labour)
If labour < "" Then
labour1 = Left(labour, Len(labour) - 3)
'MsgBox ("Labour1: " & labour1)
End If
End If
total = total + (labour1 / 100)
'MsgBox ("Total: " & total)
Next count
End If
cell.Value = total
total = 0
Next cell
End Sub

I cant understand why it work perfectly fine unless the last argument in
the
string has more than 1 person.

Help please, what am i missing