Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating percentages
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating percentages | Excel Worksheet Functions | |||
Calculating percentages | Excel Worksheet Functions | |||
calculating percentages | Excel Worksheet Functions | |||
calculating percentages | Excel Discussion (Misc queries) | |||
Calculating percentages | Excel Worksheet Functions |