File or function not found error
 

I've written the following:

Sub CountReasonContactCode()
Dim Wksht As Worksheet
Dim rReason As Range
Dim lRow As Long
Dim lCurRow As Long
Dim rCell As Range
Dim l16Rct As Long
Dim l16Act As Long
Dim l16BGct As Long
Dim lCt As Long

lRow = 107

l16Rct = 0
l16Act = 0
l16BGct = 0
Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D"))
rReason.Select

For Each rCell In rReason
If rCell = "13" Then

lCt = InStrucase(1, rCell.Offset(0, 2).Value, "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If
(rest of code not shown)

I'm getting a compile error on "InstrUcase". I Googled here, and looked
for a "missing" component in VBE references. None there. What is
causing this? I know it's not the instr because without the ucase it
works fine. I looked for ucase in VBE help, and it isn't there. Not
sure this means anything because lots of VBA
command/function/properties aren't listed in help.
Thanks for the help.

File or function not found error
 

lCt = InStr(1, ucase(rCell.Offset(0, 2).Value), "R")

or for a case insensitive ( r = R, R = R, r = r)

lCt = InStr(1, rCell.Offset(0, 2).Value, "R", vbTextcompare)


--
Regards,
Tom Ogilvy



"davegb" wrote in message
oups.com...
I've written the following:

Sub CountReasonContactCode()
Dim Wksht As Worksheet
Dim rReason As Range
Dim lRow As Long
Dim lCurRow As Long
Dim rCell As Range
Dim l16Rct As Long
Dim l16Act As Long
Dim l16BGct As Long
Dim lCt As Long

lRow = 107

l16Rct = 0
l16Act = 0
l16BGct = 0
Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D"))
rReason.Select

For Each rCell In rReason
If rCell = "13" Then

lCt = InStrucase(1, rCell.Offset(0, 2).Value, "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If
(rest of code not shown)

I'm getting a compile error on "InstrUcase". I Googled here, and looked
for a "missing" component in VBE references. None there. What is
causing this? I know it's not the instr because without the ucase it
works fine. I looked for ucase in VBE help, and it isn't there. Not
sure this means anything because lots of VBA
command/function/properties aren't listed in help.
Thanks for the help.

File or function not found error
 

You cannot combine functions like that. Each function can be combined but
must be arranged to act on the elements relevant for the function. In this
case you want the UCase function to act on the test string part of the InStr
function e.g........

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")

BTW: UCase is referenced in VBA help

--
Cheers
Nigel



"davegb" wrote in message
oups.com...
I've written the following:

Sub CountReasonContactCode()
Dim Wksht As Worksheet
Dim rReason As Range
Dim lRow As Long
Dim lCurRow As Long
Dim rCell As Range
Dim l16Rct As Long
Dim l16Act As Long
Dim l16BGct As Long
Dim lCt As Long

lRow = 107

l16Rct = 0
l16Act = 0
l16BGct = 0
Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D"))
rReason.Select

For Each rCell In rReason
If rCell = "13" Then

lCt = InStrucase(1, rCell.Offset(0, 2).Value, "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If
(rest of code not shown)

I'm getting a compile error on "InstrUcase". I Googled here, and looked
for a "missing" component in VBE references. None there. What is
causing this? I know it's not the instr because without the ucase it
works fine. I looked for ucase in VBE help, and it isn't there. Not
sure this means anything because lots of VBA
command/function/properties aren't listed in help.
Thanks for the help.