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

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

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.

----
mkarja

(E-Mail Removed) 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

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.)

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

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

(E-Mail Removed) 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

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 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

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.

----
mkarja

Dave Peterson wrote:
> 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 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