Calculating percentages

[mkarja]

Hi,

I'm trying to make a macro that would do two things. 1) calculate the
run rate of test cases 2) calculate the success rate of test cases.

I did first the success rate calculation and it worked. Then I added
the run rate calculation, and did everything same way I did the success
rate, but for some reason the code won't work anymore.
It gets stuck at the ending End Sub.
I'll post the code here so you can see the code and mayby tell me
what's wrong with it.

------ SNIP ------
Function CountPassed(ByRef value As Long) As Long
Dim cnt As Long
Dim c As Range
Dim firstAddress As String

With Sheet2.Range("F7:F86")
Set c = .Find("PASS", LookIn:=xlValues)
If Not c Is Nothing Then
cnt = 0
firstAddress = ""
firstAddress = c.Address
Do
cnt = cnt + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

value = cnt
End Function

Function SuccessRate(ByRef value As Long) As Long
Dim passed As Long
passed = value
value = 0
value = passed / 60 * 100
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cntRun As Long
Dim cntPass As Long

Call CountRun(cntRun)
Call RunRate(cntRun)
Sheet2.Cells(3, 2).value = cntRun

Call CountPassed(cntPass)
Call SuccessRate(cntPass)
Sheet2.Cells(4, 2).value = cntPass
End Sub
------ SNIP ------

The code for CountRun & RunRate is exactly the same as those Success
codes are.
Except the CountRun has one difference than CountPassed. The line of
code is:
Set c = .Find("PASS", LookIn:=xlValues). Instead of the word PASS there
is letter A.
That way it will count both, the PASS and FAILED cases.
No when I try this, it crashes and when I press the Debug button the
last line, End Sub
is highlighted.
Any help would be greatly appreciated.

 

[paul.robinson]

Hi
The .find will give an error if there is no word "PASS" in your range.
You can suppress the error with

On error resume next
Set c = .Find("PASS", LookIn:=xlValues)
If Not c Is Nothing Then
cnt = 0
firstAddress = ""
firstAddress = c.Address
Do
cnt = cnt + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
on error goto 0

The two wrapping lines make VB ignore the error and go to the next
line.
regards

 

[mkarja]

Hi,

Thanks for the help. I added you suggestions, but there's still a
problem. I think I know what happens now, but I don't know why.

It seems like it goes into an infinite loop or something, because
when I test this by adding FAILED on one cell in the range, the
cursor just turns into a hour class and it goes on and on. When
I interrupt it by pressing Esc, then click the Debug button the
highlight is on different rows almost everytime.

The code works otherwise. When I debug it step by step the
values in the designated cells change to what it should be, but it
just won't stop.

 

[Dave Peterson]

Maybe you could just drop those function routines and replace them with a
worksheet formula:

if pass is the only thing in the cell
=countif(sheet2!f7:f86,"pass")
or
or if the cell contains other stuff
=countif(sheet2!f7:f86,"*pass*")

=============

or do that in code, too:

dim myRng as range
dim cntRun as long
set myrng = Sheet2.Range("F7:F86")
cntRun = application.countif(myrng,"Pass")
or
'cntRun = application.countif(myrng,"*Pass*")

==========
Just a guess about the End Sub stuff...

Is that worksheet_Change code behind sheet2?

If yes, then each time your code runs, it changes something on sheet2 and causes
the code to run again and again and again and....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cntRun As Long
Dim cntPass As Long

Call CountRun(cntRun)
Call RunRate(cntRun)
application.enableevents = false
Sheet2.Cells(3, 2).value = cntRun
application.enableevents = true

Call CountPassed(cntPass)
Call SuccessRate(cntPass)
application.enableevents = false
Sheet2.Cells(4, 2).value = cntPass
application.enableevents = true
End Sub

I'd use Me if this code were behind sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cntRun As Long
Dim cntPass As Long

Call CountRun(cntRun)
Call RunRate(cntRun)
application.enableevents = false
me.Cells(3, 2).value = cntRun
application.enableevents = true

Call CountPassed(cntPass)
Call SuccessRate(cntPass)
application.enableevents = false
me.Cells(4, 2).value = cntPass
application.enableevents = true
End Sub

(Me refers to the object owning the code. In this case, sheet2. I think it
makes the code easier to read/understand.)

 

[Dave Peterson]

..find() won't return an error if it isn't successful. But C will be nothing.

 

[Dave Peterson]

ps. You may want to start specifying all the parms for the .find. Excel and
VBA will remember the last parms used. And if the user specified something you
don't want, you'll be at their mercy--unless you tell the code what to do.

 

[mkarja]

Hi,

Many thanks for your help Dave. Now it works as it should.
The reason I have to do it with macro code is that in the range
specified
the case results are in a group of three. There are one empty line
between
cases.
But anyways, it works now and I'm happy. Thank you again.