Thread
:
Calculating percentages
View Single Post
#
5
Posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
Calculating percentages
..find() won't return an error if it isn't successful. But C will be nothing.
wrote:
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
Paul
mkarja wrote:
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.
----
mkarja
--
Dave Peterson
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson