You might want to explain how your came up with your counts... I don't see
how you got the numbers you showed. Perhaps if you took one of the lines and
showed us the individual "men that are indicated" that might help.

--
Rick (MVP - Excel)


"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