Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
You need to make sure the range includes past 300 when you call this
function. wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
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!! stevebriz wrote: You need to make sure the range includes past 300 when you call this function. wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
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 -- HTH JonR " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
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 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
Email me the sheet and I will have find it for you ...no problem
CJ wrote: 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 wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro stops @ row 300 with count... how?
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!! JonR wrote: 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 -- HTH JonR " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening a file stops a Macro | Excel Worksheet Functions | |||
Macro repeats and then stops | Excel Discussion (Misc queries) | |||
My Macro stops | Excel Programming | |||
Macro stops before beginning. | Excel Programming | |||
macro stops midstream | Excel Programming |