string manipulation

[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

 

[Rick Rothstein]

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.

 

[Jacob Skaria]

Dear Joseph

Thanks for posting this query as this will be useful for me. Please try the
below piece of code which would expect the data in Column A and the result in
ColumnB.

If this post helps click Yes
--------------
Jacob Skaria


Sub Macro()
Dim intRow
Dim intResources

intRow = 1
Do While Range("A" & intRow) <> ""
intResources = GetStringCount(Range("A" & intRow), ",") + 1
intResources = intResources + GetStringCount(Range("A" & intRow), "200%")
Range("B" & intRow) = intResources
intRow = intRow + 1
Loop

End Sub

Function GetStringCount(strData, strString)
Dim intTemp
intTemp = 1

Do While intTemp > 0
intTemp = InStr(intTemp + 1, strData, strString)
If intTemp > 0 Then GetStringCount = GetStringCount + 1
Loop

End Function

 

[Bernd P]

Hello,

For a start, I would use split. Example:
http://www.sulprobil.com/html/wssplit.html

Regards,
Bernd

 

[Nigel]

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

 

[Joseph Atie]

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

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
(e-mail address removed)

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

 

[Ron Rosenfeld]

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

I'm not sure, at first glance, why your method does not return the correct
results. But it does seem like a complex method to parse out the various
parameters of the string.

Seems to me it would be simple to
count the number of commas
add 1
Then, for each "%", divide by 100 and subtract 1, then add that number
to the total.

Here's a different approach at parsing out your string using regular
expressions, and then doing the math:

======================================
Option Explicit
Sub manhours_cal()
Dim c As Range
Dim re As Object, mc As Object
Dim i As Long
Dim lHrs As Long

Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+(?=%)"
re.Global = True

For Each c In Selection
c.Offset(0, 1).ClearContents
lHrs = Len(c.Value) - Len(Replace(c.Value, ",", "")) + 1
If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
For i = 0 To mc.Count - 1
lHrs = lHrs + mc(i) / 100 - 1
Next i
End If

c.Offset(0, 1).Value = lHrs
Next c
End Sub
===============================
--ron

 

[Rick Rothstein]

Ron's algorithmic approach makes the most sense (count commas and add that
to the sum of the numbers in brackets less one). Here is a non-RegEx macro
that implements that algorithm...

Sub CountWorkers()
Dim R As Range, X As Long, Count As Long, Parts() As String
For Each R In Selection
Count = UBound(Split(R, ",")) + 1
Parts = Split(R, "[")
For X = 1 To UBound(Parts)
Count = Count + Val(Parts(X)) / 100 - 1
Next
R.Offset(, 1).Value = Count
Next
End Sub

 

[Bernd P]

Hello Joseph,

You already got many good suggestions on how to solve your task.

If you like your original code corrected:
Sub manhours_cal_corr()


'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 = resource
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, InStr(1, labour & "%", "%")
- 1)
'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

Regards,
Bernd