J
Joseph Atie
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
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