Count # of "." characters within a cell

[Bubba]

I need some help to count the number of "." characters within a particular
cell and return this value to another cell using VBA. For Example in Column A
I have the following:

WBS 56OC-0000700
WBS 56OC-0000700.01
WBS 56OC-0000700.01.10
WBS 56OC-0000700.01.10.10
WBS 56OC-0000700.01.10.10.10
WBS 56OC-0000700.01.10.10.20
WBS 56OC-0000700.01.10.10.30
WBS 56OC-0000700.01.10.10.40
WBS 56OC-0000700.01.10.10.50

This script will need to be in a Macro to loop through each row and count
the number of period (".") characters encountered. For each row I will store
this value in the column using the variable 'DescCol'. Any help is
appreciated! Thanks!

 

[Rod Gill]

Hi,

From my book, page 346 a function to count decimal places in a string. Pass
this function any string (EG Tsk.Text1) and it returns the number of
decimals.

Function NumberOfDecimals(OutlineNumber As String) As Long
'Return the number of periods in the OutlineNumber string parameter
Dim instr1 As Long
Dim instr2 As Long
Dim Level As Long
Level = 0
instr1 = 1
Do
instr2 = InStr(instr1, OutlineNumber, ".")
If instr2 = 0 Then
Exit Do
Else
instr1 = instr2 + 1
Level = Level + 1
End If
Loop
NumberOfDecimals = Level
End Function

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

I need some help to count the number of "." characters within a particular
cell and return this value to another cell using VBA. For Example in
Column A
I have the following:

WBS 56OC-0000700
WBS 56OC-0000700.01
WBS 56OC-0000700.01.10
WBS 56OC-0000700.01.10.10
WBS 56OC-0000700.01.10.10.10
WBS 56OC-0000700.01.10.10.20
WBS 56OC-0000700.01.10.10.30
WBS 56OC-0000700.01.10.10.40
WBS 56OC-0000700.01.10.10.50

This script will need to be in a Macro to loop through each row and count
the number of period (".") characters encountered. For each row I will
store
this value in the column using the variable 'DescCol'. Any help is
appreciated! Thanks!

__________ Information from ESET Smart Security, version of virus
signature database 4825 (20100201) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jan De Messemaeker]

Hi,

First, I have the impression that you are not working with MS Project which
does not know anything like "Column A".
Moreover, there is definitely no column named "Desc Col"
Supposing you are, and your text is in the task field Text1, and you put the
result in the task field Number1, here's your code:

for each job in activeproject.tasks
if not task is nothing then
job.number1=0
for Ctr=1 to len(job.text1)
if mid(job.text1,Ctr,1)="." then
job.number1=job.number1+1
end if
next ctr
end if
next job
end sub

Greetings,



--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availabliy check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Jan De Messemaeker]

Sorry, it is
if not job is nothing instead of
if not task is nothing.
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availabliy check:
http://users.online.be/prom-ade/Calendar.pdf