Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
CJ CJ is offline
external usenet poster
 
Posts: 18
Default Newly created Get Function is not working when I copied the syntax from a working function

In my spreadsheet, I have a module where the following working
functions previously existed:

Function GetPass(TR As Range) As Integer
Dim i As Integer, c As Integer
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "pass" Then
c = c + 1
End If
Next i
GetPass = c
End Function
-----------------------------------------------
Function GetFail(TR As Range) As Integer
Dim i As Integer, c As Integer
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Fail" Then
c = c + 1
End If
Next i
GetFail = c
End Function
-----------------------------------------------
Function GetToDo(TR As Range) As Integer
Dim i As Integer, c As Integer, d As String
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "to do" Then
c = c + 1
End If
Next i
GetToDo = c
End Function
-----------------------------------------------
Function GetTotal(TR As Range) As Integer
GetTotal = TR.Count
End Function

**********
I needed to further define a "fail" status so I renamed fail to bug in
the above function. Then I copied that function and defined it for
each of my other fail types. (Blocked, Warning, Design) - see below:

Function GetBlocked(TR As Range) As Integer
Dim i As Integer, c As Integer, d As String
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Blocked" Then
c = c + 1
End If
Next i
GetBlocked = c
End Function
-----------------------------------------------
Function GetWarning(TR As Range) As Integer
Dim i As Integer, c As Integer, d As String
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Warning" Then
c = c + 1
End If
Next i
GetWarning = c
End Function
-----------------------------------------------
Function GetDesign(TR As Range) As Integer
Dim i As Integer, c As Integer
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Design" Then
c = c + 1
End If
Next i
GetDesign = c
End Function
*******************

In my spreadsheet I have a variable called OTStatus that defines a
range of cells. Then at the top of my spreadsheet I have a call that
tallies the occurence of each of the statuses (Pass, Bug, To Do,
Design, Blocked, etc). This formula is GetPass(OTStatus).
The issue that I'm having is that the functions I copied and modified
are not working in my spreadsheet. For instance in my range (OTStatus)
I have one occurence of Bug, however the cell that is defined with
formula: GetBug(OTStatus), it shows a count of 0. Copying the code for
the function does not appear to be working.

I would be ever so grateful if someone could help. I'd gladly send you
my spreadsheet to help show you what I'm trying to explain! :)

Thank you so kindly in advance!!

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 747
Default Newly created Get Function is not working when I copied the syntax

None of the functions should work except the "to do" and "pass" versions
because of this example line:
If Trim(LCase(TR.Cells(i, 1))) = "Fail" Then

You are coercing the contents of TR.Cells(i, 1) to ALL lower case and then
compare it to a word that has an upper case ("F" in this case). Change your
comparison expressions to all lower case ("fail", "warning", "design" etc.)

Regards,
Greg

"CJ" wrote:

In my spreadsheet, I have a module where the following working
functions previously existed:

Function GetPass(TR As Range) As Integer
Dim i As Integer, c As Integer
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "pass" Then
c = c + 1
End If
Next i
GetPass = c
End Function
-----------------------------------------------
Function GetFail(TR As Range) As Integer
Dim i As Integer, c As Integer
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Fail" Then
c = c + 1
End If
Next i
GetFail = c
End Function
-----------------------------------------------
Function GetToDo(TR As Range) As Integer
Dim i As Integer, c As Integer, d As String
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "to do" Then
c = c + 1
End If
Next i
GetToDo = c
End Function
-----------------------------------------------
Function GetTotal(TR As Range) As Integer
GetTotal = TR.Count
End Function

**********
I needed to further define a "fail" status so I renamed fail to bug in
the above function. Then I copied that function and defined it for
each of my other fail types. (Blocked, Warning, Design) - see below:

Function GetBlocked(TR As Range) As Integer
Dim i As Integer, c As Integer, d As String
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Blocked" Then
c = c + 1
End If
Next i
GetBlocked = c
End Function
-----------------------------------------------
Function GetWarning(TR As Range) As Integer
Dim i As Integer, c As Integer, d As String
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Warning" Then
c = c + 1
End If
Next i
GetWarning = c
End Function
-----------------------------------------------
Function GetDesign(TR As Range) As Integer
Dim i As Integer, c As Integer
For i = 1 To TR.Count
If Trim(LCase(TR.Cells(i, 1))) = "Design" Then
c = c + 1
End If
Next i
GetDesign = c
End Function
*******************

In my spreadsheet I have a variable called OTStatus that defines a
range of cells. Then at the top of my spreadsheet I have a call that
tallies the occurence of each of the statuses (Pass, Bug, To Do,
Design, Blocked, etc). This formula is GetPass(OTStatus).
The issue that I'm having is that the functions I copied and modified
are not working in my spreadsheet. For instance in my range (OTStatus)
I have one occurence of Bug, however the cell that is defined with
formula: GetBug(OTStatus), it shows a count of 0. Copying the code for
the function does not appear to be working.

I would be ever so grateful if someone could help. I'd gladly send you
my spreadsheet to help show you what I'm trying to explain! :)

Thank you so kindly in advance!!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ISBLANK function not working when cell is blank dut to function re mcmilja Excel Discussion (Misc queries) 9 May 7th 23 03:43 AM
Working Days function copied down column Mally Excel Discussion (Misc queries) 1 July 23rd 09 07:24 AM
IF function not working Loren Excel Discussion (Misc queries) 5 May 5th 08 05:36 PM
Problem using newly created user function in Excel 2003 Richard Excel Discussion (Misc queries) 10 January 22nd 07 01:44 AM
Working with Arrays, pasing from function to function mikebres Excel Programming 2 April 27th 06 06:33 PM


All times are GMT +1. The time now is 04:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"