Macro stops @ row 300 with count... how?

[celjames]

When I change the contents of column 'K' for example to Pass. It
updates a value up top where I have Pass / Fail / To Do / Total.

Once I reach row 300, the macro stops firing and I can change the cells
for K:301 and further down, but nothing updates.

When I hover over the TR.Count below it reads Count = 300. How do I
change this?

Thanks!


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

 

[stevebriz]

You need to make sure the range includes past 300 when you call this
function.

 

[CJ]

I got that, but how. Sorry I am a newbie here trying to edit an
existing macro. I've found the problem, but can't find the reference
to the 300 to change.

Thanks!!

 

[Guest]

How are you determining TR?

I use this method - it's clunky but it works unless there is a blank cell
somewhere in the range. Since I deal with imported data, that's rarely the
case.

cells(1,11).select 'cell K1
inRow = Selection.End(xlDown)

From i = 1 to inRow

' <etcetera>

 

[stevebriz]

ok...
in your sub where you call the function you need to define the range

eg: MsgBox GetPass(Range("K1", "K400"))

As I am not sure how you are setting range at present. as you didn;t
post this code but it could be something like

Dim k As Range
Dim NumOfPass as Integer
Set k = Range("k1", "k400")
'then
NumOfPass =GetPass k

 

[CJ]

When I open VB from the Excel menu, I have a list of Microsoft Excel
Objects (my worksheets) with no information showing. I open the
modules tree and see Module1. When I double click on that I get
information to show, part of which I pasted below.

See the ENTIRE list of what I see here. I can't see where the TR is
defined. Can you help me find it? I would happily send you the
spreadsheet and be forever grateful!

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

 

[stevebriz]

Email me the sheet and I will have find it for you ...no problem

 

[CJ]

Steve hooked me up.

The range is called OTStatus..

From Excel

On the insert menu goto NAME then DEFINE and move the cursor down until
OTSTATUS is highlighted then edit the range ath bottom to go down as
far as you need then hit ok

='Phase 3'!$E$17:$E$316
eg:
='Phase 3'!$E$17:$E$450

THANKS STEVE!!